Create a Summary Report from a Multi-select Dropdown Field

  • 0
  • 1
  • Question
  • Updated 4 months ago
  • Answered
Suppose I have a Multi-select field with options (A, B, C) and 100 records where the field is used. 

Is it possible to create a report that counts each time an option is selected in each record? 
For example: 

A - 5
B - 8
C - 2 

Above would indicate that the option A has been selected 5 times out of all of my records, B has been selected 8 times, and C has been selected 2 times. 

When I create a summary report for each I just get the count of each combination. Like 

A           - 5
A;B        - 3
A;B;C    - 2
Photo of Paul Trimor

Paul Trimor

  • 488 Points 250 badge 2x thumb
  • lonely

Posted 8 months ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,036 Points 50k badge 2x thumb
You can do this with three fields.
The formula for [A count] would be

If(Contains(ToText([my multi select field],"A"),1,0)

But be sure that your choices are unique like ABC and not like

Mice
Elaphants
Ants


Because the phrase ants is also in elephants.

Then do similar fields for the [B count] and [C Count]
Photo of Paul Trimor

Paul Trimor

  • 488 Points 250 badge 2x thumb
thanks , If(Contains(ToText([my multi select field]),"A"),1,0)
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,322 Points 20k badge 2x thumb
If you suffix and prefix the multi-select text field and the option string with the three characters " ; " (ie space, semicolon and space) you can absorb the oddball case where one option is a sub-string of another:

ToNumber(Contains(" ; " & [field] & " ; ", " ; dog ; "))
ToNumber(Contains(" ; " & [field] & " ; ", " ; cat ; "))
ToNumber(Contains(" ; " & [field] & " ; ", " ; fox ; "))
ToNumber(Contains(" ; " & [field] & " ; ", " ; ox ; "))

In the above I used ToNumber() instead of Mark's If() to convert from text to number.
Photo of Adam Keever

Adam Keever

  • 996 Points 500 badge 2x thumb
That is very useful. How did you learn about this?