I know the “Email = USERPRINCIPLENAME()” trick. I’m trying to take it a few steps further and integrate linked information from other tables.
In my model, there's an Employee table with Employee ID, Name, and Email.
Example Employee Table:
Every employee at my company belongs to some number of groups. Each group has an Employee Group Code, Description, and Type. Right now, I'm only interested in the Management type.
Example Groups Table:
Each management type group has employees who are assigned to Roles: one Primary Supervisor and any number of Backup Supervisors and Workers. These are linked on the Employee Roles table, which contains Employee ID, Employee Group Code, and Functional Role.
Example Employee Roles Table:
It's all linked together like this:
One Employee to many Employee Roles, one Group to many Employee Roles, and several employee detail tables with a many to one relationship with Employee.
I want Primary Supervisors and Backup Supervisors to be able to view information about the Workers in groups to which they are assigned, but I do not want Supervisors to see each other or Workers to see information about Supervisors. (Bonus points if I can allow any user to see information about themselves, though!).
Example Viewing Permissions:
I've tried separating the Employee Roles table into Workers vs Supervisors, but I'm still stumped on how exactly to make this security work. I know the logic I need is something like:
- Find Groups where Current User's Role is Primary Supervisor or Backup Supervisor
- Current User can see details about Workers who belong to these Groups
- (Bonus) Current User can see details about Current User
Any direction for me?