I feel like this is a really basic relational database question and I feel like a numpty that I cant make it work.
I have submission form to update table "Providers". In that submission form I have a field "Location" with a dropdown populated from a "Locations" table. In that Locations table, each location is a single record with 'Officer' as field. I'm trying to autopopulate the "Officer' field hidden in the submission form (for Providers) with the officer data determined from the "Location" selected in the dropdown, by referencing the corresponding Officer in the Locations table. Shoul