Auto Complete Field

[mtwildtrout]

I have a field on a layout that uses a dropdown list to select an account name. The dropdown uses a related table to populate the dropdown list. This table includes the account name & account # for each account name.

I want to be able to select the account name from the dropdown so it can be entered in the account name filed of the record & have the corresponding account # automatically entered in the record's account # field.

Is ther a way to accomplish this without a button? Can it be triggered in some way? I have FM Version 9 Advanced.

[Makah]

Yes, this is possible.

For clarity, I'm going to assume you have an "order" table and an "account" table.

You need to set up a relationship between your order table and your account table (many accounts to one order).

On your order table, you will have an account name field and a account number field that are unstored calculations. The definition for the account name will be something like Account::AccountName, and for the account number will be Account::AccountNumber.

Once you have that set up, on your order layout, you will have the foreign key for account names. It will be a drop down list based on a value list where the first value is the primary key in the accounts table and the second value is the account name. Include all values and show only values from the second field.

Now add the account name field from the Order table and overlay it on the drop down foreign key field you just created. You want to make it non-enterable in browse mode.

Now you will see if you click on the account name, you will see a list of all the available accounts. When you change the account, you are actually changing the foreign key, and your unstored calcs will update as well.

You may notice that while you are in the drop down list, you can actually see the number in the foreign key field. You can fix this by changing the color of the text in the foreign key to be the same as the background color. Also, you'll probably want to validate the foreign key to be part of your value list in case the user types something into the field instead of picking from the drop down list.

Hope this helps. Let me know if you have any questions.

[LaRetta]

Welcome to FM Forums, mtwildtrout!!

I want to be able to select the account name from the dropdown so it can be entered in the account name filed of the record & have the corresponding account # automatically entered in the record's account # field.

There may be a few easier approaches (attached) and I believe you will see that both Account Number AND name don't need to be inserted into your Orders table, either as text [color:red]* or as calculations:

Example 1:

Pop-up menus are best when inserting IDs because they restrict the User to only valid entries AND pop-up menus display the name instead of the ID so it handles two issues in one (keeps the technique very simple). The yellow field shows that, what actually implants into the AccountNumber field is truly the ID even though it displays the name itself.

Example 2:

Shows how to use a drop-down list instead. The pink field is actually the related account name. Allow entry to the field is turned off and it is placed on top of the Orders::AccountNumber and you color the background so the underlying AccountNumber field won't show. You can move the pink field to see it. When User clicks the pink field to select Account Name, the AccountNumber field beneath opens via drop-down and allows selection.

[color:red]* ... sometimes it is important to insert the second value (description) into the order. For instance, it might be important to keep track of an order's shipping address back through time, even if the customer has moved since then. If you truly require this need to plant the second piece of data for audit trail purposes, go to the Orders::AccountName field and set it as a lookup or auto-enter which points to the Accounts::AccountDescription field instead. But usually, we only insert the IDs and display all other related information through the relationship by cross-placing the fields (as I did with the pink AccountName field).

Accounts.zip

Edited December 13, 2009 by Guest
Added *

[mtwildtrout]

Thank you for your suggestions. I appreciate you taking the time to help me sort this out. I will give each suggestion a try.

[mtwildtrout]

Thank you for your suggestions. I will give it a try. I appreciate you taking time to help me.