Security filter using Enumlist

[Wallace_Service]

Hi everyone!

I have a security filter on a table called “ServiceTicket” using the following expression:

AND([Date]>=TODAY()-90, IN(SELECT(Local employees[TechnicianUseremail], [Division]=“Door”),[Assigned to]))

I have recently changed the [Assigned to] column in the “ServiceTicket” table to an Enumlist that references another table called “Local employees”. The “Local employees” table has a key column called [TechnicianUseremail].

The purpose of the security filter is to show any record where a value in the ServiceTicket Assigned to column matches any of the Local employees rows selected by way of TechnicianUseremail column where the Division column equals Door.

My issue is the security filter doesn’t actually show anything. It’s not the [Date] portion - if I isolate that portion the filter works.

I feel like I’m missing something simple but I can’t quite figure it out. My first thought was my select statement in context of IN isn’t workable. If I wrap the Select statement with Any I do get some records showing. That makes me think this issue is there.

Has anyone solved a similar/same problem before?

Thanks!

Justin

[Suvrutt_Gurjar · Accepted solution]

Could you please try

AND (
[Date]>=TODAY()-90,
ISNOTBLANK(INTERSECT(SELECT(Local employees[TechnicianUseremail], [Division]=“Door”),[Assigned to]))
)

[Steve]

For reference:

help.appsheet.com

INTERSECT()

Items common to two lists

[Wallace_Service]

Thanks @Suvrutt_Gurjar and @Steve! That did work. I haven’t used the Intersect function before but have read up on it now. I do have one question - why ISNOTBLANK()? Is that just because we don’t want two null values to end up matching?

[Suvrutt_Gurjar]

Hi @Wallace_Service ,

A FALSE on ISNOTBLANK() denotes that there is nothing common in two lists. So basically none of the emails in Local Employees table in the “Door” division matches that in the Enumlist [Assigned To].

So the FALSE on ISNOTBANK() filters “out” that record while a TRUE filters " in" that record.

To describe it in another way, INTERSECT() still returns a list that cannot be used in filter expressions needing a TRUE or FALSE. ISNOTBLANK() wraps the INTERSECT() list with a TRUE/FALSE result that can be used in AND() , OR() and such TRUE/FALSE returning filter expressions.

[Wallace_Service]

Got it, thanks @Suvrutt_Gurjar !

[Wallace_Service]

Hi @Suvrutt_Gurjar, I’m using the INTERSECT() function in a different app for a security filter again.
In this case, I have a ProjectVisit table with an AssignedTo enumlist column referencing an Employees table. The Employees table has a key column of EmployeeEmail which is the employee’s USEREMAIL().

I’m trying to use the intersect() function in a security filter so the project visit will only show to the users in the AssignedTo column of the ProjectVisit table if they also have an EmployeeType of “Foreman” in the Employees table.

So I have something like this as a security filter of the ProjectVisit table:

ISNOTBLANK(INTERSECT(SELECT(Employees[EmployeeEmail], [EmployeeType]=“Foreman”),[AssignedTo]))

However, the project visit record will still show to a user whose EmployeeType is something other than “Foreman”.

My understanding of the statement is that the record would only show to those in the AssignedTo list that that had a EmployeeEmail that was in the AssignedTo list and also in the EmployeeEmail list filtered by EmployeeType of “Foreman”.

Any tips as to what I’m doing wrong?

Thanks!

Justin

[Suvrutt_Gurjar]

Hi @Wallace_Service ,

Your expression and logic seems to be in order. Did you take a look at the test results when you run this expression in the security filter?

[Steve]

Wallace_Service:

However, the project visit record will still show to a user whose EmployeeType is something other than “Foreman”.

https://community.appsheet.com/search?q=@steve%20quirk