Count based on multilple values

Hi Yves Bourgarel ,

To achieve this use case, I recommend doing the following:

1. Create a calculated field (e.g. case_evaluation_c) in the Cases module to evaluate the criteria you want to count records on. Based on your example, the formula could look like the following:
   

   ifElse(
       and(
           equal(
               $type,
               "reclamation"
           ),
           not(
               equal(
                   $status,
                   "closed"
               )
           )
       ),
       "Open - Reclamation",
       ""
   )

2. Then, in the custom field on your Contacts module, you would use the countConditional formula to count cases based on the value in your custom field from step 1. The formula would look like the following:
   

   countConditional(
       $cases,
       "case_evaluation_c",
       "Open - Reclamation"
   )

The reason the first field is necessary is that the countConditional formula can only accept a single condition. The first field allows you to evaluate as many conditions as you need within that module to then give you reliable datapoints for conditional rollup and count calculations in parent modules.

Chris

Thank you Chris,

It does look great indeed.

However I was getting the error message "Attempted to evaluate expression with an invalid format : Open Reclamation". Which block entirely the Contact module. Thank god I was in sandbox ;)

So i did a couple of changes, starting by making both fields integer, then i inserted the following formulas : 

ifElse(
and(
equal(
$type,
"reclamation"
),
not(
equal(
$status,
"closed"
)
)
),
1,
0
)

and  :

countConditional(
$cases,
"case_evaluation_cv2",
1
)


Problem is  : the calculated field on the contact does not return the correct value.

I suspect there might a type problem with the second formula : apparently the second argument is supposed to a list. I tried using the array function (as indicated in the help) and [ ], they both caused an error.

What am i doing wrong ?

Thank you for your help 

Hi Yves Bourgarel ,

I apologize! The second formula I provided had the incorrect syntax. Instead, the formula should be:

countConditional(
    $cases,
    "case_evaluation_c",
    createList(
        "Open - Reclamation"
    )
)

One additional note is that if you are not getting the expected rollup counts, the first formula is case sensitive on the values you are checking. It doesn't matter what the display value is for the option, so you need to look at the dropdown list to see how the dropdown key is capitalized for the purposes of using in formulas.

The default status in the Cases module is 'Closed', not 'closed'. Similarly, if your value for reclamation has any capitalization, you will want to update that.

Chris

Thanks Chris,

Ah! It's still acting like it's not calcultating anything on the contact page.

The field on the cases module works since i'm getting 1 for open ones and 0 for closed.
However it's blocking at the contact page.
Here is the formula i wrote taking into account your last comment : 

countConditional(
$cases,
"case_evaluation_cv2_c",
createList(
1
)
)

Thanks Chris,

Ah! It's still acting like it's not calcultating anything on the contact page.

The field on the cases module works since i'm getting 1 for open ones and 0 for closed.
However it's blocking at the contact page.
Here is the formula i wrote taking into account your last comment : 

countConditional(
$cases,
"case_evaluation_cv2_c",
createList(
1
)
)

Hi Yves Bourgarel ,

I recommend putting the 1 in quotes in your createList formula. If that doesn't work, there may be a defect with trying to count integer values conditionally. The formulas I provided work as desired, so if you are unable to get it to work with the integer, I recommend changing to the setup I reference above.

Chris

Thanks Chris, 
I managed to make it work thanks to you