Can you combine intersect with and minus list in one filter?

rob26 · 2024-09-24T06:34:49.781Z

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.

Qaiser · 2024-09-24T06:48:54.312Z

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

rob26 · 2024-09-24T22:08:22.357Z

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:

Screenshot 2024-09-24 at 2.42.39 PM

and this works:

Screenshot 2024-09-24 at 2.57.54 PM

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.

rob26 · 2024-09-25T19:04:05.669Z

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)

Screenshot 2024-09-25 at 12.02.01 PM1374×366 40.2 KB
then created a new RG to appear when the Users role = filtered
As i now have 4 lists of SKUs based on the previous filtering, I set the data source for this RG as follows:

Screenshot 2024-09-25 at 11.56.31 AM680×856 39.7 KB
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.

system · 2024-12-03T06:35:16.082Z

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