I need to create custom filtering like in Pipedrive. Some fields for the criteria are fixed (DB fields) and some fields are dynamic/custom. The search comparison are all “dynamic”; user can change the comparison for each criteria. Has anyone done something this complex in Bubble? If so, I would appreciate some tips or best practices. 
Hi there, I do a mix of filtering on my cocktail recipe app KnowThyDrink - Search Cocktails (use the filters to play around).
I have found the best way to handle multi-constraint searches like this is ensuring the data is structured properly. I have a combination of fields on my recipe datatype for things like total calories, lists of “Characteristics” (an option set), "Allergens (option set), etc, but I also have a field of type text that lists all the keywords a user may be searching for (in this case, recipe name, ingredient names, the type of liquor, etc).
I have a series of properties on my reusable element, all of type recipe (list). The first is the simple constraint:
Recipe-List (with constraints)
Do a search for Recipe
The second property’s data is the previous data, filtered further:
Recipe-List-Filtered:
Recipe-List's List of Recipes: Filtered
What you can then do if you have a series of ANY conditions, is some additional :filtered searches on the Recipe-list-filtered data, but merge them all together - for example:
Recipe-List-Filtered's List of Recipes: Filtered: Merged with: Recipe-List-Filtered's List of Recipes: Filtered
etc.
One thing to note - using the ‘Filtered’ does not typically slow down the search unless you were to introduce a complex search query like ‘Advanced’. On it’s own, ‘Filtered’ behaves just like adding constraints to a search.
My suggestion is to input some dummy data you understand, and start playing around with the filtering until you get it to behave like you expect. There will be trial and error, but this seems reasonably possible.
Thanks for the reply. I assume the value of the criteria is dynamic, i.e. user selects a characteristics or an allergen, e.g. allergen is “peanut”. But the “comparison operator” in your filtering is fixed, right? That is, user can decide that allergen “is” peanut on the filter screen and then decide to change comparison operator to “is not” so the criteria becomse allergen “is not” peanut.
I haven’t quite gone with that approach (since in my use-case, people searching for recipes want to avoid the cocktails with specific allergens, as opposed to finding cocktails with allergens) but what I would suggest is to approach this with a series of custom states on that line, and use the combination of the 2x drop downs to set the stte. Using the allergens use-case, both custom states would be of type Allergen (list). One of them can be called “Allergens-Included” and the other “Allergens-Omitted”. When a dropdown’s value changes, both states are set. “Allergens-Included” is set to have the allergen when the dropdown Comparison is set to “Is”. When set to “Is”, “Allergens-Omitted” would be set to an empty state. And vice-versa.
Now in your constraints, Recipes:Filtered would have a constraint of:
Allergens Doesn't Contain (custom state's Allergens-omitted's allergen)
and
Allergens Contains (custom state's Allergens-Included's allergen)
And don’t forget to mark ‘Ignore empty constraints’. This means if either custom state is empty (i.e. no allergen is set), it’ll disregard that particular constraint and find recipes where the other constraint is true.