How to Calculate the median, from the Value of Rule Inputs for individual item in an Cascading dropdown

Hello Everyone,

I have an editable Grid, which contains a Cascading Drop down as shown below, where as the category displays 'MI' and Skills pop up related to that. I also have multiple values as such. And i can add more rows to it.

The following is the Rule Input

Now, how can I to have a median calculated based on "ri!CC_Person.skills.Rating" only for 'MI' even though I add more rows and select 'MI' with the rating.

Thanks in advance..!!

 

  Discussion posts and replies are publicly visible

Parents
  • Can you index the list of CC_Person wherecontains MI for skills.category? Something like:

    median(index(ri!CC_Person.skills.rating, wherecontains("MI", ri!CC_Person.skills.category), 0))

    I'd have to test the code to know if it works correctly, so let me know if that still doesn't work and we can discuss in more detail.
  • Hello Ashvin,

    Thank You for your response, to the check the Median i've taken a text field to test the output temporarily. please check the code below

    a!textField(
    label:"",
    value: {a!forEach(
    items: ri!CC_Person.skills.Rating,
    expression: if(ri!CC_Person.skills.category = "MI", median(index(ri!CC_Person.skills.rating, wherecontains("MI", ri!CC_Person.skills.category), 0)), "")
    )},
    readOnly: true()

    )

    Its not throwing me an error, when one row is selected it gives me a correct value, else if I select many this is the output ";".
  • Not sure I understand - why would you need the forEach() at all? Seems like you want the median of ALL ratings where the category is MI. Try this:

    a!textField(
    value: median(index(ri!CC_Person.skills.rating, wherecontains("MI", ri!CC_Person.skills.category), 0)),
    readOnly: true()
    )

    I also sent you a message, so let me know offline if you need additional info from me. This seems to just be a logic configuration question which we can handle off of the message board.
Reply
  • Not sure I understand - why would you need the forEach() at all? Seems like you want the median of ALL ratings where the category is MI. Try this:

    a!textField(
    value: median(index(ri!CC_Person.skills.rating, wherecontains("MI", ri!CC_Person.skills.category), 0)),
    readOnly: true()
    )

    I also sent you a message, so let me know offline if you need additional info from me. This seems to just be a logic configuration question which we can handle off of the message board.
Children
  • Hello Ashvin,

    The below is the code for Median, As I have to showcase the median i'm using a text field read only and displaying the value



    a!textField(
    label: "MI",
    labelPosition: "JUSTIFIED",
    value: if(ri!CC_Person.skills.category = "MI", median(index(ri!CC_Person.skills.rating, wherecontains("MI", ri!CC_Person.skills.category), 0)),"" ),
    refreshafter: "UNFOCUS",
    readOnly: true(),
    align: "LEFT"
    ),



    Even though I'm using a 'if' condition yet there is an error stating null value being passed - Expression evaluation error at function 'wherecontains' parameter 2 [line 190]: Invalid index: Cannot index property 'category' of type Text into null value of type CC_Skills?list

    Any help would be appreciated
  • Going by the error message, it seems that the skills list is coming back as a null value (not an empty CDT since it would probably recognize that the category field is present). I would double-check that the data you're using is always populating the ri!CC_Person.skills CDT so that at least an empty CDT is assigned to it.
  • 0
    Certified Lead Developer
    in reply to nellorea0001

    Try this updated version of Ashvin's code. I've added some safeguards here, the main one being:

    Indexing instead of using dot notation - As stevens812 noted, if ri!CC_Person.skills is null (not typed), we would get the error you posted. By indexing, we avoid the UI error. 

    a!textField(
      value: median(
        index(
          index(ri!CC_Person.skills, "rating", {}), 
          wherecontains(
            "MI", 
            touniformstring(index(ri!CC_Person.skills, "category", {}))
          ), 
          0
        )
      ),
      readOnly: true()
    )