What’s the best way to filter an RG with multiple lists? for example one list needs to return matches and one list to exclude matches.
For example this is the RG content:
Title
Genre
Catalogue
move 1
Action
Cat1
movie 2
Sports
Cat1
movie 3
Action, Drama
Cat1
movie 4
Drama, Thriller
Cat2
movie 5
Sci-Fi, Action
Cat3
I want to filter the genre by Action and Sports, but i want to also exclude cat2 and cat3 from catalogue. i should in theory be left this:
Title
Genre
Catalogue
move 1
Action
Cat1
movie 2
Sports
Cat1
movie 3
Action, Drama
Cat1
i’ve been able to stack multiple :intersect with’s fine but cannot figure out how to exclude a list of matches from the results at the same time using :minus list, it just seems to overwrite any filtering performed by :intersect with. As i’m always trying to filter with lists i can’t use use the constraints in do a search for.
i’ve also tried building a workflow where i save the entire list into a custom state, and then trigger a custom event have multiple steps to gradually keep filtering custom state and use the custom state as a data source, this didnt work either it kept returning a blank RG.
Super appreciate any help as i really need this to work and i’ve been working on it for far too long.
Do a search for table:
cat is not cat2
cat is not cat3
genre contains action
merge with
Do a search for table:
cat is not cat2
cat is not cat3
genre contains sports
You can also use advanced filter In that case you don’t need to do a search for twice but advanced filter will slow down the loading of repeatinggroup.
FYI: in advanced filter you can use this expression this table genre intersect with state count > 0 where state should be pre populated with action and sport
not sure i’m following exactly as your example would only work with static data? the filters are lists which are dynamic, for example there’s 50+ genres and the filter could be any number of these (same with catalogue). Constraints don’t let you filter against lists as far as i’m aware.
I’m saving the filters in the User datatype, i created 4 fields to hold the various filters:
genre filter
genre exclude
catalogue filter
catalogue exclude
i’ve tried various advanced filter approaches which work individually for example:
and this works:
But i cannot find a way to combine them… it either just doesn’t work at all or the minus list filter overwrites the intersect with filter when combining them with OR.
Tried setting a custom state with the master list and then with a new custom state have a workflow where each step adds a new :filtered to the same state, then use that state as the datasource for the RG, didn’t work either. also tried do a search for with one filter :merged with do a search for with the next filter, but the last filter overwrites the first.
Ok so i figured out one solution for this… but i don’t know if this is really the best solution, feels a bit like a hack to me as i don’t know bubble well enough, but here’s what i did/found out.
The main issue i found was that one of the fields i wanted to filter was a list which meant using constraints wouldn’t work, so i thought what if i still use intersect to filter the search but now return the value of a single value field instead (I use SKUs in my database which is a single ID for each title), then use that field in the constraints.
Solution:
I created a workflow on page load to do a search for the entire list and store it in a custom state.
the next step i used a different custom state to filter this list (i had 4 different custom states for the 4 filter options, below is an example of one)
This way i can now use “is in” and “isn’t in” to match against the lists of SKUs, as they’re constraints they don’t overwrite each other.
Filter/Exclude is an OR option so genre filter and genre exclude can’t be active at the same time, therefore one would be empty which is why i needed to tick ignore empty constraints.
