Example of User Table with email and role to manage view access and editable_if for fields

AppSheet AppSheet Q&A
1

Hi All, I have seen different posts on managing field level security, such as using Editable If …IN(USEREMAIL(), LIST(“xyz@gmail.com”,“abc@gmail.com”))

This is great for an app with few users and limited field level security, but what about larger solutions?

I brought in ‘Users’ table with an ‘Email’ column and a ‘Role’ column.
Assuming the role was ‘editor’ for a particular user, how could I create an editable_if statement to only allow the user role ‘editor’ to make such edits to this particular field, how would I achieve this?

I was trhing * IN(LOOKUP(USEREMAIL(), Users…but wasn’t working

1 Like
2

Sam87:

I brought in ‘Users’ table with an ‘Email’ column and a ‘Role’ column.> Assuming the role was ‘editor’

There should be various different expression would work, but for example,

any(select(User[Role],useremail()=[Email]))=“editor”

1 Like
3

Thanks appreciate it

4

tsuji_koichi:

any(select(User[Role],useremail()=[Email]))=“editor”

Slight syntax fix in case anyone else references this:

(any(select(User[Role],useremail()=[Email])))=“editor”

1 Like
5

Hi @tsuji_koichi,

If I want to include an AND condition from another table, finding this syntax fails. Any feedback on why the below does not work? Perhaps not able to pull status field is in the Request table due to incorrect query.

(any(select(User[Role],useremail()=[Email])))=“Editor”, Requests[status] <> “Closed”

6

Sam87:

(any(select(User[Role],useremail()=[Email])))=“Editor”, Requests[status] <> “Closed”

This expression is invalid.

7

Hi Steve, I am aware this doesn’t work. Getting a bit frustrated with the expression syntax. Much prefer SQL. Do you have any feedback for how i might fix it?

8

Hi @Sam87
If you search the documentation for Expressions Including SELECT() and AND() you should find some pointers.

1 Like
9

Sam87:

(any(select(User[Role],useremail()=[Email])))=“Editor”, Requests[status] <> “Closed”

As pointed out by other member, above syntax is not gonna work.

In case you are trying to add filter with the expression to the table of “Request” table, then the working expression would be

Sam87:

and(any(select(User[Role],useremail()=[Email]))=“Editor”, [status] <> “Closed”)

3 Likes