I have been struggling with creating a filter that solves this problem for a while.
I have a list of records with one concatenated field showing all of the possible characteristics for a given record, looking something like as follows:
User
Shirt Color
Person 1
Blue
Person 2
Blue, Green, Red
Person 3
Red, Green
Person 4
Green
Person 5
Red, Blue
I am trying to make a filter that Has the options of the three colors and searches for any instance of that color appearing.
My first solution was = If( [Shirt Color] like '*Blue*', 'Blue',
If( [Shirt Color] like '*Green*', 'Green',
If( [Shirt Color] like '*Red*', 'Red')))
However this only picks up the first true value, so person 1, 2 and 5 will be marked as "Blue" and wont be counted as "Green" or "Red" even if they have that value recorded in Shirt Color.
I then tried to break up the field into three separate fields (Red Shirt, Blue Shirt, Green Shirt) and created 3 filters based on these. But when I select "Yes" for Red Shirt and "Yes" for Blue Shirt, it only returned values where a person had both Red and Blue Shirts as opposed to anyone with a red shirt (regardless of blue shirt) and anyone with a blue shirt (Regardless of red shirt)
Was wondering if there was a solution to this, has been driving me crazy!
Wouldnt this still give me the same issue I outlined here:
"I then tried to break up the field into three separate fields (Red Shirt, Blue Shirt, Green Shirt) and created 3 filters based on these. But when I select "Yes" for Red Shirt and "Yes" for Blue Shirt, it only returned values where a person had both Red and Blue Shirts as opposed to anyone with a red shirt (regardless of blue shirt) and anyone with a blue shirt (Regardless of red shirt)"
Seems like using multiple filters is acting as an And function (Red shirt AND Blue Shirt) when I want an Or (Red Shirt Or Blue Shirt)
When using SubField() you would be filtering on a single field, so if you were to select Blue and Red, you would get anyone who matches either one. Again, this would need to be done in script, since SubField() doesn't split values into rows when used at the front-end level. SubField(Shirt Color,',') would essentially create
