To create a value list based on fields in a table in your database system:
- Open Manage | value lists
- Click new
- Click the "Use values from a field" option.
- From the top left drop down list, select a table occurrence based on your table where the values are stored.
- Now click a field in the list of fields that appears below it.
For a simple value list, you are done and can click OK to dismiss dialogs and can specify the new value list as a drop down list format for text fields on your layouts.
The other options in this dialog:
Also display values from second field:
If you select this option, you can select a second field from the same table or a table that is linked to it in a relationship. This is used in cases where you need to provide the user with more info to help them select the correct value--especially when the first value is an ID number.
Include Only Related values....
You can specify that a relationship be used to limit which records in your table of values supply values to the value list. This is most often used to set up conditional value lists where a value entered into field 1, control what values appear in a value list used with field 2. I have links on conditional value lists that I can share if you are interested in this option.
Show Values only from second field
Values from the first field are what get entered into the layout's field when you select a value. Sometimes the value in the first field means nothing to the user and thus you can simplify what they see in the list by hiding the values in the first field. For drop down lists, this only affects the value list, the first field value will appear in the field once you exit the field. In Pop up menus, the second field value is still displayed--but the first field value is stored, in the field after you exit when this option is selected.
Sort values using
Values from a field are always sorted in ascending order, either by the first or by the second field as controlled by this option.
A note of caution when specifying the Show values or Sort values options:
When you show values from only the second field or sort values using the second field, records with duplicate values in the second field are automatically omitted from the value list--which can omit values you need from the first field. If you have first field = Macintosh, second field = Apple, first field = Granny Smith, second field = Apple, sorting on the second field or only showing the second field will omit either Macintosh or Granny Smith as a value you can enter into the field when you select from this value list. It's often a good idea to put a unique values validation on the second field to prevent problems with such duplication of values.
Second field Tip:
It's often necessary to define a field that merges data from several different fields so that it can be used in your value list as the second field. Example, you might use EmployeeID as the first field and EmployeeFullName as the second with it defined with LastName & ", " & FirstName as an auto-entered calculation to combine names from the first and last name fields. (Use the auto-enter option so that you can still use a unique values validation if you need it.)
Leave this control unchanged unless you are working with a multiple languange database solution where you need to see the values sort in an order specific to a language different than your system's current language settings.
Thanks for your clarification.
I'm puzzled that Drop Downs Lists which contain an ID Field show the ID field/number after the user presses the Return/Enter key, and not the second field which is much more meaningful.
Is the only reason to save a few bytes per record by storing a number instead of the literal text?
You'd have to ask a programmer who works for FileMaker that question. I can't tell you why, only what.
It's easily worked around though. You can place a field with an opaque fill color and with browse access denied on top of the drop down list field. Make this field the secondary field from your values table (must be related via link to primary field) and you see just the second field value. Click on the field in front and the list pops to the front to display the value. Exit the field and it disappears back behind the secondary field.
Thanks again — this time for the work-around receipe.
The hints about using a field in the value list that concatinates the values from other fields really helps with sorting the data usefully. Thanks...preferable to using contact Name in Contacts.