Assigned Role Security Expression Help

[VLMark37]

I have set up the security hierarchy in my "Jobs" table as

Admin>Manager>Project>Manager>Driver

with the following security filter expression: 

IFS(
ANY(Current_User[Role])="Admin",TRUE,

ANY(Current_User[Role])="Manager",
OR([Reassign Driver]=USEREMAIL(),[Reassign Driver].[Role]="Project Manager",[Reassign Driver].[Role]="Sales"
AND([Reassign Driver].[Branch]=ANY(Current_User[Branch]),[Reassign Driver].[Role]="Driver")),

ANY(Current_User[Role])="Project Manager", OR([Reassign Driver]=USEREMAIL(),AND([Reassign Driver].[Branch]=ANY(Current_User[Branch]),[Reassign Driver].[Role]="Driver",[Job Type]="Project")),
TRUE,

OR([Reassign Driver]=USEREMAIL(),IN( USEREMAIL() , [Assigned Team] ))
)

I am have the only  "Admin" role.  I entered three test jobs in the system that I can see.  I have assigned my front desk person (Misty) and my operations manager (Scott) both the same "manager" role.

Here are the four test jobs, listing who is reassigned, who authored job and who can see job in their role:

Job#1 - Reassign Driver= Scott ("manager"), Record Owner=Misty("manager"), who can currently see=Lee("Admin"), Scott("manager")

Job#2 -   Reassign Driver= Tino ("driver"), Record Owner=Lee("admin"), who can currently see=Lee("Admin"), Tino("Driver")

Job#3 - Reassign Driver= Chris ("driver"), Record Owner=Lee("admin"), who can currently see=Lee("Admin"), Chris("Driver")

Job#4 - Reassign Driver= Amber ("Sales"), Record Owner=Lee("admin"), who can currently see=Lee("Admin"), Misty("manager"), Scott("manager"), Amber("Sales")

I would like the managers to be able to see all four jobs.  I know it's in how my expression is written.

Do you have any suggestions on tweaking my expression?

Thanks,

Lee

[dbaum]

You're missing a comma in the "Manager" block's OR series.

IFS(
    ANY(Current_User[Role]) = "Admin",
    TRUE,

    ANY(Current_User[Role]) = "Manager",
    OR(
        [Reassign Driver] = USEREMAIL(),
        [Reassign Driver].[Role] = "Project Manager",
        [Reassign Driver].[Role] = "Sales",
        AND(
            [Reassign Driver].[Branch] = ANY(Current_User[Branch]),
            [Reassign Driver].[Role] = "Driver"
        )
    ),

    ANY(Current_User[Role]) = "Project Manager",
    OR(
        [Reassign Driver] = USEREMAIL(),
        AND(
            [Reassign Driver].[Branch] = ANY(Current_User[Branch]),
            [Reassign Driver].[Role] = "Driver",
            [Job Type] = "Project"
        )
    ),
    TRUE,
    OR(
        [Reassign Driver] = USEREMAIL(),
        IN(USEREMAIL(), [Assigned Team])
    )
)

[VLMark37]

That worked for all jobs except for Job#1:

Job#1 - Reassign Driver= Scott ("manager"), Record Owner=Misty("manager"), who can currently see=Lee("Admin"), Scott("manager")

Misty the author of the record with "manager" role still can't see this entry.

[dbaum]

You need to determine the criteria that make a row available to a user, and then align your expression. Your criteria are unclear to me.

---

@dbaum wrote:

ANY(Current_User[Role]) = "Manager", OR( [Reassign Driver] = USEREMAIL(), [Reassign Driver].[Role] = "Project Manager", [Reassign Driver].[Role] = "Sales", AND( [Reassign Driver].[Branch] = ANY(Current_User[Branch]), [Reassign Driver].[Role] = "Driver" ) ),

---

If the preceding expression represent your criteria, then you should expect a row to be available to a user with the role "Manager" only if one of the following is true:

• [Reassign Driver] = USEREMAIL(),
• [Reassign Driver].[Role] = "Project Manager",
• [Reassign Driver].[Role] = "Sales",
• [Reassign Driver].[Branch] = ANY(Current_User[Branch]) AND [Reassign Driver].[Role] = "Driver"

If instead you want users with the role "Manager" to see all rows, then just use something like:

IFS(
IN(ANY(Current_User[Role]), LIST("Admin", "Manager")), 
true, 
...
)

[VLMark37]

IFS(
IN(ANY(Current_User[Role]), LIST("Admin", "Manager")), 
true,

This makes more sense; expect I would want the "manager" role only to see jobs assigned to their "branch", i.e. Any(Current_User[Branch]).

[dbaum · Accepted solution]

Then, it sounds like you want something along the lines of:

IFS(
    ANY(Current_User[Role]) = "Admin",
    TRUE,

    ANY(Current_User[Role]) = "Manager",
    [Reassign Driver].[Branch] = ANY(Current_User[Branch])
...

[VLMark37]

Thanks for the help!!