Filtered dropdown list from related table
I'm looking to create three dropdown lists which will display staff members depending on their work role, such as manager, senior, driver.
I have many customers and many staff, therefore a join table exists between my customers table (where I want the dropdown list to be) and the staff list table.
The staff list table is simple enough, consisting of name and type (manager, senior, driver etc). The staffrelationships table is customerIDFK, staffIDFK, staffname (calculation from staff table)
On the layout for client, I need three dropdowns, one for manager, one for senior, one for driver. Each dropdown should filter, based on the detail in the staff table, for only one particular type of staff.
I've been through lots of forums and I'm struggling to get something I can work from. I can generate a list of ALL staff, that's not a problem! Staff filtered by work role is something I am stuck with.
Thanks in advance