Count based on multilple values

Hello,

I need to add a field on the contact page that would calculate the number of open cases of a specific type. 

I tried the following formula, but even tho i'm not getting error message, the calculation does not seem to work.


count(and(equal(related($cases,"type"),"reclamation"),not(equal(related($cases,"status"),"closed"))))


Any idea how to acheave my goal?

Thanks for your help.

Parents
  • Hi  ,

    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  ,

    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
    )
    )

Reply Children