Example of a value list that includes only related field values

If you are generating the values in a value list from a field, you can have FileMaker Pro Advanced dynamically change the value list's values based on the values in a second field. In other words, the value list will only display values in the first field from related records where the relationship definition is met by a value entered into the second field.

For example, imagine you have a table called Products that tracks a large number of inventory items. You could define a value list and assign it to the Item Name field to display only a subset of those items based on values in a second field, Category. During data entry, when you specify the value of the Category field (for example, Accessory), only the Item Name values where the Category equals Accessory show up in the value list.

To set up a value list like the example above, do the following:

The following instructions assume that you have a table called Products. One of the fields in the Products table is called Item Name. It is a list, displaying the name of each item (for example, Necklace or Bracelet). Another field is called Category and displays the list of different categories (for example, Accessory or Clothing).

1. Define a self-join relationship in the Products table and use the Category fields as the match fields. For more information, see Self-joining relationships.

Defining a self-join relationship creates another occurrence of the Products table in the Relationships graph (referred to here as the Products 2 table).

2. Define a value list that uses values from the Item Name field in the Products 2 table. Define this value list to include only related values from the Products table.

For more information about defining a value list, see Defining value lists.

3. Format the Item Name field in the Products table to display the value list you created in step 2.

For more information, see Setting up a field to display a pop-up menu, checkbox set, or other control.

4. In Browse mode in the Products table, choose a category from the Category field, then click the Item Name field to choose an item. The Item Name field displays only the items that are related to the selected category.


Both the field with the value list values and the match field for the relationship must reside in the same file, for example, Category and Item Name from the example above.