Help with do a search for:filtering many to many using a custom state list based on an option set list

:grimacing: :exploding_head: :dizzy_face:

Hi all. I can’t quite seem to sort out how to “do a search for” using an option set list when multiple choices in that list are chosen through a custom state (ie “name of element” “name of custom state” (set to a list): plus item 1 from option set, plus item 2 from option set).

From what I can tell you can’t “filter” your search server side when working with lists of lists and instead must “filter” on the page ie “do a search for: filter”.

What expressions are possible when using “Advanced” and working with lists of lists? And when would you use each? Here’s some screen shots of ones that seem possible - but given they don’t behave the way I guess they would I am getting lost :exploding_head: !Can anyone provide guidance on ways to approach many to many (or list of lists) using custom states and option sets? I’d super appreciate it!

I’ve tried to read all the great posts related to this topic by @mikeloc @ed727 @romanmg @boston85719 but am still missing something.


From what I understand, you should use the :intersect. Intersect take two list and return items that are in both. Using count like you do will return how many items are the same.
I suggest you to use “inspect” when you are un debug_mode=true and check each part of your condition. You should be able to find where this fail. Be sure that both list are the same type.

You can also make your app public view mode and someone can have a look and help you. It’s sometimes easier to see how you have set thing this way and why it may not work.

Thanks for the comment and suggestions @Jici !

1 Like

To add to @jici’s comment… yes, you’ll need to use advanced filter and intersect with. Use the :count to determine how many intersects there should be to make the condition true (at least 1? 2? exact number?).

Intersect with is advanced filter, which is client side, so with a huge list it will be slow. I understand it’s possible to do this type of search server side via API worklows, but I haven’t had time to figure it out.

If you do a one to many search that can be done client side through the contains or is in type stuff.

PS: I may be reading your post incorrectly, but make sure you are doing an intersect with to find common ground between your option set selections and the data type you are filtering, (instead of the option set selections and the option set itself).

Thanks @ed727 for the feedback. Appreciated! :grin:

I’ve never used the intersect method and not sure if it would return different results than the method I use.

I also don’t use custom states in most cases and opt to use URL parameters, so that the user can navigate using browser navigation and not lose their search…this also allows them to save the URL to the DB as a single text field if they want to save a search.

Here are some screen shots from an application I use during a lesson on Data Modeling in Bootcamps I run.

Screen Shot 2021-03-23 at 12.59.04 PM

Notice in the Restaurant Data type some fields are lists of options while others like Brooklyn Neighborhood is a single option.

Then on the element holding the search results this is the setup for the search using the filters

And here is an example of a search result using only two filters for cuisine type

In that screen shot only 2 restaurants search both American and Seafood, although there is a 3rd restaurant that serves American, however, it doesn’t serve Seafood so it is omitted from the results.

I believe this may be the difference between using intersect with VS the method I employ, however, I am not familiar with the intersect method and how it returns results.

And a final screen shot below of using three filters on cuisine type

1 Like

Interesting… I am going to play around with the contains list to see how it operates, but from your example it looks appropriate for “and” filtering where all of the cuisines need to be in a result. Intersect allows you to construct an “or” filter, which would return restaurants that contain any of the cuisines selected. (Or to take it a step further, you can control the number of matches required by using the :count. You can construct an intersect with to work as an “and” by having the count of the selections match the count of the intersects).

Thanks so much @boston85719 ! I am going to try out your URL parameter method in a bit (as your rational for doing what you do makes really good sense) and I really appreciate the screen shots and details! I will definitely write a more comprehensive post once I am done too with the various solutions I have found. Going back to using custom states and intersect I realize now thanks to @ed727 I was trying to intersect the wrong thing for one of my more simple " filters".

Yesterdays Learning:
If you are trying to “do a search” for a field that is based on an option set list (where the list returns multiple items) and you are also using a custom state list and that list too is also based on the option set list (and again can return multiple items) you need to: 1. “do a search for:filter” ie use filter which will filter results on page (or alternatively possibly use a backend workflow and api call but we aren’t going there for this solution) 2. make sure to use the actual data type/data field in database and reference that against the custom state using an “advanced” expression ie filter will be "advanced = “this data types” “data field” “intersects with” “custom states element” “customs state”: “count” >0. Where I was going very wrong was trying to intersect the custom state with the option set list. The reason this wasn’t working in do a search for I believe is that my search was simply happening only on the browser (as option sets only live in the browser).

Yes, I suspected the intersect would be different than the contains list in the way you mentioned ( or vs and )…I’ll have to play around with intersect to see some good use cases…I guess it would all come down to the UX design for which to utilize.

When using the intersect with :count does it make it somewhat random…for example if I select :count = 2 and my user has selected four filters, does it show results who has any 2 out of the 4 selected filters?

Intersect example
List A: Option 1, Option 2, Option 3, Option 4
List B: Option,1 Option 2, Option 5, Option 6
Intersect will return: Option 1 and Option 2. They are in both list. It doesn’t return true or false here. If you use count, it will return 2 for this example. So if you just wan’t to know if any item in the list B is in the list A, > 0 is perfect.
Contain list will return true only if ALL elements of List B are in List A (if you set List A contain list…)

1 Like

Ok one more big thanks @Jici @boston85719 and @ed727 !

In my case my first filter needed to be an “or” so intersects with worked great when count>0. My second filter needed to be “and” so “contains list”. Each filter was looking at a different field in DB that was based on an option set list that was matching (“or” in case 1 and “and” in case 2) to a custom state list that was using the same option set list. Final quick answer I needed is below. Thanks you SO much! I look forward to learning the URL parameter method too. :smiling_face_with_three_hearts:


This topic was automatically closed after 70 days. New replies are no longer allowed.