Dynamic Row Level Security with multiple factors

[bcobrien1977]

Hi, 

I have several contracts where I only want the data to be seen for employees in that contract and some employees do work on multiple contracts. 

Here is an example with all of the data. I want contract A to be selected and the only the A contract to be displayed.  

[Contract] = LOOKUPVALUE(Contract[Contract],Contract[Contractor Email],USERPRINCIPALNAME())

Here is what I am using to do the RLS but I cannot get this to work with an employee that works in multiple contracts.  Can anyone please assist?  Thanks

[v-xinruzhu-msft]

Hi @bcobrien1977 

You can create a new  table and input the useremail and the contract you want to the user to watch.

e.g 

Then create a one-to-many relationship between two tables.

Then create a rls 

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[bcobrien1977]

Thanks for doing this. When I try to test this, using Contract, it does not filter this.

I see you have email adresses input that don't reconcile to the users I provided. 

Can you please explain a bit more how this works?

Again, Contract A employees would see onliy contract A, b would see B and so on.

Thanks

[v-xinruzhu-msft]

Hi @bcobrien1977 

What I provided is only test data, you need to modify the email address to the corresponding user's email (this email needs to be the email corresponding to userprincipalname).

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.