Home > Example of a value list that includes only related field values

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 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. Similarly, if the value of Category changes to Clothing, the values for Item Name change to those records where Category matches Clothing.

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

1.	Define a self-join relationship in the Products table and use the Category fields as the match fields.

For more information, see About 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 starting from the Products table.

For more information about defining a value list, see Defining a value list for data entry.

3.	In Layout mode, add the Category and Item Name fields from the Products table to your layout.

For more information, see Placing and removing fields on a layout. If you want to create multiple line items on an invoice, you can add the Category and Item Name fields to a portal.

4.	Format the Item Name field 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.

5.	Enter data into your Category and Item Name fields.

After you enter a value for Category and tab into the Item Name field, FileMaker Pro retrieves the related records where Category matches the value you entered, and displays the corresponding Item Name values in the value list.

Note  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.