Security Filter Expression Strange behanvior

[Jonathan_S]

Hello Everyone,

Just tried to launch my online for our vendors and No can Do.

Very Strange,

I have a security Filter than is the Following

OR(In(useremail(),Admin Emails[Email]), In([ID], SELECT(Security Filters[Vendors], [email]=useremail())))

Note, this expression could significantly impact sync time.

ANY of these statements is true:
…1: (USEREMAIL()) is one of the values in the list (ADMIN EMAILS[Email])
…2: (The value of column ‘id’) is one of the values in the list (The list of values of column 'Vendors’
…from rows of table 'SECURITY FILTERS’
…where this condition is true: ((The value of column ‘Email’) is equal to (USEREMAIL())))

Admin Emails Work perfecly, Everyone onsite here can see everything. I removed my own email from admin emails and added to a few vendors in the Security Filter Table, and this works completly fine, problem is when somone elses email is added to security filter table for certain vendors they see nothing.

Security Filter Table

Vendor Table

I’m have no idea what I’m doing wrong and thought i had it all figured out. Also tried contains etc but to no avail. I dont understand why it is working for my email and not for others. Extremly frustrating. Ugh

[tony1 · Accepted solution]

Hi @Jonathan_S. What type of column is Vendors? The fact that it’s plural makes me suspect that it’s an EnumList. If that’s the case, then SELECT(Security Filters[Vendors], [email]=useremail()) will return a list of EnumList values. That means you’re checking to see if ID is in a list of EnumLists, which will always return false.

If that’s all correct, then I could see two different ways of fixing this:

(1) Change your security filter to check to see if there are any rows that have ID in their vendor lists. Something like this:

COUNT(
  SELECT(
    Security Filters[Id],
    AND([email] = USEREMAIL(), IN([_THISROW].[ID], [Vendors]))
  )
) > 0

(2) Change your Security Filter table to have a single vendor per row (instead of an EnumList). Then your security filter would have something like:

IN([ID], SELECT(Security Filters[Vendor], [email] = USEREMAIL()))

(note that Vendor is singular - that’s the change to your schema).

[Jonathan_S]

Thank you sir