About lookups

Contents > Working with related tables and files > About lookups

About lookups

A lookup copies data from another table into a field in the current table. After data is copied, it 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.

To establish a connection between tables for a lookup, you create a relationship. Then you 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 matching value. Next, it copies the value from the lookup source field into the lookup destination field, where the value is stored.

After a value is copied into the lookup destination field, you can edit, replace, or delete it like any other value (because the lookup value belongs to the current table). You can also update data in the current table to match data that changes in the related table.

Use lookups to:

Copy data from a related table (which can be the same table) and keep it as copied, even when the data in the related 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.

Maintain tables that already contain lookups, when you don't want to change the tables to a relational database.

Notes

Values in the match fields used for lookups do not have to be equal to match.

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.

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 destination field. To update the data, you must look it up again. This occurs when the value in the match field changes in the lookup destination table, causing FileMaker Pro to perform a relookup.

To copy data into a second field that uses the same match fields (for example, to copy a name and telephone number based on a matching client identification number), define a lookup for the second field, and use the same relationship as the first field.

All FileMaker Pro tables used in your database must be from FileMaker Pro 7 or later files. Files created with earlier versions of FileMaker Pro must be converted to the current version first. For more information about converting files, see the conversion information in the FileMaker Pro User's Guide.

More detail about working with lookups

Defining lookups

About relationships

Updating lookup values

Contents > Working with related tables and files > About lookups