This expression was implemented on 26Jun2020 and was working until a user got an error message on 4Sep and the expression was deleted.
SWITCH(LOOKUP(useremail(),Employees, โEmployeeIDโ,โroleโ)
โIT Adminโ,โALL_CHANGESโ,
โMDโ,โADDS_AND_UPDATESโ,
โREAD_ONLYโ
)
How can I revise the expression to control update permissions based on the userโs โroleโ in the Employees table?
Possibly related to recent changes in the SWITCH expression:
Although, from the error message, it sounds like another cause could be that Employees[role]
used to be a Text or Enum, and was changed to an EnumList.
The SWITCH expression looks at the column type of the first input, which is the [role]
in this case-- the return value of LOOKUP().
Then, SWITCH expects the 2nd, 4th, 6thโฆ parameters to be of the same type.
This error is saying that [role]
is a list, and โIT Adminโ is text, so it can not compare them. Instead, try using IN() to see if โIT Adminโ is IN the list of [role].
Thanks, you were right about the EnumList.
I am able to use CONTAINS() to see if โIT Adminโ is in the list of [role], but AppSheet does not like that if I use with SWITCH.
Your use of SWITCH() doesnโt make any sense. SWITCH() asks whether its first argumentโa singular valueโmatches any of the several following values, which is not at all what you claim youโre trying to do: asking whether one of the several values occur in a given list.
Hereโs my suggestion:
SWITCH(
ANY(
INTERSECT(
LIST(
"IT Admin",
"MD"
),
SPLIT(
LOOKUP(
USEREMAIL(),
"Employees",
โEmployeeIDโ,
โRoleโ
),
" , "
)
)
),
"IT Admin",
"ALL_CHANGES",
"MD",
"ADDS_AND_UPDATES",
"READ_ONLY"
)
LOOKUP(...)
is your LOOKUP() expression. You noted the Role column is of type EnumList. The LOOKUP() function always produces a singular value; it cannot produce a list. If the column LOOKUP() is asked to provide is a list, LOOKUP() will combine the individual items of the list into a single Text value, which cannot be used as a list directly.
SPLIT(..., " , ")
splits the Text value produced by LOOKUP() in (1) back into a list.
LIST("IT Admin", "MD")
defines the list of roles we care about here, from most important to least.
INTERSECT(..., ...)
finds the items the lists produced by (2) and (3) have in common. The resulting list of common items will be in the order they occur in the first argument to INTERSECT().
ANY(...)
gets the first item of the list produced by (4). Since the first argument to INTERSECT() was a list of relevant roles in importance order (see (3)), the most important matching role will be the first in the list.
SWITCH(...)
is the remainder of your original SWITCH() expression.
See also:
Steve,
This is really impressive. Thanks.
It took a while to test and tweak, but I now have it working.
One change I had to make to get employees who had multiple โrolesโ to work was to eliminate the spaces in
SPLIT(โฆ, " , ") splits the Text value produced by LOOKUP() in (1) back into a list.
I am still working with this in my test version and am not sure what this will do to sync time. However, I am getting the desired function.
Thanks again!
User | Count |
---|---|
35 | |
35 | |
27 | |
23 | |
18 |