I'd strongly advise you to use an auto-entered serial value as your PK. You should never have to remember, or interact in any way, with The PK. It should be invisible to the user (including the developer) if the design is correct. If the user must select a PK value, using value lists with a second field also shown and "Show only second field" should be used.
Yes I agree as I do hide PK from user but my question remains as in the invoice example when entering product items to the invoice the drop down list comes from the value list using the ID's to establish the link and to inport the product information in the item line portal of the invoice.
What I would like to do is have the user type the product name maybe even in a dummy field then do a find in the related product table in a new window for user to select the correct product, then have it linked to the invoice. An ID drop down list just won't cut it. as shown in example.
Can I do this rather than as in my 1st post please look at the invoice sample and you will see what I mean.
Thank you for any help
There are several different widgets you can build into a FileMaker solution to make text based searches with Primary Key values entered as ultimate result of the search.
One method is to use an auto-complete enabled drop down of your product descriptions with scripted support that handles multiple match results and which then enters the selected item's ID number where needed.
Another method is to set up a filtered portal with a filter expression that matches text in the portal table's field to a a search field above it. The fields in the portal can be set up as a button that enter's the PK of the selected item into your line items portal. As the user types in more and more text, the listed items in the portal filter to smaller and smaller lists of matching records. With this method, you'd need to use the OnObjectModify script trigger to run this script to force the portal filter to update with each key stroke:
Refresh Window [flush cached join results]
Thank you so much, as your example was right on. Havn't had time to try it but the scripting makes sence I also found that I can open a window of the products in list mode and drag and drop but then I will have the have the ID's field exposed.
I'm loving FM more and more.