Are updates allowed? SWITCH expression stopped working 4Sep2020

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?

0 4 493
4 REPLIES 4

GreenFlux
Participant V

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"
)
  1. 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.

  2. SPLIT(..., " , ") splits the Text value produced by LOOKUP() in (1) back into a list.

  3. LIST("IT Admin", "MD") defines the list of roles we care about here, from most important to least.

  4. 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().

  5. 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.

  6. 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!

Top Labels in this Space