Solved: Re: Show or hide menu icons based on User role

eldderri

Hi everyone here,

In my app ,I have three icons in one menu ,These icons are related to three dashboard views.I want to show or hide each icon based on the user role in my User Manager table which is structured like that:

UID	Team	Email	Role
1	A	aaa@gmail.com	Admin
2	B	bbb@gmail.com	Dispatch
3	C	ccc@gmail.com	Logistics
4	D	ddd@gmail.com	Acting As D
5	E	eee@gmail.com	Viewer

The three icons are related to three views in my menu ,which is structured like that:

ID	Name	Image
1	New Action
2	Supervisor Dashboard
3	Report with Filter

I want to enable Logistics" ,"Dispatch" and "Acting As D" to see New Action and Supervisor Dashboard icons ,while Viewer can only see Supervisor Dashboard view icon .Admin can see them all.

Thank you in advance.

Rafael_ANEIC-PY

Hello there,

Here's how you can solve your issue:

But before you start reading, you should look into implementing a current user system if you haven't already:

https://www.googlecloudcommunity.com/gc/Tips-Tricks/Current-User-Slice-How-to-conform-your-app-aroun...

Now the solution:

1- Create a new table only for the "User Roles", and in it add a column called "Allowed Views", make it an EnumList base type text, and populate it with the name of your options.

2- For every role, use the Allowed Views column to choose which views are allowed for their role.

3- For this to work the "Role" column in your users table should be of type REF to your newly created "User Roles" table

4- Add a virtual column called "_allowedViews" with an expression of [Role].[Allowed Views] to make the upcoming expression easier to make.

5- Create a slice of your menu table, you'll use this slice as the table for the menu view

6- Once you have all that, you can use an expression like this on the filter condition of the slice you just created, the one on which your menu will be built on:

IN(
 [name],
 INDEX(currentUser[_allowedViews],1)
)

This lets you dynamically change just from within the app which roles have access to which views, and it is easily scalable to multiple roles and views.

OR

Just add an EnumList column to your menu table, the options for this column should be all your roles, then you can populate each row in your menu with the roles that would be allowed to see each view, and then you'll use instead this expression in the menu slice:

IN(
 INDEX(currentUser[Role],1),
 [allowedRoles]
)

Personally I like the other option more, even if it is more work.

View solution in original post

Rafael_ANEIC-PY

Hello there,

Here's how you can solve your issue:

But before you start reading, you should look into implementing a current user system if you haven't already:

https://www.googlecloudcommunity.com/gc/Tips-Tricks/Current-User-Slice-How-to-conform-your-app-aroun...

Now the solution:

1- Create a new table only for the "User Roles", and in it add a column called "Allowed Views", make it an EnumList base type text, and populate it with the name of your options.

2- For every role, use the Allowed Views column to choose which views are allowed for their role.

3- For this to work the "Role" column in your users table should be of type REF to your newly created "User Roles" table

4- Add a virtual column called "_allowedViews" with an expression of [Role].[Allowed Views] to make the upcoming expression easier to make.

5- Create a slice of your menu table, you'll use this slice as the table for the menu view

6- Once you have all that, you can use an expression like this on the filter condition of the slice you just created, the one on which your menu will be built on:

IN(
 [name],
 INDEX(currentUser[_allowedViews],1)
)

This lets you dynamically change just from within the app which roles have access to which views, and it is easily scalable to multiple roles and views.

OR

Just add an EnumList column to your menu table, the options for this column should be all your roles, then you can populate each row in your menu with the roles that would be allowed to see each view, and then you'll use instead this expression in the menu slice:

IN(
 INDEX(currentUser[Role],1),
 [allowedRoles]
)

Personally I like the other option more, even if it is more work.

eldderri

Hi,

I have already a User Manager table with this structure:

UID	Team	Email	Role
1	A	aaa@gmail.com	Admin
2	B	bbb@gmail.com	Dispatch
3	C	ccc@gmail.com	Logistics
4	D	ddd@gmail.com	Acting As D
5	E	eee@gmail.com	Viewer

Shall I create another one,or just add a column?

Rafael_ANEIC-PY

Yeah, the new "User Roles" I was talking about looks more like this tho:

UID	Role Name	Allowed Views
LAKSDJ123	Admin	Supervisor Dashboard, Report with Filter, New Action
OKJLJIO23	Dispatch	Report with Filter, New Action
FGSH9ISG0	Logistics	New Action
ZXCZCX221	Acting As D	Report with Filter
BVNMVB45	Viewer	Report with Filter

eldderri

Ok.I have now my User Roles table like that:

UID	Role Name	Allowed Views
LAKSDJ123	Admin	New Action , Supervisor Dashboard , Report with Filter
OKJLJIO23	Dispatch	New Action , Supervisor Dashboard
FGSH9ISG0	Logistics	New Action , Supervisor Dashboard
ZXCZCX221	Acting As D	New Action , Supervisor Dashboard
BVNMVB45	Viewer	Supervisor Dashboard

To which table I sould add the virtual column:"_allowedViews"?

Rafael_ANEIC-PY

@eldderri wrote:

To which table I sould add the virtual column:"_allowedViews"?

To your "User Manager" table

eldderri

I followed all the steps above ,but I get empty app for all users even Admin

Would you please explain this:

IN(
[Name],
INDEX(Current_User[_allowedViews],1)
)

Rafael_ANEIC-PY

@eldderri wrote:

I followed all the steps above ,but I get empty app for all users even Admin

Did you double check you did everything? if you could share some screenshots I could look and see if it's all ok

@eldderri wrote:

Would you please explain this:

IN(
[Name],
INDEX(Current_User[_allowedViews],1)
)

Yes, IN() will search for the Name of the menu item in any given row among the _allowedViews of the person that is using the app, the result should be TRUE if it is among the allowed views or FALSE if it isn't, which makes it visible or not, since the view is running on a slice with this condition.

eldderri

Yes of course,Herewith some screenshots illustrating the process:

User Roles table:

2. users table should be of type REF to your newly created "User Roles" table:

3.Virtual column in User Manager table:

4.Slice for menu table:

These are all the steps I followed , but I think the problem is in the expression as Current_User is not clear to which table you refer.

Note also that I have already created a slice from User Manager table named Current_User. with :

I even tested the 2nd option but does not work

Best regards

Rafael_ANEIC-PY

Hey, my bad, I forgot to mention that since you now have a table for your User Roles with its own ID column, that means the Role column in your users table should be a reference (that looks like the UNIQUEID() appsheet often uses) and not just the name of the role, that broken reference might be the reason why it's not working, could you please check that?

You can spot a broken reference if you see a yellow triangle next to a column in the app, you can fix it by selecting the roles for each user in-app

eldderri

I have the User Roles table like that:

in googlesheet:

It displays nothing even for me when using slice filter expression:

Rafael_ANEIC-PY

Can you send a picture of your User Manager table as well please?

eldderri

Of course:

As data is sensitive and related to my company and I do not have the right to share it ,I replaced real names:

See you tomorrow as it is nearing midnight

Rafael_ANEIC-PY

Aha ! it is just as I suspected.

@eldderri wrote:

Your ROLE column in this table has just the name of the role, this is not a valid reference to your user roles table

Replace the Role in your user manager table with the UID instead for each role and it SHOULD work

eldderri

My tables are like that now:

User Role table:

User Manager:

but still not working

Rafael_ANEIC-PY

I see, it should be working but I can't tell what's the issue from here.

If you want, you could create a copy of your app without any data except dummy user data and share it as an editor to my email (PII Removed by Staff) and I can take a look.

eldderri

Ok ,I will do it later on as I am very busy in develloping my other apps.Thank you so much for your interresting guidance.I will be back soon to give you more details about my app.

Best regards,

eldderri

Hi ,

I have created a dummy Data app and I want to share it with you so that You can apply your steps and give me feedback .send me your e-mail on :<PII removed by staff>

eldderri

Is there any other option to share my app with you?

Rafael_ANEIC-PY

Hello there, I can't find the option that would let me send you a direct message with my email so that you can share the copy of the app with me, so I typed it into a pastebin that will delete itself in a day.

https://pastebin.pl/view/9ac10523

eldderri

Thank you so much.It is shared now with you;

Waiting for your repy.

Rafael_ANEIC-PY

Hey, I'm in, but I don't see the "User Roles" table we talked about anywhere here

eldderri

I delete it because it caused a problem to my original app.This app is a sample made based on the original without the steps you have described.

Rafael_ANEIC-PY

Can you restore a copy of the app back when it had all the steps built in and share that one with me please?

eldderri

Give me some time to apply your steps again and see what will happen

eldderri

Have a look now:I have followed all the steps ,but got this error:

Rafael_ANEIC-PY

I fixed it, your Allowed Views and _allowedViews were configured as text, not enumlist of base text, that's why you were getting that error.

Also you didn't have a slice set up for that view.

Here's the proof it works:

eldderri

Hi friend;

Working as expected.I am very grateful for your valuable assistance.Thank you so much.

Marked solved

Rafael_ANEIC-PY

No problem, please mark the original answer as the solution so others that might have the same issue can find it easily among our many replies