Security Filter for User Role Table

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!

Ref loops like this are not allowed.

Roles:

(USEREMAIL() = [Email])

Clients:

IN([Client ID], Roles[Client ID])

Projects:

IN([Client ID], Clients[Client ID])
2 Likes

Hi Steve, many thanks for your swift reply! Much appreciated. I’ve two follow-up questions:

  1. Are the security filters you suggested for Clients and Projects tables somehow also filtering the Client IDs for each specific Email from the Roles table so that for instance Client A and Client B don’t get each others data on their device from the Client and Project table? In the Roles table I had added a [Client ID] column to link each user-email to a Client ID. I’m not sure if/how your expressions would read which user is linked to which Client ID and filter accordingly.

  2. What would be the correct additional security filter to allow user Role “Admin” to see all data in the Roles table? I’ve tried an expression for Project table (see below) which seems to work but I stumble with the Roles table. With your suggested security filter - (USEREMAIL() = [Email]) - Admin only sees own data in Roles table but no other user data.

Security filter in project table:
OR(
IN([Client ID], Roles[Client ID]),
IN(LOOKUP(USEREMAIL(), Roles, Email, Role), LIST(“Admin”))
)

Thanks again!

My suggestions do this:

  1. Load all of the roles associated only with the current user, without regard to client or project.

  2. Load all clients associated only with the loaded roles: i.e., only the clients associated with the current user.

  3. Load all projects associated only with the loaded clients: i.e., only the projects associated with the clients associated with the current user.

If the current user is associated with multiple clients, the user will be able to see the projects for each of those clients.

The problem here is that data in one row of the Roles table is not available when apply a security filter to another row of that same table. If you want an admin for roles, you’ll need to achieve it some other way. Would this admin have access to all roles, or just roles with the same client?