User manual

Chapter 4
|
Working with related tables and files 99
Types of relationships
When you join two tables using a relationship, you establish criteria that FileMaker Pro uses to display or
access related records.
Your criteria can be simple, such as matching a field in Table A with a field in Table B, or more complex. A
more complex relationship definition will usually return a narrower set of related records. Examples of
complex relationships include using multiple match fields on one or both sides of the relationship,
comparing match fields using non-equal relational operators, or using calculated match fields.
Single-criteria relationships
In a single-criteria relationship, data in one field is matched to data in another field.
For example, a record in either table is related to any record in the other table when the values in the Client
ID field in the Clients table and the Client ID field in the Phone Numbers table are the same.
Use a single-criteria relationship when you want to relate two tables based on a single common value, such as a
serial number or an ID number. For example, a clients database has two tables: Clients, which stores names and
addresses, and Phone Numbers, which stores phone numbers and types of phone numbers, such as work, home,
fax, and so on. The data is split between two tables because a single client can have multiple phone numbers.
The tables have the following fields:
The Client ID field is the match field in the relationship between the two tables. In the Clients table, the
Client ID field is set to automatically enter a serial number, giving each record in the Clients table a unique
ID number. In the Phone Numbers table, the Client ID is just a simple number field.
Because the relationship is defined to allow the creation of related records in the Phone Numbers table, the
Client ID number from a Clients record will be automatically inserted in the Client ID field in the Phone
Numbers table in each new related record. This allows many records in the Phone Numbers table to be
related to a single record in the Clients table.
Users will be able to enter a phone number in an empty portal row, and FileMaker Pro will automatically
create a related record for that phone number.
Ta b l e Field name Comment
Clients Client ID Number field, auto-enter serial number. Match field for the Clients table
First Name Text field
Last Name Text field
Address1 Text field
Address2 Text field
City Text field
State Text field
Phone Numbers Phone Number Text field
Phone Number Type Text field
Client ID Number field. Match field for the Phone Numbers table
Match field Match field