Sunday, April 14, 2019

Saved searches > Formula to Count the Number of Selected Vlues in a Custom Multiple Select Field

Example, a custom transaction field applied to Opportunity has the following possible values:

In the Results tab of the saved search, displaying the custom field will give the following samples values:

Opportunity Number Custom Field
1 A
2 A,B,C
3 A,B,C,D
4 B,C

When using the Summary type "Count", the custom multiple select field will always give value of "1".

To get the below sample Expected Result, formula can be used.

Number Custom Field  Count 
1 1
2 A,B,C   3
3 A,B,C,D 4
4 B,C 2

Sample Steps using Opportunity Saved search:

1. Reports > Saved searches > All saved searches > New

2. Click "Opportunity".

3. Results tab:
- Internal ID
- Number
- Formula (Numeric)
    Formula = CASE WHEN {customfieldID} LIKE '%,%,%,%' then 4 WHEN {customfieldID} LIKE '%,%,%' THEN 3 WHEN {customfieldID} LIKE '%,%' then 2  WHEN {customfieldID} LIKE '%' THEN 1 ELSE 0 END

4. Save and Run

* note: The number of "%" depends on the maximum number of values in the custom list of the custom field.
In this case, the maximum number of values the user can select in the multiple select field is 4.

No comments:

Post a Comment