User manual

Working with related files 8-5
1 Define a lookup, to copy data from one related record for each
record in the master file (a one-to-one correspondence). For
example, use a lookup to copy the current price of a product into a
Line Items file. (Data copied to the master file doesn’t automatically
change when the data in the related file changes.)
Keep these points in mind:
1 Each relationship involves only one master file and one related
file; however, you can define many relationships from a master file
and many relationships to a related file.
1 The master file for one relationship can be a related file for another,
and a related file of one relationship can be a master file for another.
1 You can define many relationships between the same two database
files. For example, the relationships can refer to different match
fields.
1 You can define a relationship from one file to the same file (a self-
join). For example, use a self-join in a portal to display a subset of
data in the current file, such as all the employees of each manager.
For more information about self-joins, choose Help menu > Contents
and Index, click the Index tab, and type self-joins.
1 If the master file is protected with passwords, you must have the
master password to define relationships. In addition, when you type
data in the match field of the master file, you might be asked to type
a password for the related file when the related file is protected with
passwords. (See “Defining passwords” on page 9-1.)
1 You can create a many-to-many correspondence between database
files. (See “Creating many-to-many relationships” on page 8-15.)
About match fields for relationships
As you define and select the match fields for relationships, keep
these points in mind:
1 In most cases, the match fields used in a relationship contain equal,
stored values. For example, when one record in the match field of the
master file contains 009-845 and one record in the match field of the
related file also contains 009-845, a relationship is established
between those two records.
1 The match fields used in a relationship can have different names;
what establishes a relationship is that the match fields contain
matching values.
1 The match field in the master file can be any type except Container.
The match field in the related file can be any type except Container,
Global, or Summary.
1 The match field in the related file must be a field that can be
indexed.
1 If match fields are text fields, FileMaker Pro looks at the first 20
characters of each word in the field, up to 60 characters (including
spaces).
1 Make sure the match fields used in a relationship are the same data
type.
1 The match field in either (or both) the master file or related file can
contain a calculation formula. For example, the formula Unit
Price in the match field of the master file establishes a relationship
to a record (or records) in the related file whose match field contains
the value of the Unit Price field in the master file. You can also
design a calculation formula for the match field to match multiple
records. For example, the formula Unit Price < 100.00 in the
match field of the master file establishes a relationship to all records
in the related file whose match field contains a value less than
100.00.