Experiencing bug with table CRUD restrictions

[anonymous]

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

[anonymous]

Steve:

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

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.

[anonymous]

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.

[anonymous]

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]

rommel:

Any suggestion on further debugging this?

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?

[anonymous]

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]

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

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?

[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()?

[Current_User (Slice) - How to conform your app around WHO is using the app](https://community.appsheet.com/t/current-user-slice-how-to-conform-your-app-around-who-is-using-the-app/35639/8) Tips & Tricks ?

It’s been my experience that ANY() can sometimes remove the “meta data” of a column; I’m talking about the deep layered data that AppSheet keeps internally that we can’t see. Stuff like: Referenced table Key column Referenced key column type But also stuff we can actually set: EnumList base type Enum base type ref table In the past when I would use ANY(), sometimes this information would be lost and I would loose the reference nature of the data or the base type of what we’re working with…

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

[Steve]

WillowMobileSystems:

Is that something that AppSheet did on their end?

Yep.