LOOKUP(USEREMAIL() Table does not allow this operation

[Atlas]

Hi, I am struggling with privileges and security filters…
I want to give edit privileges to specific users and limit them to view related projects by using staff table,
but getting this error “Table does not allow this operation”.

STAFF TABLE

with the formulas

for privileges
LOOKUP(USEREMAIL(),"STAFF","EMAIL","PRIVILEGE")

for security filters
IN(LOOKUP(USEREMAIL(),"STAFF","EMAIL","PROJECTS"),[PROJECT])

I want
Morpheus to view all projects but READ_ONLY
Mr Anderson to view all projects and make ALL_CHANGES in all projects
TANK to view worlds and make UPDATES_ONLY
Trinity to view projects that she is related but READ_ONLY

Thank you all…

@LeventK @Steve

[Steve]

Please provide a screenshot of this:

Atlas:

but getting this error “Table does not allow this operation”

[Atlas]

Hi, Steve, thank you for your attention.

I removed security filters to view if there is a problem with this expression. Even so “Are updates allowed?” expression did’t work…

Error "Table does not allow this operation"

[Gowtham]

@Atlas From the screenshot I observed there is no return mode (updates_only, read_only,…)
In your Are updates allowed expression give the resultant on the right-hand side. I mean
SWITCH(LOOKUP(useremail(), “staff”, “email”,“privilege”) = which update mode you want ex: “read_only”., default mode)

[Atlas]

Hi, thank you for taking your time, ,
I have a STAFF table and I’ve assigned return modes in this table in privileges column. So, there are already return modes assigned to USEREMAIL…

[Gowtham]

I am not sure that the return mode will take from your STAFF table. Instead, give a try AppSheet return mode on the right-hand side.

[Steve]

I’m concerned your LOOKUP() expression might not be returning a value. To test, let’s try this instead:

ANY(
  SELECT(
    STAFF[PRIVILEGE],
    (USEREMAIL() = [EMAIL])
  )
  + {"READ_ONLY"}
)

[Atlas]

@Steve, I have tried this now but this also gives the same results. Expression reads the privileges correct so the user can who has privileges to make ALL_CHANGES can edit and adds. But when hit the sync the applicasion giving the same error “Table ‘DATA’ does not allow this operation”.

ANY(
  SELECT(
    STAFF[PRIVILEGE],
    (USEREMAIL() = [EMAIL])
  )
  + {"READ_ONLY"}
)

[Steve]

What happens if you set the entire expression to exactly this?

"ALL_CHANGES"

[Atlas]

Unfortunately the result is same

“Table ‘DATA’ does not allow this operation”

[Steve]

I would guess he permissions on your spreadsheet do not allow AppSheet sufficient access. You should probably engage support@appsheet.com for further help.

[Atlas]

@Steve it’s excel file in my drive. Sorry but, I did’t understand what you mean

[Steve]

You should probably engage support@appsheet.com for further help.

[Atlas · Accepted solution]

So, I have worked around and noticed that, formula is actually working perfect when user sign in from the phone.
But in the preview pane in appsheet editor confused me giving error, bizarre!

LOOKUP(USEREMAIL(),"STAFF","EMAIL","PRIVILEGE")