access control by user

AppSheet AppSheet Q&A
1

I would like to control the record based by user mail.

The app shared by 4 person.
manager @ gmail.com
Anne @ gmail
John @ gmail
Tom @ gmail

There are 3 tables.

Item table (This table is master table)

A | B | C | D | E | F
ItemID | ItemName | Anne | John | Tom | Who
12345 | XXXXX | Yes | No | Yes | AnneTom
(Who column is combine the name of YES)
In F, there will be AnneJohnTom, AnneJohn, JohnTom.

C,D and E, here is the name of staff, if Yes, the person can be order,
if no, the person is not assigned to the item.

This table does not have useremail.

Order table
OrderID | ItemID | qty | UserEmail
00000 | 12345 | 6 | Anne
11111 | 12345 | 0 | John
22222 | 12345 | 2 | Tom

Staff table
this table now related with only order table.

In the order table, another system already generate the record for John.
(CSV data is imported to Appsheet.)
But John is not assign to ItemID 12345.
When John login to app, orderID 11111 will be hide automatically.

The system generate aprox 1000 record at one time,
and there are also the records not assigned to Anne and Tom too.

What is the best way to do this?
Security filter or Slices?
What kind of formula is needed and in which column?

In security filter for order table, Useremail is already set both of the manager and login user.

2

Assuming ‘not assign’ is indicated by a ‘0’ in the qty column, it seems like you could use a security filter on the Order Table to only show OrderID rows that:

AND([UserEmail]=USEREMAIL(), [qty]>0)

If the 0 in the [qty] is not an indicator the set up of your Items table makes things a little more difficult since you are using columns for each person than than a separate row.

1 Like
3

One way to do this..

First filter the “Item” table with CONTAINS(Useremail(),[Who]) in the secure filter so you have rows that belong to the user.

Then with the “Order” table you can use a security filter as.. IN([ItemID],Item[ItemID]) and it will show rows where the Item belongs to app user.

If you need access for the manager in the “Order” table, then use..

OR(EMAIL()=“name@domain.com”,IN(..))

1 Like
4

I am sorry there is some missing information on my first post.
I had modified my post to clarify.

The KEY of identification will be C, D, E of Item table.

@lynchk21 @AleksiAlkio
Thank yo ver much for your proposal!