Click here to download the sample file.
FileMaker Pro Advanced AutoComplete Video
Hi, this is Don Clark of FM Database Consulting and FileMakerProGurus.com
Today I’m going to show you a simple technique that uses an Account Name value list with Auto Complete to assign the proper ID to an invoice record’s foreign key.
A good use for this technique would be to link an invoice to an account.
There are other techniques out there to accomplish the same result, but I have a client that does not like to scroll through dropdown or popup value lists, and is not patient enough to pop a new window, type and filter, and then choose from a list.
He wanted the drop down list to show the Account Name, and have the Auto Complete feature turned on, as well. This would allow him to just start typing the name and when he had typed enough to filter out all but the correct name, just hit the enter, return or tab key to finish entering the name and linking the invoice to the Account.
Normally, I would hide the foreign key field behind the Account Name field on the Invoice layout and show just the Account name in the value list as the second field. You can start typing and get the list filtered, but you have to type quickly or you will lose your place. When you select the name, the value entered into the field is the foreign key, not the name. Setting up your value this way disables the Auto Complete button.
So here’s my work around:
Use an Auto Complete drop down list with the value list being derived from the Account Name Field in the Accounts table.
As the user types, the correct name will eventually be isolated. The user then pressed enter, tab, or return, and a script trigger is fired upon exiting the Account Name field.
The script copies the exact Account Name from the field above, navigates to a blank Account layout, enters find mode, finds the correct Account record, saves the ID to a variable, returns to this layout, and sets the foreign key, id_Account, to the value of the correct Account.
Lookup values are automatically entered once the foreign key has been entered.
There are a couple of caveats —
1. Once you process an invoice, you should use security settings to prevent changes to the invoice. That will keep users from inadvertently changing the Account Name
2. This won’t work well if you have duplicate account names, which is not a problem with my client.