I am trying to set a layout that has two drop-down lists and based on the selection a third text field will be populated with data previously stored in a separate table.
The basic setup is that the main table has:
Text descriptor for an activity
Who performs the activity (first dropdown)
Location Where the activity is occurring (second dropdown)
Performer's licence number (this is what I want to retrieve).
I have a second table with all the performers names and contact details and I use this to create the value list for the first dropdown.
I have a third table with all the locations names and details and I use this to create the value list for the second dropdown.
Finally I have a fourth table that contains the performer ID (auto-enter serial number from the performer records), location ID (auto-enter serial number from the location records), and the relevant license. Each Performer-Location combination has a separate license.
How do I retrieve the license from the fourth table and either just display it directly, or put it into a field in the first table, based on the selections made in the drop downs??
I considered something like this Auto filling fields based on another field but couldn't see how it would work with more than 1 dropdown, and also this Filemaker Pro - Multiple Levels of dropdowns - YouTube but was too thick to get all the additional table instances and relationships to work.
Any help would be hugely appreciated.