Experiencing bug with table CRUD restrictions

rommel · 12-13-2021 11:05 AM

Hello - I’ve had some CRUD restrictions on a table that have been working for the past 4 months, and all of a sudden my users are unable to Add new records because the expression is yielding the incorrect value.

I have a table called Cases where Medical Directors have the ability to ADDS_ONLY records, and it’s using a Switch Lookup to the Users table to determine the role of the user that’s logged in. Here’s the expression that I’m using:

switch(
lookup(USEREMAIL(), Users, Email, Role),
Admin, "ALL_CHANGES",
Medical Director, "ADDS_ONLY",
"READ_ONLY"
)

Here’s a screenshot of the Users table indicating the user Role:

and finally, here is a screenshot of the error that I’m getting when adding a new record to the Cases table with a user that has the correct Role:

Any ideas on why this would stop working?

Thanks in advance!

Steve

I would guess you have more than one row in the Users table that has an Email column value matching the affected user’s email address.

I would also recommend quoting all textual values:

SWITCH(
  LOOKUP(USEREMAIL(), "Users", "Email", "Role"),
  "Admin", "ALL_CHANGES",
  "Medical Director", "ADDS_ONLY",
  "READ_ONLY"
)

rommel

Hey Steve, thanks for the prompt reply.

I double checked the Users table and there is only one record with that email address.

Secondly, I tried your recommendation of quoting all textual values and no success there, unfortunately. The expression has been working without any issues until today. I’m guessing this is a bug with the way that AppSheet is interpreting the results of the expression

Steve

Please post a screenshot of the Are updates allowed? expression for the Cases table, including the entire Expression Assistant window.

rommel

Yes, absolutely.

Here is the Are Updates Allowed? expression window with both how it was previously setup, and after your suggestion to quote all textual values.

Steve

I see no problem with with expression. The only possibility I can think of is an email mismatch or duplicate.

rommel

The issue doesn’t appear to be due to a mismatch or duplicate record in the Users table as I’ve verified that there is no discrepancies in the Users table, and I’ve tried with multiple users with the Medical Director role and the issue seems to persist across the board.

Any suggestion on further debugging this?

I really appreciate all of your help on this, Steve!

WillowMobileSys

Just to confirm, it is only those with the “Medical Director” role that are being affected?

What is the data type of the Role column?

rommel

Yes, it’s only the Medical Director role that’s being affected. The other role, Admin, behaves as expected. The data type of the Role column is Enum with base type of Text

WillowMobileSys

In the Error message you showed above, it reflects an “ANY(SELECT())” instead of LOOKUP(). Is that something that AppSheet did on their end? Or did you have an ANY(SELECT()) originally?

Steve

Yep.

SkrOYC

This may sound weird but, I have read comments about ANY() (and LOOKUP() seems to be a macro using ANY()) that it changes the type of the value.
Have you tried with INDEX() and a Current User slice instead of the whole dataset and LOOKUP()?

Since you don’t want to change everything (although you did the hardest part already) you could try this:

SWITCH(
  INDEX(
    SELECT(
      Users[Role],
      [Email]=USEREMAIL()
    ), 1
  ),
  "Admin", "ALL_CHANGES",
  "Medical Director", "ADDS_ONLY",
  "READ_ONLY"
)