Can I limit what data shows per the user email - linked to a customer table?

[Tiger1]

I want my app to open the same for everyone. I want each customer/client to see the exact same layout and views. However, I want the views, layouts etc… to ONLY show the data pertaining to that customer. How do I do this on a global app setting? I do not want to have to build multiple apps PER customer - I want the same app - but when customers login using their email address - they will ONLY see their data.

Thanks

[WillowMobileSys]

The concept you are diving into in known as Scalability. There are a couple of approaches in AppSheet to scalability - Security Filters and Data Partitioning. You can combine the two if needed.

Below are some articles to help you identify which Scalability feature best works for you and the basic ideas of how to implement them.

help.appsheet.com

Approaches to Data Scalability

How to scale apps to go past spreadsheet row limits.

---

Security Filters

help.appsheet.com

Security Filters: The Essentials

You can use Security Filters to control which records a user can see and update.

help.appsheet.com

Scaling Using Security Filters

Security filters can be used to limit the data fetched by AppSheet on a per-user basis.

---

Data Partitions

help.appsheet.com

Scaling Using Data Partitions

How to use data partitions to improve performance at scale.

---

[Tiger1]

Thank you very much!

[Tiger1]

Going on what you had suggested - I am going to try and use the security filters for my filtering. I am stuck on how to write the expression. I will need to write one per customer PER table. That’s ok. I want to write it so that each table will check the useremail and the customer account that useremail belongs to (In my new customer user list table - i built). If the useremail matches the customer number - then ONLY show that data containing that customer number. Here is my table and my current expression. My expression is not complete. I need to add to it. I am confused on how to force certain data only - meaning the data that ONLY has the customer number attached to it:

Expression:
IFS
(
AND(
USEREMAIL()= johndoe12345.com,
CONTAINS([Customer Number], “89171”)
), true
)

Table:

[SkrOYC]

I think there is a lack of details but IN() should do the trick instead of CONTAINS()

help.appsheet.com

IN()

Is item in list?

[Tiger1]

So How do I write it to check “IN” the table - the useremail and the corresponding customer number - then ONLY show the data that has that customer number attached to it - in my other tables?

[Tiger1]

This?

IN([Column], SELECT(AnotherTable[SomeColumn], [EmailColumn] = USEREMAIL()))

[WillowMobileSys]

Tiger:

then ONLY show the data that has that customer number attached to it - in my other tables?

You will need to reference the Users table to extract the Customer Number of the logged in user in many places. I recommend that you first implement a Current User slice as described below. It’s easy to do, will shorten many of your expressions and I believe is more efficient than using LOOKUP(), SELECT() or FILTER() functions in all the places.

Current_User (Slice) - How to conform your app around WHO is using the app Tips & Tricks 💡

One of the core basic functions, included in just about EVERY app I make, is the ability for the app to know WHO is using the app. If the app knows who’s using it, then I can easily control many different aspects of the app: Add/edit/delete permissions How data should be filtered What views are shown What buttons, or Actions, are visible Which workflows should fire off Which columns should be shown or editable etc. Requirements To accomplish this functionality in your app, you need the follo…

Once you have the above slice created you can use it like in the expression below to filter rows by company. An example Security Filter expression might be

ANY(CurrentUser[Customer Number) = [_THISROW].[Customer Number]

NOTE: You do not need to use IF or IFS.

The CurrentUser slice already does the “finding” of the logged in user row.

[_THISROW] refers to the row in the table you are attempting to filter. If you have a [Customer Number] column in all of your tables that need to be filtered, then you are likely all set.

However, there are some cases where you may not. For example, maybe you have an Orders and Order Details table. Maybe you store [Customer Number] in the Orders table but need to filter the Order Details table as well. ASsuming the Order Details rows references its parent Order row, then your expression might be:

ANY(CurrentUser[Customer Number) = [_THISROW].[Order ID].[Customer Number]

I hope this helps!

[Tiger1]

Thank you again. One more question - Can I make a master/admin user? Meaning someone that can see ALL data? How?

[WillowMobileSys]

Yes, you have two ways.

1. Use the AppSheet Users feature. You can assign users as Admin or as User. Then in the app you would the USERROLE() function determine which type of user the logged in user is.

2. Add a Role column to your Users table. Then you can use the ANY(CurrentUser[Role]) technique described above to get the user’s role from your home built Users table. This has an added benefit in that you can assign within each company a designated “Admin” - someone who can see ALL of the company data but only for THAT company. For example, if you are tracking timesheets. Normal users should only see their timesheets and no one else’s. The company Admin could be implemented to see all employee Timesheets for that company only. AND you could still use the AppSheet USERROLE() function for a system admin that can see all timesheets for all companies. This can also be extended to provide ANY number of user roles in the company to control what views each employee might see in the app.

[Tiger1]

Ok. I added User_role in my master user table. I am having trouble making each table ONLY show data per condition:

If User_role= “Admin” - show everything.
If user_role = “…” - only show data containing the [customer number].

This is what I have so far:
OR(
INDEX(Current_User[User_Role], 1) = “Admin”,
IN([Customer Number], SPLIT(Current_User[User_Accounts], " , ")))

Here is my master user table:

[SkrOYC]

What’s the column type of [Customer Number]?

[Tiger1]

It is a refence to another table …

[WillowMobileSys]

Test each expression in the OR separately to figure out which one (or if both) is causing trouble.

FYI, I do notice that you “ADMIN” in the column but “Admin” in the test. I don’t think it’s case-sensative but I can never remember when it is and isn’t.

[Tiger1]

Ok. So after testing them separately I have found that they work correctly when the “OR” is not there. I logged in as both users and the data shown was correct - when the “OR” is not in my expression.

When I add the “OR” and combine the two - it shows everything/all data. So the second expression - ONLY showing data per customer number - is not working.

FYI - i changed “Admin” - to “ADMIN” - didn’t seem to help anything.

[WillowMobileSys]

Tiger:

So the second expression - ONLY showing data per customer number - is not working.

Trying reversing the order of the items within the OR. Sounds silly I know but I have experienced exactly that issue in other expressions and by simply moving the last item to be first corrected it.

[Tiger1]

Ok. Now it ONLY uses the first expression before the “OR”.

It seems as though the “OR” is not working at all?

[SkrOYC]

Ref type so?

[WillowMobileSys]

Tiger:

It seems as though the “OR” is not working at all?

Hmm, can you show an image of the complete expression you have from the Expression Assistant?

I am not sure of the impact SPLIT() might be causing here. I would suggest to try using INDEX() or ANY().

SkrOYC:

Ref type so?

REF type should not matter as long as the values are actual row keys in both sides of the IN() function.

[Tiger1]

I want to add this…

I am putting this inside EACH table - correct? In the security section?

Also - Am I suppose to label the roles?

Meaning:
Admin = 1
USer = 2
etc…

[WillowMobileSys]

I think the SPLIT() function is creating issues. Try using INDEX() as you did for User_Role.

[Tiger1]

Using INDEX() does not help… Same issues

[Tiger1]

I thought I had it - I still do not…

[SkrOYC]

WillowMobileSystems:

REF type should not matter as long as the values are actual row keys in both sides of the IN() function

If it’s Ref he doesn’t need to use IN() at all because he would be comparing to Values

[Tiger1]

It is a reference to “Customer Table”. So I need to delete “IN” in my expression?

[Tiger1]

It appears that taking that ref off - did it.

Let me test it further…

[WillowMobileSys]

SkrOYC:

If it’s Ref he doesn’t need to use IN() at all because he would be comparing to Values

The way I am reading it, a User can be assigned a LIST of Customer accounts. We are filtering the Customer data rows based on the [Customer Number] belonging to the accounts the logged in user manages. So I believe a Value is being compared to a List

[Tiger1]

That is correct…

[WillowMobileSys]

On the same track as @SkrOYC, how is the User_Accounts column defined? It should be EnumList with base type of Ref to your Customers table??

[Tiger1]

I have it as Enumlist - but not a reference? Should I add that?

[WillowMobileSys]

If [Customer Number] is a REF then yes. Otherwise, if [Customer Number] is just the number then simply make sure your User_Accounts columns is pulling in the same list of numbers.

[WillowMobileSys · Accepted solution]

SkrOYC:

Usually the Users table is the one with the EnumList but I saw just one value inside of it.
That’s why I asked if it’s Ref type.

You are correct. User_Accounts is meant to be EnumList and he did have an example with only a single value.

You did confirm that [Customer Number] is a REF with this question:

SkrOYC:

What’s the column type of [Customer Number] ?

and his response…

Tiger:

It is a reference to another table …

I assume “another table” is the Customers table.

---

While I assumed User_Accounts was an EnumList based on the the name AND the SPLIT() function used (it would have erred out)…we never really confirmed the base type of the User_Accounts column. It should be a REF type also to Customers table or whatever table [Customer Number] column is a REF to. This has been the issue all along.

[Tiger1]

Ok. Now after making these changes:

[User_Accounts]-
EnumList - with ref to “Customer Table”

And
Leaving Customer as a reference to the same referenced table above - “Customer Table”

It ALL seems to work fine now.

Thanks

[WillowMobileSys]

Yeah!!

[SkrOYC]

I’m fealing that this relationship is the other way around.
I’ll check this tomorrow or later with a fresh mind to help you solve the puzzle

[WillowMobileSys]

SkrOYC:

I’m fealing that this relationship is the other way around.

Which relationship are you referring to?

[SkrOYC]

Usually the Users table is the one with the EnumList but I saw just one value inside of it.
That’s why I asked if it’s Ref type.
The CurrentUser slice/scenario is very straightfoward. It seems that there is a problem in the database design but I know we can fix it.
I will try to read everything again later and try to come up with ideas