Discussions

Expand all | Collapse all

Create a Summary Report from a Multi-select Dropdown Field

  • 1.  Create a Summary Report from a Multi-select Dropdown Field

    Posted 03-20-2018 16:11
    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


  • 2.  RE: Create a Summary Report from a Multi-select Dropdown Field

    Posted 03-20-2018 16:29
    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]


  • 3.  RE: Create a Summary Report from a Multi-select Dropdown Field

    Posted 03-20-2018 16:45
    thanks , If(Contains(ToText([my multi select field]),"A"),1,0)


  • 4.  RE: Create a Summary Report from a Multi-select Dropdown Field

    Posted 03-20-2018 16:43
    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.


  • 5.  RE: Create a Summary Report from a Multi-select Dropdown Field

    Posted 07-19-2018 16:42
    That is very useful. How did you learn about this?