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
Solved! Go to Solution.
Could you please try
AND (
[Date]>=TODAY()-90,
ISNOTBLANK(INTERSECT(SELECT(Local employees[TechnicianUseremail], [Division]=โDoorโ),[Assigned to]))
)
Could you please try
AND (
[Date]>=TODAY()-90,
ISNOTBLANK(INTERSECT(SELECT(Local employees[TechnicianUseremail], [Division]=โDoorโ),[Assigned to]))
)
For reference:
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?
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.
Got it, thanks @Suvrutt_Gurjar !
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
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?
User | Count |
---|---|
40 | |
36 | |
34 | |
23 | |
17 |