This is related to Row-level security. Here is a dummy table on which I want to implement this RLS.
The RLS formula I am using right now is -
[location] = LOOKUPVALUE([location],[login],USERPRINCIPALNAME())
My end requirement is to make this RLS work on the case where if a user logs in he should see the data if the location he is in and also should see other users who are in the same location.
But lookup is throwing an error 'A table of multiple values was supplied where a single value was expected'
Also, a single user can have access to multiple location and a single location could have multiple users.
Please help me on how I can achive this level of Row-level security using either Lookup or using any other possible way.
Thanks a lot!
You should use a table with all your users, must be single values, that connects with your database using bidirectional and security filters .
Well, I have tried that but by this way RLS will only filter based on Username and not on location. End result should be that if a user logs in, he should see all other users too who are in same location.