Filtering a report for Combined Text Summary Field

Question

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.

MarkShnierYou · Answer

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,";"))<>"")

Sign up

Login to the community