I will keep using the Basket/Fruit example for our sake.
Basket:
Number (Number)
Fruits (List of fruits)
Key (Text)
Imagine that every time that you create or edit a Basket, you also modify its key. The key is a text containing all the unique_ids (formatted as text) of all the Fruits inside it.
So, a Basket with Apple and Banana will have this configuration:
Now, you can use the Search for to Search for Basket where the Key contain the keyword(s) you want. In this case, you will always use the Fuit’s unique_id as keyword.
The issue is that this still just works with Foods that are equivalent to option sets. Even though they’re split out into a table, there’s only one Banana instance. We need an instance for every Banana out there (where each instance will have its own serial number).
Edit… realizing that option sets don’t give you the opportunity to grab a unqiue ID… so I supposed I’d need to create a new field in the option set that has the phrase with hyphens, to prevent multi-word options pulling up other multi-word options that contain some of the same words.
I played with it by creating a new field in the option set that contained the display words without spaces, effectively creating a unique word that could be used for searching. And in my datatype entries, I created a text field that could be populated by unique words from the option set selections.
Searching worked, but it needs to be “contains keywords” since “contains” was looking for words to be adjacent to each other.
However, this is an “AND” search, meaning that all the selections need to be in an entry for it to display. I can’t see a way to do an “OR” search (ie show me results that contain ANY of the selections) via this method.
I was having a very disappointing experience with speed using advanced filters, and I need to enable the function to have users select a list of filters and match that against a list of items as a datafield on the data type being searched.
Before finding this thread I was toying with the idea of turning things into text.
Following the ideas here I implemented a strategy that does make the search significantly faster (from 8 seconds down to 0.5-1 second) and avoids the issue of the fact the ignore empty constraints function does not work on advanced filters. It also allows me to have a search constraint that looks to see if a list of items doesn’t exist in the datafield list, something I could not achieve using the advanced filter either.
What I did is I created a data field on my data type that is a text field (note, this is not a list, it is just a single text, however, it is a single text with commas separating the different items).
So, if it were the fruit example the data field looks like (apple, orange, strawberry), but again this is a text, not a list of text. Because it is a text, I get the search operator ‘contains keywords’. On the data type I call the data field ‘Filter Text’.
My search uses the constraint on the search itself (so it is server side and not client side = faster).
Filter Text contains keywords
My value for that constraint is actually coming from a URL parameter in which I have a list of values.
Thank you for sharing bubbles issue with advanced filtering.
I need to make a search with two filters, so i was using advanced filtering as a wild card. I have a table that could potentially grow to 50K things and don’t want to have lag issues in the future. Somehow i made it by using nested “searches for” and think this would be far better than doing it on the front end.
. That is why I used the unique_id instead of the Fruit’s name.
.
