Security filter using Enumlist

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

Could you please try

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

3 Likes

For reference:

2 Likes

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?

1 Like

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.

1 Like

Got it, thanks @Suvrutt_Gurjar !

2 Likes