Populate drop down list based on a value selected in a previous drop down list
I have the following database structure:
Table 1: hotel
Fields 1: id, name
Table 2: coordinator
Fields 2: id, hotel_id, name
I have the following data records for the first table:
1, Hilton; 2, Marriott; 3, Hyatt
I then have the following records in the second table:
1, 1, Hilton Person; 2, 2, Marriott Person; 3, 3, Hyatt Person; 4, 3, Hyatt Person 2
I have a relationship between hotel.id and coordinator.hotel_id in my relationships.
I then created another layout and have two fields on there. Basically what I'd like to happen is to populate the first field with either a drop down or pop up box with the hotel name. As soon as the user selects an entry from the first field it automatically populates the second drop down list based on the hotel ID that was selected in the first drop down.
So if someone selected Hilton in the first field, it would automatically populate Hilton Person in the second field. If they selected Hyatt, then it would automatically populate Hyatt Person and Hyatt Person 2 in the second drop down.
How can I acheive this?
Thanks in advance!