Pulling data from another table in a drop down list to set a secondary key in the main table
I am having an issue on pulling data from table 2, showing it in a drop down list of table 1 via value lists ... but am not able to use the id of that field to set the secondary id in table 1.
Here the details. I am migrating an MS Access 2003 app to Filemaker Pro 11. This is about two tables: Inquiry and Property with Inquiry::PropertyID and Property::PropertyID with autonumber.
The idea is I have a layout in Inquiry in order to enter allow a user enter details for an inquiry. I put a drop down list for the properties, these being picked from the Property table and I allow the user to pick from them. I have implemented this via a dynamic value list, that picks the Property::Name. However, when the user picks a property from the drop down list, FM updates Property::PropertyName instead of assigning the corresponding ID to the Inquiry::PropertyID.
I could of course use directly the Inquiry::PropertyID for the drop down list, but that looks silly picking a number for a property. I would rather have the user pick from a list of property names. And then setting Inquiry::PropertyID based on the user's choice of Property::Name
MS Access calls this option Enforce Referential Integrity in a one-to-many relationship and works seamlessly there. In fact the MS Access solution works based on a cascaded sql query. Meaning behind the drop down list an sql query is issued that takes care in the background of pulling the property names, displaying them on the drop down list and assigning ONLY the corresponding PropertyID to Inquiry::PropertyID once the user has chosen the Property::Name.
Any ideas for implementing this in FileMaker without breaking my fingers with some complex scripts ?