Use Inquiry::PropertyID for you drop down list or pop up menu. Define your value list to show the ProperyID's in column 1 and the Property Names in column 2. Your user will select by name, but the ID number will be entered into the field. If you enforce unique property names, you can hide the first column so that all the user sees in the value list are the property names. If you use such a value list with a pop up menu, the field will display the property name even though the ID number is what is stored in the field. If you use a drop down list, you'll see the ID number in the field after you select a value. In those cases, you would add the Property::PropertyName field to your layout, next to this field to display the property name.
There are also scripted approaches you can take that allow you to exploit FileMaker's auto-complete feature in a drop down list of names, but you said you didn't want to "break your fingers" on a script...
thanks PhilModJunk. Almost there but not quite yet. It works but ... when the user picks up the name the field shown is the id. I have tried by having two fields in the portal: Inquiry::PropertyID and Property:Property and have only the second field be selectable; the consequence is however that the name is overwritten in Property::Property and Inquiry::PropertyID is not assigned.
Here my example, of you look in the layout Inquiry and look in the field Property:
You are almost there. Lock Property::Property in Behavior of the data tab of the inspector so that it cannot be edited. Make Inquiry::PropertyID your editable field with the value list I described. When you select an ID number (with the name in the second column), you'll see the property name appear automatically in Property::PropertyID.
fantastico !! It works, PhilModJunk. Although not so elegant as the ID cannot be made completely invisible even by setting the colour to white. I just overlapped both fields, so that the ID just appears when pulling the drop down list.
In retrospect I find the MS Access cascaded SQL solution much more elegant and clean, so let's what other surprises does the Mac world bring now that I am crossing over to the dark side after more than 25 years of Windows life.