Forum Discussion
Multilevel RLS with userprincipalname
Hello,
You need a table with unique employees and their email/login. This will be the starting point of the RLS filter;
Emp1 | [email protected]
Emp2 | [email protected]
Emp3 | [email protected]
Emp4 | [email protected]
The second table you need is a table with employees and their children, or parents and all employees they have access too;
Emp1 | Emp1
Emp1 | Emp2
Emp1 | Emp3
Emp2 | Emp3
Emp2 | Emp4
Now, Emp1 will filter the second table and only filter out Emp1, Emp2 and Emp3. No access to Emp4.
The last table is your transaction table with sales or whatever, and this will be connected to table 2 based on employee;
2019-01-01 | Emp2 | 50 Sales
2019-02-02 | Emp4 | 100 Sales
Now, Emp1, will only see a total number of 50 sales, since there is no access to the second row of the transaction table.
Hope this helps to model your data in the right way.
Regards,
Adrian
Emp1 |
Thanks for your quick response avanderschilden
This is exactly where I got stuck: Emp 1 in your use can see Emps 2 & 3.
But because Emp 4 reports to Emp 2 who in turn reports to Emp 1, I want Emp 1 to be able to see figures for Emp 4 as well. Kind of like a waterfall RLS from the top node to the most bottom node in that chain for Emp 1
Regards,
Bare
- So why dont you just add a row with;
Emp1 | Emp4
In that case you have what you need right?
You just have to store your table with parents and childs different, without level. Just 2 columns with a parents and child.avanderschilden It's a good idea worth trying. I was avoiding any manual maintenance (the employee file comes from a separate system) that I didn't think of this. I'll simply carve out a 2 column solution with Power Query and let you know how it goes.
Many thanks