Help

Working with related tables and files
F
ILEMAKER PRO HELP 395
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 customer 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 in FileMaker Pro 12 format. Files
created with earlier versions of FileMaker
Pro must be converted to the current version first.
See
Converting files from FileMaker Pro 11 and earlier.
Related topics
About relationships
Defining lookups
Important Before you begin, be sure you understand how lookups differ from relational databases.
See About lookups.
To define a lookup:
1. Define the relationship for the lookup between the match field of the current table and the
match field of the related table.