Forum Discussion

NOELHODGES's avatar
NOELHODGES
Qrew Cadet
13 days ago

Filtering a report for Combined Text Summary Field

I need to filter my report based on a Combined Text Summary Field called "Fruit Salad," which can contain up to multiple different options. I want the report to include only those records where more than one option is selected in the "Fruit Salad" field. For example:

  • Record #1 has "Apple, Banana, Orange" selected.
  • Record #2 has only "Apple" selected.

I want my report to exclude Record #2 because it has only one selection, and include Record #1 because it has multiple selections.

To summarize:

  • Records with less than two selections in the "Fruit Salad" field should be filtered out of the report.
  • Records with two or more selections should be included in the report.

This way, my report will only show records with multiple selections in the "Fruit Salad" field.

reports
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    13 days ago

    If you want a non generic answer to your specific question, you can make a formula checkbox field called [Multiple Fruits?] with a formula of

    Part(ToText([Fruit Salad]),2, ";") <> "" 

    That formula says is look at the [Fruit Salad] combined text summary field, convert it to a text string which will be semicolon delimited and then look at the second part of that string which is delimited by a semicolon and see if it is blank. If it's not equal to blank that means there was a second part and that means there's least two fruits in the salad.

    If you wanted a more generic solution then you could create a formula numeric field called [# of Fruits] and Filter the report where the # of Fruits is >= 2.

    var text value = ToText([Fruit Salad]);

    Count(
    Trim(Part($value,1,";"))<>"",
    Trim(Part($value,2,";"))<>"",
    Trim(Part($value,3,";"))<>"",
    Trim(Part($value,4,";"))<>"",
    Trim(Part($value,5,";"))<>"",
    Trim(Part($value,6,";"))<>"",
    Trim(Part($value,7,";"))<>"",
    Trim(Part($value,8,";"))<>"",
    Trim(Part($value,9,";"))<>"",
    Trim(Part($value,10,";"))<>"",
    Trim(Part($value,11,";"))<>"",
    Trim(Part($value,12,";"))<>"",
    Trim(Part($value,13,";"))<>"",
    Trim(Part($value,14,";"))<>"",
    Trim(Part($value,15,";"))<>"",
    Trim(Part($value,16,";"))<>"",
    Trim(Part($value,17,";"))<>"",
    Trim(Part($value,18,";"))<>"",
    Trim(Part($value,19,";"))<>"",
    Trim(Part($value,20,";"))<>"",
    Trim(Part($value,21,";"))<>"",
    Trim(Part($value,22,";"))<>"",
    Trim(Part($value,23,";"))<>"",
    Trim(Part($value,24,";"))<>"",
    Trim(Part($value,25,";"))<>"")