I am having an issue with the expression that limits the staff personnel to seeing only the projects assigned to them.
ADMIN and Manager need to see all
Staff see’s only the projects assigned to them
Will request you to post such expressions in textual format rather than as screenshot because if someone wishes to suggest a change or solution, then she/he can easily make changes without the need to retype the entire expression again.
I anyway used Google Gemini studio to extract the text from the screenshot shared by you. 
Assuming you are using the expression in a security filter , you could try an expression something like below
OR(
AND(LOOKUP(USEREMAIL(), “Employee”, “EMAIL”, “UserRole”) = “STAFF”, [Employee Id].[_ComputedName] = USEREMAIL()),
LOOKUP(USEREMAIL(), “Employee”, “Email”, “ADMIN”) = “YES”,
LOOKUP(USEREMAIL(), “Employee”, “Email”, “UserRole”) = “Manager” )
Please look for the epic tip of Current User system by @MultiTech in the Tips and Tricks section and you could simplify LOOKUP() to simply INDEX(Current_User[UserRole], 1) =”STAFF” and so on.
1 Like
Create a slice on the Employee table. Call it “ActiveEmployee” and set the condition to:
[Email] = UserEmail()
Then you can reference this slide without doing a UserEmail() lookup. It is a UserEmail lookup.
Wrap it in ANY to return a single record - which should be the only Employee record with that email.
So…
ANY(ActiveEmployee[Emaill]) returns the email field.
ANY(ActiveEmployee[ADMIN]) returns the ADMIN field.
etc. etc.
The above formula can then be:
OR(
AND(ANY(ActiveEmployee[role]="Staff",[Employee Id].[Email]=UserEmail()),
AND(ANY(ActiveEmployee[Admin])=TRUE,ANY(ActiveEmployee[UserRole])="Admin"),
AND(ANY(ActiveEmployee[Admin])=TRUE,ANY(ActiveEmployee[UserRole])="Manager")
)
Of course, if ADMIN is true, does that automatically provide access? Meaning, are only “Admin” and “Manager” listed as ADMIN?
2 Likes
Yes ADMIN and Manager is ADMIN they have full access to the table {Project}
Ill try your suggestion I’ve never looked at it that way before.
OR(
AND(ANY(ActiveEmployee[UserRole]=“Staff”,[Employee Id].[Email]=UserEmail()), AND(ANY(ActiveEmployee[Admin])=TRUE,ANY(ActiveEmployee[UserRole])=“Admin”), AND(ANY(ActiveEmployee[Admin])=TRUE,ANY(ActiveEmployee[UserRole])=“Manager”) ))
I get this error
“=” requires arguments to be of comparable types. First argument must be File or Text; received List
I have written that expression and a variation of it with the same conclusion’s
OR(
AND(LOOKUP(USEREMAIL(), “Employee”, “EMAIL”, “UserRole”) = “STAFF”, [Employee Id].[_ComputedName] = USEREMAIL()), Or replace the [_ComputedName] with [Email]
LOOKUP(USEREMAIL(), “Employee”, “Email”, “ADMIN”) = “YES”,
LOOKUP(USEREMAIL(), “Employee”, “Email”, “UserRole”) = “Manager” )
Both result in the ADMIN and Manager seeing all which is ok that right
The UserRole “STAFF still sees all also. It’s like its not singling out the USEREMAIL() in the “STAFF expression.
Yes, there should be [Email] and not [_ComputedName] if separate [Email] column is available.
I believe the following should work.
OR(
[Employee Id].[Email] = USEREMAIL(),
LOOKUP(USEREMAIL(), “Employee”, “Email”, “ADMIN”) = “YES”,
LOOKUP(USEREMAIL(), “Employee”, “Email”, “UserRole”) = “Manager” )
3 Likes
Hey Jack,
I’m not sure about the error message except I don’t really know your data.
I’d test each statement for what it returns. I was looking as [Admin] as a yes/no (TRUE/FALSE) field.
With the ActiveEmployee slice set up properly:
ANY(ActiveEmployee[UserRole]) is the same as:
Lookup(USEREMAIL(),”Employee”,”Email”,”UserRole”)
If ANY(ActiveEmployee[Admin])=”Yes” works, it should be swappable in my example…
So, a modified version of what I posted previously would be:
OR(
AND(ANY(ActiveEmployee[role]="Staff",[Employee Id].[Email]=UserEmail()),
AND(ANY(ActiveEmployee[Admin])="Yes",ANY(ActiveEmployee[UserRole])="Admin"),
AND(ANY(ActiveEmployee[Admin])="Yes",ANY(ActiveEmployee[UserRole])="Manager")
)
If I get a moment, I’ll try to recreate enough of your app & data structure to create a slice with the above concept in mind.
1 Like
Oops. Thanks Steve!
The problem with theoretic solutions that I am not testing. 
1 Like
We’ve all been there!
1 Like
You know the code I use. This is the code I use, where I show the tasks or notices. For example, if the user who logs in has a higher access role, he can see all the notices of all the users apart, he is conditioned if he is an active user. If he is a regular user, he will only show the notices of that user, I do all this from a request leaked in a table.
IF
(
IN(LOOKUP(ANY(SELECT(INICIODASHBOAR[correofiltro],
[UserInicio]=USEREMAIL(),TRUE)), "EMPLEADOS", "Correo", "Nombre_Rol"),
{"Admin","Usuario Avanzado","IT"}),
[Estatus]=ANY(SELECT(FiltroDashAviso[SelectStatus],[Filtrar]=TRUE)),
AND
(
[Estatus]=ANY(SELECT(FiltroDashAviso[SelectStatus],[Filtrar]=TRUE)),
[Nombre]=
ANY
(
SELECT
(
Aviso[Nombre],
[CorreoRecibe]=ANY(SELECT(INICIODASHBOAR[correofiltro],
[UserInicio]=USEREMAIL(),TRUE))
)
)
)
)
1 Like
I made your ‘s work with the slice when I added the closed parenthesis I missed it and was looking right at it.
It works Thank you I have used it on another app i was having a similar issue with
2 Likes