Open Manage | Value lists
Start a new value list and select the "Use values from a field" option.
Select Customers Details in the drop down and click C_ID to select the ID field as the primary field. This field's value will be entered when you select a value in this field.
Click the "Also display values from second field" checkbox
Select C_Name as the secondary field.
At this point you have a value list that displays both the ID and name values, but enters the ID number when a value is selected.
You can select the the values be sorted by Name and/or that only the names are shown, but if you use either or both options, make sure that customer names are always unique as duplicate names will then be omitted.
This is the "beginner" level value list to do what you asked. More sophisticate approaches are also possible.
Thanks for that, but it does not quite do what i am trying to do. I have the value list ok,
on my invoice layout if i attach this value list to the I_C_ID field, I get a drop down list, as you say, and can make this just a list of customers if i wish. Once a selection is made the customer id is entered in the I_C_ID field.
However there are two issues.
1. There is no form of auto complete durng the data entry, and the option seems to be greyed out, I want the person to start typing the customer name, and it auto-complete from the list as they type, or of course they can scroll down the list to select the customer name they are after. I guess the problem may be the field I_C_ID is expecting a number.
2. I dont really want the customer number showing. I am after a drop down box, on the invoice layout, that contains the list of customers, that auto completes, as the user types, or allows them to scroll down the list and select. Once a selection is made the customer name is displayed in this field, not their ID, but in my invoice table the customer id is stored, (C_ID) in the I_C_ID field.
Hope that makes sense.
1) this is a basic limitation of FileMaker value lists. One that is very irritating, but it is possible to devise work arounds that involve some scripting so that you can get an auto-complete enabled value list to work for this. Remember that I said that my first suggestion was a "beginner level" solution.
2) If you select pop up menu and set up the value list to only display names, the numbers will not be shown. If you want to use the drop down list, you can place the name field from the related table on top of the drop down list and use field behavior settings to prevent accss to this name field. When you click on this name field, control is passed to the hidden drop down list behind it and it pops to the front and deploys a list of values. When you exit this value list and field, it pops back behind the name field.
Here are some more sophisticated approaches that enable auto-complete. They also handle duplicate names so that you no longer have to require that the customer names be unique.
A simplified method I recently worked up:
A more sophisticated demo file with several "search portals" also included. If you are using FMP 12, it will convert to FMP 12 format and the converted copy should work just fine. (Open it from the file menu to convert it.)
Many thanks, for he time an effort.