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

AppSheet AppSheet Q&A
, , , , , , ,
1

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!

2

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.

1 Like
3

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

1 Like