To further clarify my post, I managed to achieve what I needed by making the Drop-Down List taking the values from a Value List that Shows only the Second Column (PRODUCTS_PN) but takes the value from the PRODUCT_ID (First Column). This works, except that the Auto-complete function works only if you type the value quickly without delays, e.g. if I type the first few characters of the Part Number (PRODUCT_PN) and stop to read the rest of the characters before typing them, the auto completion is reset which is an inconvenience. Also, another inconvenience is that, although the Drop-down List only shows the second column for the look-up, after the final value is entered, the field actually shows the PRODUCT_ID value and not the PRODUCT_PN.
Thanks in advance for any comments or suggestions.
See this demo file:
FileMaker 12 users: https://dl.dropbox.com/u/78737945/SimpleNameLookupDemo.fmp12
Pre FileMaker 12 Users: https://dl.dropbox.com/u/78737945/SimpleNameLookupDemo.fp7
It uses an auto complete value list on a text field to look up the ID number. Scripting is included to handle the issue of possible duplicate names.
Thanks PhilModJunk for your help with this.
I actually ended up re-linking the tables by the PRODUCTS_PN column as it made my life way easier and the PN turns out to be unique across the T_Products Table.
Just because a field always contains unique values does not mean that it is the best choice for use as a primary key in relationships. Such a "name" or "description" field is vulnerable to users deciding that the text in the field should be changed. And such a change will create issues for your database when that change then "breaks" the link to other related records where the match field still contains the original name or description.
Keeping your records linked by an auto-entered serial number that is never ever changed avoids this issue. That's the point behind the demo file. It allows you to use a name or description for a search with an auto-complete enabled drop down list and yet a serial number field remains the primary key.