Expression for Separating Data by Admin and Non-admin

[Jonathan_Lance]

I have four roles (SuperAdmin, Admin, Student and Coach) where the

1. SuperAdmin - sees all the data and views.
2. Admin - sees all except Student data
3. Student - sees only his/her data
4. Coach - sees his/her student data

Have tried slices and security filters but am missing a piece of the puzzle, Have used the IF(ANY( expression, but to no avail. Any help from the community would be appreciated. Thanks.

Regards,
Jonathan

[Marc_Dillon]

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…

help.appsheet.com

Limiting Users to Their Own Data

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

[Jonathan_Lance]

Hi Marc,

Thanks for the reply. Here is what I have done:

1. Created a slice called “Current User” and the expression USEREMAIL() = [Email]. [Email] is my first column the Users tab. I have the following roles [Role] - “SuperAdmin”, “CareManager” and “Patient”

2. Under my “Users” Table with [Email] as my primary key, I input the following formula that you recommended:

IN(INDEX(Current User[Role], 1), list(“SuperAdmin”, “CareManager”, “Patient”))

When I hit save I get the following message: Unable to fetch app definition. Error: Data table ‘Users’ is not accessible due to: Error: Data table ‘Users’ is not accessible due to:

When I remove the expression, the message disappears. I have checked the Google Sheet, regenerated the table.

What I am attempting to achieve is as follows: SuperAdmin sees everything, CareManagers see his/her patients and patients see only their data.

Would appreciate another reply. Thanks.

Regards,
Jonathan

[Marc_Dillon]

Jonathan_Lance:

1. Under my “Users” Table with [Email] as my primary key, I input the following formula that you recommended:

IN(INDEX(Current User[Role], 1), list(“SuperAdmin”, “CareManager”, “Patient”))

When I hit save I get the following message: Unable to fetch app definition. Error: Data table ‘Users’ is not accessible due to: Error: Data table ‘Users’ is not accessible due to:

When I remove the expression, the message disappears. I have checked the Google Sheet, regenerated the table.

You put that expression where exactly? (screenshots are usually the best)

[Jonathan_Lance]

You can see what error message I am getting.

[Marc_Dillon]

Jonathan_Lance:

You can see what error message I am getting.

Not all of it. I’d ask you to post the entire thing, but it is probably better to focus your attention away from that as I don’t think you’rr writing an appropriate expression for your needs to begin with.

Given that you want to specify which records to load under the 4 different role cases, I’d probably use IFS or SWITCH.

help.appsheet.com

IFS()

First-match conditional evaluation

help.appsheet.com

SWITCH()

Chose-one conditional evaluation

Keep in mind that the Security Filter expression is evaluated on every record of the Table. Your posted expression does not reference anything from the Users records, which means it is going to evaluate exactly the same across all records, which is probably not what you want.

Also, your 3 items in the LIST in your expression are different from the 4 roles you mentioned in your first post. That’s confusing.

[Jonathan_Lance]

Sorry about not being clear. So here is the whole story:

1. I have a Users table with columns: Email (key), Name, Phone No., Role (SuperAdmin, CareManager & Patient), Group, Photo
2. I have a Profile table which has each patient’s typical demographic data with Email (key)
3. I have a Vitals table which has the patient’s Email (key), key (unique ID each time the patient inputs blood pressure and heart rate), date/time, systolic level, diastolic level and heart rate.
   4, I will add other similar tables as Vitals tables with other medical data using Email as the key.

So far, the User table has a virtual column called Related Vitals with the experssion (REF_ROWS(“Vitals”, “Email”). The Email in the Profile and Vitals table is a Ref.

Yes, my first post was a generic role setup. Perhaps I am doing something wrong with my columns. Please take a look if you have time. I want to be respectful of your time and expertise.

[Marc_Dillon]

Oh I think I know what’s going on here. You’re trying to use a Slice in a Security Filter. However, they are based on the same Table, correct (Users)? You can’t do that. The Security Filter requires the Slice to be loaded before it can load any of the Table records, and the Slice needs the Table records to be loaded before it can create the Slice. To get around that you could make another Table just to hold the Roles associated with each USEREMAIL().

[Jonathan_Lance]

Hi Marc,

Thanks for being patient. So I started all over again.

1. Created the Users table with Email as the key (this has the roles in it: SuperAdmin, CareManager and Patient)
2. Created the Profile table with Email as a Ref
3. Created the Vitals table with Email as a Ref

So this part works.

This is what I understand from your previous reply on what I need to do next:

1. Create a slice called “Current_User” referring to the “Users” table with the expression USEREMAIL()=[Email] or are you suggesting that I create three separate slices with the expression, [Role}=“SuperAdmin”, [Role]=“CareManager” and finally [Role]=“Patient”
2. Now I should put in your expression of IFS against the “Current_User” into the Data section for Profile & Vitals or use IFS against the three slices.
3. Then I can use the IFS expression in the UX section for each view under ShowIf.

Thanks.

Regards,
Jonathan