Expression Assistance

[tintin007]

Hi Community,

I have

A user table with columns, [ID] [User Email] [Role ID] and [Active[

A user role table with a [Role ID] and various ye/s no columns for different roles.

A Menu Table with the menu items includes a column [View Link] and [Active]

The Below formula works with a single item where i have admin in quotes : eg "Admin"

IF(Lookup(Useremail(), User table, User Email, Role ID) = "Admin", [View Link] = "Home",
and([View Link] = "Home", [Active] = "False"))

I am trying to create a filter for a menu set up.  but there can be more than one role for each menu item. I have an enum list set up on the Menu table which a column type Ref.

I tried changing this  " Role ID) = "Admin", [View"   to and or or any, list formula but didn't get it to work just returned the first value.

cant decide if i should use IFS , OR , Index , List somewhere amongst this to return multiple field roles. 

The Active column is used to indicate if a user is active from the user table or a menu is active from the menu table

The formula above works but it does not include the User Role Table or the active columns. 

Hopeful someone can help

Cheers

Should also say i do have a Current User slice on the User Table and tried  INDEX(Current User[Role id],1) in part of the formula as well with no joy

[MultiTech · Accepted solution]

---

@tintin007 wrote:

INDEX(Current User[Role id],1)

---

FYI: This should work, provided everything is aligned correctly.

If you've got a single value in the role column for the user, and you've got a list for the menu option, you need to use IN() for the comparison.

• It might be just a matter of syntax is all.

[Tommy62]

Schauen Sie sich an, was meiner Meinung nach das beste Erklärvideo zu diesem Thema ist: Show a User only its data in AppSheet

[AleksiAlkio]

I normally filter the Users table with the Security filter and then filter the main menu with the EnumList. Something like..
IN(ANY(Users[Role]),[Roles])

The Users table is filtered with [User Email]=USEREMAIL() and then ANY() will find that one row only. The "Roles" column is then an Enumlist.

[MultiTech · Accepted solution]

---

@tintin007 wrote:

INDEX(Current User[Role id],1)

---

FYI: This should work, provided everything is aligned correctly.

If you've got a single value in the role column for the user, and you've got a list for the menu option, you need to use IN() for the comparison.

• It might be just a matter of syntax is all.

[tintin007 · Accepted solution]

HI,

Going to stick with this formula for now till i have more time to sit and figure out why i can't get the user role table in sync with the check boxes. Like you say, i am missing a key point. 

IF(Lookup(Useremail(), User table, User Email, Role ID) = "Value", [View Link] = " value 1",
and([View Link] = "value 1", IN(ANY(Current User[Role ID]),[Menu Role ID]))) 

this slice row filter gets me going with user role enum list in the menu table

[tintin007]

HI,

Thanks for the responses. I got this working with the formula below, But I would rather use the columns in the user role table than an enum list in the menu table for each row..  It seems to show the row even if the user does not have access. I think the False value in the if statement is wrong somewhere.

this is the table slice formula

IF(Index(SELECT(User Role[column], ([Role ID] = Index(Current User[Role ID], 1))), 1), [View Link] = "Accessory",
and([View Link] = "Accessory",IN(ANY(Current User[Role ID]), User Role[Accessory])))

Each view has this formula

Index(SELECT(User Role[ Column name], ([Role ID] = Index(Current User[Role ID], 1))), 1)

removed from menu table the ref column for role id as I don't really want to use this. 

User role table has a ref column Role ID back to the user table.

Wondering if i should make the [view link] a ref column to the user role table so it can match up the view name ??

@MultiTech , Love your you tube videos they have inspired me to get this far 🙂

Cheers

[MultiTech]

---

@tintin007 wrote:

@MultiTech , Love your you tube videos they have inspired me to get this far

---

Thank you! (^_^)

------------------------------------------------------------------------------------------------------------

FYI: anytime you're using SELECT(), it's not really the way to go.  

• Select() is a brute-force style of formula, one that literally forces the answer out of the database.  There are more efficient methods for accomplishing what you're wanting.

It sounds like you're trying to do something, but you're missing a key aspect of something - that's why you're having to do all this SELECT() stuff to make it work.

• It should be straightforward and easy, provided everything is in place.  If it's not, then something is missing - you just need to figure out what and where.

The setup you had in the beginning:

---

@tintin007 wrote:

I have an enum list set up on the Menu table which a column type Ref.

---

Your formula for a slice to filter the appropriate menu options would then be:

IN(Index(Current_User[User_Role], 1), [Menu_Approved_Roles])

• It should be that easy; if it's not, there's some key piece missing from somewhere....

-------------------------------------------------------------------------------------------------

Not to push anything on you, but I actually have a solution for this that I've been using for years; an auto-table with corresponding guides on how to setup and use it.

• You can find more in the Answer Portal.