Re: Security filter not working with IN()

1minManager · 09-21-2023 04:06 AM

I've got an App that I'm trying to speed up by using security filters to reduce the number of synced rows. To check that this is working I'm using various USERSETTINGS columns to do a simple:

COUNT(Table[KeyColumn]

Most are fine but one security filter for the Site table always returns all rows. Which is PITA because that tables has got 26,000+ rows 😭. Here the security filter is set to use:

IN([Team],Select(Staff[Locations],[Login Email]=UserEmail()))

Site[Team] is an ENUM, Staff[Locations] is an ENUMLIST. Using a USERSETTINGS column I can see that Select(Staff[Locations],[Login Email]=UserEmail())) results in "London". Yet if the filter was working this should return 4000ish rows, but its pulling in 26000+.

I've tried the following for the security filter:

IN([Team],ANY(Select(Staff[Locations],[Login Email]=UserEmail()))
IN([Team],LIST(Select(Staff[Locations],[Login Email]=UserEmail())))
IN([Team],LIST(SPLIT(Select(Staff[Locations],[Login Email]=UserEmail()),",")))

But all the above returns all the rows. The only way I got it to reduce was to user [Team]="London". Then it shows 4000ish but this is obviously not usable.

Anyone any ideas of what I'm getting wrong here? Would CONTAINS() be better as all Site[Team] choices are quite distinct? Does it make any difference that I'm also using "Preview App as:", not sure that it does as other security filters seem to work fine.

Simon, 1minManager.com

Suvrutt_Gurjar

@1minManager wrote:

Site[Team] is an ENUM, Staff[Locations] is an ENUMLIST

Could you once try the following expression

IN([Team],SPLIT(TEXT(SELECT(Staff[Locations],[Login Email]=USEREMAIL())),","))

jyothis_m

😀saw this only after posting

Suvrutt_Gurjar

No problem at all. I understand. Happens with me also when I post unknowingly when someone has already posted. 🙂 I think once the response window is open we have no way of knowing if someone else has already responded.

jyothis_m

And i think text() (as you posted) should be used instead of any() (in my expression) which will combine the list of lists into list whereas any() will consider only one list from the list of lists

Suvrutt_Gurjar

Correct, if the SELECT() will return many [Location] values, then ANY() may not work. On the other hand , I believe I mistyped the space between comma in the SPLIT() , that you have properly mentioned. So revised suggested expression could be

IN([Team],SPLIT(TEXT(SELECT(Staff[Locations],[Login Email]=USEREMAIL()))," , "))

1minManager

No this didn't work

IN([Team],SPLIT(TEXT(SELECT(Staff[Locations],[Login Email]=USEREMAIL()))," , "))

jyothis_m

can you try IN([Team],SPLIT(ANY(Select(Staff[Locations],[Login Email]=UserEmail())," , "))

1minManager

No same result with this unfortunately

jyothis_m

Any improvement if using [_ThisRow].[Team] instead of just [Team]

1minManager

Already tried that, no

1minManager

So to further add something that might help. In UserSettings:

[COUNTSITES]
= COUNT(Select(Site[SiteID],IN([Team],ANY(Select(Staff[Locations],[Login Email]=UserEmail())))
RESULT = 4134

Whereas also in UserSettings

[Sites]
= COUNT(Site[SiteID])
Security filter of Sites
= IN([Team],ANY(Select(Staff[Locations],[Login Email]=UserEmail())))
RESULT = 26167

scott192

Create a Slice on Staff with a row filter of [Login Email] = USEREMAIL()

Then use your slice in your comparison row filter

IN([Team],StaffSlice[Location])

1minManager

Set slice as

CurrentUser=[Login Email]=USEREMAIL()

and SecFilter as

IN([Team],CurrentUser[Locations])

didn't work 😞

scott192

Let's get the lowdown on the field values.

[Locations] is an ENUMLIST. ENUMLIST of What? Text? Refs?

[Team] is an ENUM. Should it be a Ref?

Can you show us the two columns from the different tables in your backend sheet so we can see the sort of data that is being stored?

Marc_Dillon

You definitely have to do a SPLIT(TEXT()) anytime you are SELECT()ing a List type column, to flatten the list of lists. I recommend always using the standard delimiter of space-comma-space, but if you sometimes don't, make sure to test with different SPLIT delimiters.

1minManager

Tried

IN([Team],SPLIT(TEXT(Select(Staff[Locations],[Login Email]=UserEmail()))","))

and

IN([Team],SPLIT(TEXT(ANY(Select(Staff[Locations],[Login Email]=UserEmail())))","))

neither worked. The ANY() shouldn't matter as i know [Login Email]=UserEmail() will only return one row