Multiple selection of Enumlist using Ref and showing only specific data from another table

[marsbar]

Hi All,

I am struggling to implement multiple selections from a list of employees that will only show me the relevant employee for that specific Users Company ... phew, thats is a mouthful!

So... I have a user Access Table that I use in a Ref column as an employee list on my MAIN TABLE, this is used for security functions, ensuring only people from a certain company/access can see their own company staff/access level data:

User Name	Company	Access Level	Email
User 1	Comp1	Super	user1@company.com
User 2	Comp1	Admin	user2@company.com
User 3	Comp1	User	user3@company.com
User 4	Comp1	User	user4@company.com
User 5	Comp2	Admin	user5@company.com
User 6	Comp2	User	user6@company.com
User 7	Comp2	User	user7@company.com
User 8	Comp3	Admin	user8@company.com
User 9	Comp3	User	user9@company.com
User 10	Comp4	User	user10@company.com

I have an existing filter expression in the MAIN TABLE settings allowing the viewer to only see those employees (Column A) in their specific company (Column B).

In the MAIN TABLE I would like the Ref column to allow me to select multiple employees but ONLY FROM THE CURRENT USER'S COMPANY.

eg. Id like User 6 to only see User 5, User 6, User 7 in the list of employees (all work for Comp2) and the user would be able to select one, two or all of these employees.

or Id like User 2 to only see User 1, User 2, User 3, User 4 in the list of employees (all work for Comp1) and the user would be able to select one, two or all of these employees.

I have tried changing the Ref column to Enumlist with a Base of Ref, but this only shows the current user...not the other staff from their company!

Can anyone help with this please?

Thanks in advance!

[Steve · Accepted solution]

In Valid if or Suggested values, try this expression:

FILTER(
  "Access Table",
  ([Company] = LOOKUP(USEREMAIL(), "Access Table", "Email", "Company"))
)

You must use the EnumList column type to allow multiple selections.

[marsbar]

Great thanks - that seems to be working. Really appreciate it thanks!!