User manual

8-6 FileMaker Pro User’s Guide
1 You can increase the number of possible matching records in either
the master file or related file by entering all possible matching values
in the match field (sometimes called a multi-key field or complex key
field). Suppose you want to display all the sales contracts obtained
by each employee, including those obtained by teams of employees,
in a portal in the master file. To do this, in the match field of the
related file, enter the name of each employee who worked on the
contract that’s entered in the Contract Name field in the same record.
Place each name on a separate line in the match field, and end each
line with a carriage return. When the match field in a record of the
master file contains any of those names, the associated contract
displays in the portal of that record. (When you design a multi-key
field to hold identification numbers, remember that each FileMaker
Pro file has a limit of 64,000 characters in text fields.)
1 Match fields can contain non-alphanumeric characters. To do so,
choose File menu > Define Fields. Select the match field in the list,
click Options, then click the Storage tab. For Default language for
indexing and sorting text, choose ASCII.
1 A match field used for a relational database can be a lookup
destination field, as long as the lookup isn’t based on a relationship
that involves the match field. (For an example of this use of a match
field, see “Accessing related data from a third file” on page 8-14.)
About lookups
You look up data by first defining a relationship between matching
data in the master file and a related file. Then you define a lookup for
a field in the master file, which copies data from a field in the related
file into a field in the master file.
When you enter a value in the match field of the master file,
FileMaker Pro uses the relationship to access the first record in the
related file whose match field contains a matching value. Next, it
copies the value from the lookup source field in that related record
into the lookup destination field in the master file record, where the
value is stored. Data that’s copied into the master file doesn’t change
when the value in the related file changes, unless the data in the
match field in the master file changes. In that case, FileMaker Pro
performs the lookup again.
After data is copied into the lookup destination field, you can edit,
replace, or delete it like any other data (because the lookup value
belongs to the master file). You can also update data in the master file
to match data that changed in the related file. (See “Defining
lookups” on page 8-13.)
Important When the same value exists in the match field in more than
one record of the related file, FileMaker Pro copies the value from
only the first related record. (The first related record that’s accessed
depends on whether the related records are sorted. For more
information, see “Defining relationships for relational
databases and lookups” on page 8-10.)
Keep these points in mind:
1 Values in match fields used for lookups do not have to be equal to
match. (See “About match fields for relationships” on page 8-5 and
“Defining lookups between files” on page 8-13.)
C235Client ID
Name
42.83.91.04Phone
ParisCity
C100Client ID
Name
555-3849Phone
VancouverCity
Master file
C200Client ID
SmithName
Related file
Relationship
Lookup destination field
Lookup source field
C200Client ID
SmithName
555-1234Phone
New YorkCity