Defining and updating lookups
A lookup copies data from another table into a field in the current table. The copied data then becomes part of the current table (and remains in the table from which it was copied). Data copied to the current table doesn't change automatically when the data in the other table changes.
For example, use a lookup to copy the price of an item at the time of purchase into an Invoice table. Even if the price in the related table changes, the price in the Invoice table stays the same.
Use a relationship to establish a connection between tables for a lookup. Then define a lookup to copy data from a field in the related table into a field in the current table.
When you type or change a value in the match field of the current table, FileMaker Pro uses the relationship to access the first record in the related table whose match field contains a value that satisfies the criteria of the relationship. Next, it copies the value from the lookup source field into the lookup target field, where the value is stored.
You can edit, replace, or delete the value in the lookup target field as any other value in the current table.
If you change data in the match field of the related table or in the lookup source field for a lookup, FileMaker Pro doesn't automatically update the data in the lookup target field. To update the data, you need to perform the lookup again, either by choosing Records menu > Relookup Field Contents in Browse mode or by modifying a match field in the relationship used by the lookup.
Important When a lookup is based on a multicriteria relationship, a lookup is activated each time a value is entered into any field that is involved in the relationship. The lookup is performed each time a value is entered into one of the required fields.
To define a lookup:
- Choose File menu > Manage > Database, then click the Relationships tab.
- Define the relationship for the lookup between the match field of the current table and the match field of the related table.
See Creating and changing relationships.
Be sure the lookup source field and lookup target field are the same field type.
- Click the Fields tab.
- Select a table from the Table list.
- Select a field from the list of existing fields, or define a new field, then click Options.
- Click the Auto-Enter tab, then select Looked-up value.
- For Starting with table, choose the table that the lookup will use as its starting point in the relationships graph. For Lookup from related table, choose the related table from which the related data will be looked up.
- Select the field from which the lookup value will be copied.
- Select options for the lookup.
To update lookup values:
- In the current table, find the records you intend to update.
- In Browse mode in the current table, select the match field, then choose Records menu > Relookup Field Contents.
The Relookup Field Contents command does not commit the active record. See Committing data in records.
Important Relookup Field Contents updates all fields looked up by the match field across all records in the current found set. You can't undo your actions when you update lookup values.
Notes
- When the same value exists in the match field in more than one record of the related table:
- the value from the first created related record is copied if the relationship does not have a sort order.
- the value from the first record in the sort order is copied if the relationship does have a sort order.
- The relationship can sort the related records so that the first related record (the record the lookup will copy) is a particular record. For example, to look up the most recent checkout date for a piece of equipment, sort records in descending order by checkout date. See Creating and changing relationships.
- You can automatically update lookup values by defining a button or a script trigger to perform the Relookup Field Contents script step.