What is the best way to filter a search where options in one list field need to be checked to see if they are in another list?

Lumyna · 2024-09-27T18:10:57.668Z

If I have a datatype “XYZ” with a list field Categories (A, B, C, D). And then want to filter this datatype by returning all records where any of the options in Categories are contained in another list (of the same type: A, B, C, D), let’s say this second list could be options selected in a filter.

How should I do this?

I’ve seen approaches that use Advanced Filter, this works, but for large lists it causes a huge delay.

I would like to know how you deal with this when Advanced Filter is not an alternative?
Is there any plugin that does this?

msgiblin · 2024-09-27T20:13:07.492Z

Hi there,

If the list of ‘Categories’ is within reason or a controlled amount of things a user can add to it, you could essentially do this by hand to avoid an Advanced Filter.

If you need to ONLY show results that contain ALL of the items in the XYZ datatype:
Do a search for: XYZ Datatype

Constraints:

Categories contains 'Options List:Item #1'
Categories contains 'Options List:Item #2'
Categories contains 'Options List:Item #3'

etc, until you have the max amount of categories that would be selected by a user in a filter. Obviously this has some scaling issues if you’re talking about increasing the amount of items selected in the filter. I use this technique by allowing users to select up to 3 ‘characteristics’ on a recipe (i.e. Savory, Sweet, Sour). In my use-case, once you get beyond 3, the chance of even finding a recipe with all the characteristics plummets. So I put a limit of 3, and built the 3 into the constraints.

system · 2024-12-06T18:11:20.126Z

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