Restrict Views for Clients with Full View for Dev/Admin

I was going to ask how to do this, but I got it acting the way I want, so I thought I would share.

BLUF:
I am creating multiple apps for us to use as a company while our clients will need access to add, update, and delete, but only their own data. I want to ensure clients from different companies cannot see each other’s data. There are only two people who should be able to view everything. These two admins should not be able to alter any data. They are read only.

How?
I have a table where the clients build work orders. There is also a users table, including the name of the company they work for. To ensure I do not have to add email addresses to expressions every time someone adds or deletes a user from their company access, I added the following expression to a slice of the work orders table for row filter condition:

USEREMAIL() = LOOKUP([_THISROW].[User Company],Users,Company,Email)

This means they can theoretically add unlimited users and only their company rows will be accessible.

The view for this slice includes this expression in the show_if under display:

“User” = LOOKUP(USEREMAIL(),Users,Positions,Email)

This ensures all USERS will see their rows in this slice, allowing me to add a different view for Admins.

For the admins…

I added another slice of the work order table, making it read only, and put the following expression in the row filter condition:

OR(USEREMAIL() = “redacted@email.com”, USEREMAIL() = “alsoredacted@email.com”)

This allows only two people to see all the data: The admins.

I added a view for this slice and included the same expression in the display show_if section.

After testing this, it works well for what I need, though I am open to better options.

This set up means my clients are the only ones who can add, update, and delete their own rows and we have view access so we can see what they’re looking at, but if any mistakes are made, it would have to be someone in their company.

3 Likes

Nice one!

The best thing I love app the AppSheet platform is that there’s…
Always another way (matrix) - reduced


You might check out this post:

  • Implementing a Current_User (slice) would reduce the database calls from all the LOOKUP()s

  • You can also easily conform things around [User_Roles] pretty simply with this method as well - in a universal way, where you’re not “hard-coding” the emails into slices.

3 Likes

I never want to keep showing up and asking for help without helping, so I wanted to offer my method in case it helps a newbie, but this is phenomenal. I did play with that option as I’ve read that article. The issue I have is I need to compare my user table to the current logged in user and their registered company. Maybe if I keep trying I can figure it out. Thanks for the help!

3 Likes

Welcome friend! partyparrot (Appsheet)

If you implement a Current_User (slice), that information is readily available - just one INDEX() formula away!

INDEX(Current_User[User_Company], 1)
INDEX(Current_User[User_Role], 1)

Let’s say you had an enumlist inside the user table, with a list of all the “Assigned Clients”

SPLIT(CONCATENATE(Current_User[User_Assigned_Clients]), " , ")

  • This gives me a functional list of the user’s assigned clients
2 Likes

Instead of Slices you should use Security Filters.

3 Likes