Expression for Separating Data by Admin and Non-admin

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

1 Like
3 Likes

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

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

1 Like

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.

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.

1 Like

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.

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().

1 Like

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

1 Like