Security Filter for User Role Table

[Alex_A]

Hello Appsheet community! I’ve been trying to add a Security Filter to the User Role table but can’t find an expression to get around the following error message “The filter expression cannot reference the table being filtered.”. I’d like to achieve that each user only sees data related to their company(aka “Client”) throughout the App. For now all users could potentially access all data in the Role table which I’d like to avoid. The difference with other use cases/solutions I came across in the Q&A forum is that each company/client can have multiple users and hence multiple related USEREMAIL(). Could you please help with a solution?

The table structure is as follows:

1. Client (Parent Table) - Key column is [Client ID]. Please note there are no user emails in this table. Users for each Client are shown in the Users Roles (Child Table) because in most cases there are multiple users for each client
2. Project (Child Table)- Key column [Project ID] and Ref Column [Client ID]
3. Roles (Child Table) - Key column [Role ID] , Ref Column [Client ID] , [Email Column], [Role Column] for Client/Admin. I’ve not included [Project ID] in this table

Perhaps I should also note there’s a Project Role table which has a ref to the Roles table and a ref to the Project table and which results in a ref View for each Project table showing a list of project team members. In the schema I see a loop Project>Client>Role>Project Role>Project.

Security Filters for Client and Project table which seem to work (no error message):

1. Client (Parent Table) :
   OR(
   IN([Client ID],SELECT(Roles[Client ID], ([Email] = USEREMAIL()))),
   IN(LOOKUP(USEREMAIL(), Roles, Email, Role), LIST(“Admin”))
   )
2. Project (Child Table):
   OR(
   IN([Client ID],SELECT(Roles[Client ID], ([Email] = USEREMAIL()))),
   IN(LOOKUP(USEREMAIL(), Roles, Email, Role), LIST(“Admin”))
   )

Security filters for Roles table which result in an error:

1. IN([Client ID],SELECT(Roles[Client ID],[Email]=USEREMAIL()))
   This expression resulted in an error “because can’t reference table being filtered”

2. IN([Client ID],Client[Client ID] where I tried to follow the logic from this advice SECURITY FILTERS: Filter grandchild records based on parent ( [THISROW-2] ) without virtual column

Any ideas how to get around this are greatly appreciated. Thanks!