Security Filter Client and User Table

AppSheet AppSheet Q&A
, , ,
1

Hi, I’m trying to use security filters to use one app for many clients and users.

I have a table Client and a table User.

In the table Client I’m using a bot to automatically update a list of all the users related to this client (Client_Master_User_List)

The Security Filter for the table Client is :

IN(LOOKUP(USEREMAIL(); User; Email; ID); [Client_Master_User_List])

The Security filter for the table User is :

[ID Client]=ANY(Client[ID])

But when a I add this one I get an error message :

VictorB_0-1668261762462.png
VictorB_0-1668261762462.png1912×321 478 KB

Any suggestions?

2

The problem is the circular reference. Each table’s security filter references the other table. That won’t work because one table’s rows must be fetched first in order to be referenced in another table’s security filter. For multi-tenant user management, here’s a post that references a pretty elegant technique outlined by @Jonathon as well as a more novice approach that I had separately described.

3 Likes
3

The Security Filters between the two tables are circular - The Client table Security Filter relies on the User table to lookup the user and the User table relies on the Client table. Security Filters cannot have this potential back and forth relationship.

You should record the Client (or Clients if there can be more than one) on the User record when the user is added. Then you can adjust the Security Filters like this

User Table:

[Email] = USEREMAIL()   -- assumes you have only a single row for each USEREMAIL()

Client Table:

IN([Client ID], User[Clients])   -- if you allow user to belong to more than one Client
OR
[Client ID] = ANY(User[Client])   -- if only a single Client, because there is expected to be only a single User row for USEREMAIL() this works fine

I hope this helps!

3 Likes
4

Hi, thanks for helping ! I understand the error.

In my case I have many users by client, is it a way to filter the users by client ?

I would like to be able to see all the users of the same client.

5

I see. In that case, I think you were on the right track initially.

Instead of saving the list of Users in a column in the Client table, save them to another table … maybe ClientUsers. Then you can:

  • LOOKUP the logged in user info to Filter down to the single Client row.
  • use the Client table, should be only a single row, to LOOKUP all the users associated with the Client for filtering the Users table.

I hope that helps…better!

2 Likes