Forum Discussion

PaulTrimor's avatar
PaulTrimor
Qrew Cadet
7 years ago

Create a Summary Report from a Multi-select Dropdown Field

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
reports and charts
Tables and fields
Tips and tricks

4 Replies

Sort By
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    7 years ago
    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]
    • PaulTrimor's avatar
      PaulTrimor
      Qrew Cadet
      7 years ago
      thanks , If(Contains(ToText([my multi select field]),"A"),1,0)
  • _anomDiebolt_'s avatar
    _anomDiebolt_
    Qrew Elite
    7 years ago
    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.
    • AdamKeever1's avatar
      AdamKeever1
      Qrew Commander
      6 years ago
      That is very useful. How did you learn about this?