Solved

Is there a way to count items selected within multi-select fields?

  • 15 September 2020
  • 7 replies
  • 473 views
katie_b
Rank
Userlevel 4
Badge +3

I’m trying to count the number of items selected within a multi-select picklist and basically assign a ‘score’ based on how many items are selected within a particular list. I was able to work through a few of these using a case expression, but anything with over 3 choices there are just too many options for this to be feasible. Is there another/better way to count the items selected so I can then SUM a group of fields together?

icon

Best answer by matthew_lind 23 December 2020, 23:15

View original
Katie Baker

7 replies

sai_ram
Rank
Userlevel 7
Badge +1

@katie_b sorry for the delay response here. I didn’t get the lines completely, could you please explain in detail. 

katie_b
Rank
Userlevel 4
Badge +3

@sai_ram For example, I have a multi-select field with 4 options….if 1 of the 4 are selected, I want to give that field a value of 1, 2 selected a value of 2, 3 selected a value of 3, etc. Is there a way to do this via rules?

Our use case is we’re using a multi select field to track specific feature blockers for our customers. We want to know how many blockers have been selected for each account so we can prioritize the impact and risk to those customers. Ideally we’d be able to pull that ‘score’ into a pie chart by CSM (to see how many accounts they have with the various number of blockers) or trigger a CTA with actionable steps to work through these blockers with the highest impacted accounts.

Katie Baker
sai_ram
Rank
Userlevel 7
Badge +1

@katie_b This is an interesting use-case! Let me check the technical possibilities with engineering team. 

@jeevan_chamarthi and @murthy_kavali any inputs on this??

jeevan_chamarthi

@katie_b Valid and sensible use-case. The current Reporting Formula Fields do-not completely solve this. Will need to work through and enhance the system as per the needs.

 

Thanks.

matthew_lind
Rank
Userlevel 7
Badge +9

@katie_b If you’re working in the Rules Engine, I have an idea, and while not a perfect solution, it does keep you out of “too many combinations” problem. You can use the Rules Engine to tally the number of items selected within a multi-select drop-down.

 

In your Bionic Rule

Step 1 - Create a Dataset.

  • Fetch the records you’re interested in, At minimum, include some identifier (I’m a fan of the GSID and Name) and also the multi-select drop-down list for which you want to count values.

Step 2 - From the Step 1 Dataset, create a Transform with CASE statements to figure out which list items are selected.

  • In your Transform, add your identifiers (in my example, GSID and Name)
  • Add one CASE statement for each item on your multi-select drop-down list.
  • Make your output a Number (probably with 0 decimal places).
  • Make your CASE 1 to read if the drop-down includes the first field (and only the first field), THEN CUSTOM 1, DEFAULT CUSTOM 0.
  • Repeat, creating a CASE statement for each item in your multi-select drop-down. (This is the tedious part, but happily you’re creating one field per list item, instead of trying to figure out all the possible combinations of list items.)

 

Step 3 - From the Step 2 Dataset, create a Transform with a Numeric Expression to count up the 1’s from your CASE statements.

  • In your Transform, add your identifiers (in my example, GSID and Name) again.
  • In this Transform, add a Numeric Expression. In your expression, add together all the CASE statement fields you created in Step 2. (In this screenshot, I’m showing just 2 line items, but imagine you have one item here for each drop-down list item.)
  • This Numeric Expression now holds the number of values selected from the multi-select drop-down.

It’s not perfect, as you still need to do some lifting for each line item within your drop-down. However, it scales in a semi-reasonable fashion as you don’t have to figure out all possible combinations. If you ever add line items to your drop-down, you can revisit Step 2 to add a CASE statement for each new line item added and Step 3 to amend your Numeric Expression to include the new line items.

 

Making people their best....currently disguised as a CS Ops Architect. How can I contribute to your success?
abeck0625
Rank
Userlevel 2
Badge +3

@sai_ram @jeevan_chamarthi @murthy_kavali Can you give me an update if there has been any progress on this in the past year? Do I still need to move forward with the above-mentioned workaround? Thanks, Anita

anirbandutta
Rank
Userlevel 7
Badge +2

@sai_ram @jeevan_chamarthi @murthy_kavali Can you give me an update if there has been any progress on this in the past year? Do I still need to move forward with the above-mentioned workaround? Thanks, Anita

@rakesh anything you want to add?

It's an opportunity for engagement

Reply


Terms & ConditionsCookie settings