Table Security

AppSheet AppSheet Q&A
1

I have multiple roles with our company. I am trying to set up a security filter formula to address each role to view customers.

Here formula:

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

ANY(Current_User[Role])=“Manager”, OR([Driver ID]=USEREMAIL(), AND([Driver ID].[Branch]=ANY(Current_User[Branch]), [Driver ID].[Role]=“Driver”,[Driver ID].[Role]=”Project Manager”)),

ANY(Current_User[Role])=”Project Manager”, OR([Driver ID]=USEREMAIL(), AND([Driver ID].[Branch]=ANY(Current_User[Branch]), [Driver ID]=”Driver”,[Driver ID])),

TRUE,

[Driver ID]=USEREMAIL())

The formula works for Admin, Manager, and Driver roles; however, the project manager role can not see any customers. Not sure why?

Thanks

2

I suspect your issue lies with these 2 conditions:

Marc_Dillon_0-1670260893729.png
Marc_Dillon_0-1670260893729.png629×261 14.8 KB

1 Like
3

Yeah, I noticed that and removed the “.[Driver ID]”. Still same result

4

[Driver ID].[Role] = “Driver” ??

1 Like
5

Ugh!! I have been working on this for too long. Can’t believe I missed that. It did work; however, now when I sign as project manager, I just see project manager and no driver info.

6

What I am really after is a security set up that is specific to 1) the user role and 2) the job type.

The hierarchy is: “Admin” = see everything; “Manager” = see everything, add/delete customers, team, members, jobs, but can’t alter the app; “Project Manager”, can add/delete jobs, see all job type “projects” only, no need to see job type “routine”, and see all customers; “Driver” = see only jobs and customers assigned to driver either “project” or “routine”.

Job Types are “Routine”, “Project” and “Sales”

I would like to add a salesman role; however, trying to work in the salesman column, i.e. [Salesman].[Role]=USEREMAIL()…well let’s just say I wouldn’t know where to begin.

I tried using:

IFS([Job Type]=”Routine”, IFS(ANY(Current_User[Role])=“Admin”,TRUE,

ANY(Current_User[Role])=“Manager”, OR([Driver ID]=USEREMAIL(), AND([Driver ID].[Branch]=ANY(Current_User[Branch]), [Driver ID].[Role]=“Driver”,[Driver ID].[Role]=”Project Manager”)),

[Job Type]=”Project”,

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

ANY(Current_User[Role])=“Manager”, OR([Driver ID]=USEREMAIL(), AND([Driver ID].[Branch]=ANY(Current_User[Branch]), [Driver ID].[Role]=“Driver”,[Driver ID].[Role]=”Project Manager”)),

ANY(Current_User[Role])=”Project Manager”, OR([Driver ID]=USEREMAIL(), AND([Driver ID].[Branch]=ANY(Current_User[Branch]), [Driver ID].[Role]=”Driver”)),

TRUE,

[Driver ID]=USEREMAIL()))

No luck.

7

From this, I’d think your security filter should be as simple as this:

SWITCH(
  [Role],
  "Admin",
    TRUE,
  "Driver",
    AND(
      (USEREMAIL() = [Driver ID]),
      IN([Job Type], LIST("Project", "Routine"))
    ),
  "Manager",
    TRUE,
  "Project Manager",
    ("Project" = [Job Type]),
  FALSE
)

All that other logic you have in your expression doesn’t correspond to anything in your stated need.

8

It returned this error:

SWITCH function is used incorrectly: Cannot convert input 2 of type ‘Text’ to ‘List of Enum of Text’

Job Type is the only enum column in the formula.

9

Please post a screenshot that shows the complete expression and the error message.

10

I have worked up a formula that captures the hierarchy I am trying to achieve.

For my customer table it is:

IFS(ANY(Current_User[Role])=“Admin”,TRUE,
ANY(Current_User[Role])=“Manager”, OR([Driver ID]=USEREMAIL(), [Driver ID].[Role]=“Project Manager”, AND([Driver ID].[Branch]=ANY(Current_User[Branch]),[Driver ID].[Role]=“Driver”)),
ANY(Current_User[Role])=“Project Manager”, OR([Driver ID]=USEREMAIL(),AND([Driver ID].[Branch]=ANY(Current_User[Branch]),[Driver ID].[Role]=“Driver”)),
ANY(Current_User[Role])=“Sales”, OR([Driver ID]=USEREMAIL(),AND([Driver ID].[Branch]=ANY(Current_User[Branch]),[Salesman].[Role]=“Sales”)),
TRUE,
[Driver ID]=USEREMAIL())

and for my Job table it is:

IFS(
ANY(Current_User[Role])=“Admin”,TRUE,
ANY(Current_User[Role])=“Manager”,
OR([Reassign Driver]=USEREMAIL(),[Reassign Driver].[Role]=“Project Manager”,
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]),[Job Type]=“Project”,[Reassign Driver].[Role]=“Driver”)),
TRUE,
[Reassign Driver]=USEREMAIL()
)

The issue I am having now is at the “Driver” role level. I posted in another Q&A.

https://www.googlecloudcommunity.com/gc/AppSheet-Q-A/Related-Virtual-Column/td-p/497282/jump-to/first-unread-message

I am sure I need to address the “Assigned Team” in the formula, I am just not sure where to plug it in.

11

Here are screen shots of an example I am talking about:

Admin Level.jpg
Admin Level.jpg591×1461 64.5 KB

Driver Role.jpg
Driver Role.jpg646×1494 70.7 KB

12

Mr. Steve,

Here are the screen shots with the formula you prov

ided. The first as is; however, I changed to match it up with my column headers.

I would be interested if we can get this formula to work; however, I think I found a formula that works.

Job Security Screen Shot.jpg
Job Security Screen Shot.jpg1067×1016 72.3 KB

Job Security Screen Shot2.jpg
Job Security Screen Shot2.jpg1012×967 64.1 KB

I am still trying to solve this:

https://www.googlecloudcommunity.com/gc/AppSheet-Q-A/Related-Virtual-Column/td-p/497282/jump-to/firs…

After much trial and error, the problem is when in Driver role and “Assigned Projects” are not showing up is because the Driver is not “assigned” to the customer or “reassigned” to the job, just “Assigned Project” member and Driver.

Since “Assigned Project” is a separate Enumnlist I am having trouble developing the formula to capture it.

I have add a OR() formula which works so that the Driver sees Assigned Projects and His Assigned/Reassigned Jobs but everyone else’s jobs are still visible too.