Expression to allow user role of admin or user who is assigned to edit

AppSheet AppSheet Q&A
, ,
1

Hey!
I am trying to put an expression on my table for updates that only allows those with the access level of Admin OR someone who is assigned to the project to edit the item. This is my formula:

IFS(
IN(USEREMAIL(), SELECT(People[Email],[Access Level]=“Admin”)), “ALL_CHANGES”,
IN(USEREMAIL(), Select(Tracker[Accountable TM (Primary)], [Accountable TM (Primary)]=USEREMAIL())), “ALL_CHANGES”,
IN(USEREMAIL(), SELECT(People[Email],[Access Level]=“User”)),“READ_ONLY”)

BUT the above allows me as a user to edit anything still. IF I change it to the following:

IFS(
IN(USEREMAIL(), SELECT(People[Email],[Access Level]=“Admin”)), “ALL_CHANGES”,
IN(USEREMAIL(), SELECT(People[Email],[Access Level]=“User”)),“READ_ONLY”,
IN(USEREMAIL(), Select(Tracker[Accountable TM (Primary)], [Accountable TM (Primary)]=USEREMAIL())), “ALL_CHANGES”)

The access role I have assigned of User overrides the fact that my email is equal to the “Accountable TM(Primary)”. Thoughts? Maybe a completely different formula…?

2

So lots to unpack here. With the latter IFS() works through the option pairs in order. As soon as it get a one to be TRUE, it then stops. So USER overrides Accountable TM (Primary) because it is second.

With the first you might find this is a better formula as the fall back option is always READ_ONLY and it merges the 2 lists of people with ALL_CHANGES access:

IFS(
   IN(
      USEREMAIL(),
      SELECT(People[Email],[Access Level]=“Admin”)
      +
      Select(Tracker[Accountable TM (Primary)],[Accountable TM (Primary)]=USEREMAIL())
   ),
   “ALL_CHANGES”,
   TRUE,
   “READ_ONLY”
)

Simon, 1minManager.com