Dropdown box with 2 nested lookups

I have 3 tables.

User - contains list of users
departments - contains list of departments
user_departments - contains a list of users and department assignments

I need a dropdown that lists the departments names, based on the current user.

My choices source is “Search for deppartments’s name” and I have a constraint that is a search for user_department’s ID, where the current user’s ID is in user_department.

When I have a single value in user_department, the dropdown box works. As soon as I add a 2nd value, it fails to return rows.

Are you unable to pass a list through 2 “search for’s” when loading values?

Bump, any assistance would be appreciated!

I will suggest you make some changes to your database (tables) to make things simple.
You don’t need the third table.
Just have two tables like below:
image
image

So a User has a list of Departments and a Department has a list of Users.

Then you can setup your dropdown simply like below

1 Like

Thank you! I’ll try this method out!

Update - Thank you again. This worked perfectly. Coming from a true database environment there are some small tweaks I need to make to my thinking. Thanks again for the education lesson :slight_smile: