OR statements when searching for records using Option Sets

Looking for some assistance. On a record I have a list of values from an option set: Category 1, Category 2, Category 3

How do I do a search to find records that contain Category 1 OR Category 3?

You can:

  1. Use Advanced: filtering (keep in mind that this operator is applied client-side, so you’ll use more WUs for fetching data from the DB) if you are comparing two lists like List A = Thing’s list of options) with List B = some list of chosen categories (for example, from multidropdown or some list custom state).
  2. Merge 2 searches: Do a search for Thing where Thing’s list of options contains “Category 1” :merged with Do a search for Thing where Thing’s list of options contains “Category 2”

How do I get advanced to appear?

I found that if I do the merge, then I can’t sort the entire list in a RG by a field, it seems to only put one sorted list after the other sorted list rather that sorting the two lists as one?

Apply :filter operator to your search. And there you’ll have the Advanced: option.

Have you tried to activate new expression composer to get parenthes functionality and apply :sorted operator to the result of the merge?

Screenshot 2024-02-18 at 23.44.55

Thank you!

No - I will try this.

I’m almost thinking it would just be easier to use a list of text instead of option sets as they seem quite limiting. Is there a benefit to option sets over text?

@psten00
One of recent @georgecollier forum replies reminded me about the third option - compiling a list of constraint values.
So in your Do a search expression you’ll have something like this:
Category is in Category 1:converted to list:plus item Category 3
This approach is more scalable and comfy cause if you’ll need to extend the list with additional category - you’ll just need to apply :plus item Category X to the expression above. While dealing with merging searches (option 2 from my post above) you’ll need to :merge with a new Do a search expression.

BTW, somehow I’ve decided that you are going to hardcode constraint values right into the data source expression. IDK why that came to my mind, may be I was too tired :grinning:
So are you looking for an option to hardcode values right into the search or your real question was about making dynamic searches (user chooses multiple categories - list of Things is filtered by chosen categories)?