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.