@praveen Thanks Praveen for your suggestion.
+Steve Coile Hi Steve, I can’t thank you enough for this assistance. Much Appreciate it. The code worked perfectly. To help others who may be looking to do similar functions, I am detailing below what I did to make department security work.
- Staff (Table Name) |
Columns - Email, Manager? (Yes/No) 2. Departments (Table Name) | Columns - Email, Department
Included the column names - [Department] & [Owner] (With initial value USEREMAIL() - Email of the user who creates the line of data) in the data table. The column [Department] is referenced to table - Departments[Department] (Department column of table Departments).
This enables the data column to fetch the value of department automatically using the email of the user.
Security Filter on the Data Table:
OR(LOOKUP(USEREMAIL(), Staff, Email, Manager?), IN([Department], SELECT(Departments[Department], IN(USEREMAIL(), LIST([Email])))))
This perfectly did the job.
Now, I have Managers with overall visibility of all departments and Departments are separated to access their own data.
Thanks heaps Guys.