AnsweredAssumed Answered

Pulling data from another table in a drop down list to set a secondary key in the main table

Question asked by PecCars on Feb 3, 2011
Latest reply on Feb 3, 2011 by PecCars


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 ?