one field in submission form determining the content of a hidden field

[AndrewPC]

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. Should this be done using a view or a relationship or both? 

[Alison]

Hi @AndrewPC,

I recommend using a Cascading fom elements to get the corresponding data in the child field based on the selection in the parent field. Please look at these articles for more information:
https://howto.caspio.com/datapages/datapage-components/cascading-elements/
https://howto.caspio.com/getting-started-videos/video/?cbResetParam=1&vid=E771C2496234470C93143FC9C0261E8F&menu=menu-item-27516&title=Adding Cascading Elements to DataPages
https://howto.caspio.com/tables-and-views/lookup-tables/

[AndrewPC]

Thanks Alison.  Can I make the resulting child field invisible to the user?

EDIT: Never mind I've hidden it using HTML blocks.  

Edited September 14, 2020 by AndrewPC
new information