User manual

70 FileMaker Pro User’s Guide
For example, a database for a travel agency might have these tables: a
Tours table, which stores the products (tours) and their current prices;
a Clients table, which stores client information; an Invoices table,
which keeps a record of each invoice; and a LineItems tables, which
stores sales data for each line of the invoice, including the tour being
sold and the price at which it is sold. Because invoices are a mix of
dynamic and static data, you use both relational databases and lookups
to display your data. Records from the LineItems table are displayed
dynamically, in a portal on the Invoices layout, but the actual sales
price of each line item is entered using a lookup, so the invoice totals
remain historically accurate, even if prices change at some future date.
You create a relational database by defining a relationship between
two fields, called match fields. These fields can be in different tables
or they can be in the same table (a self-join). You are able to access
related data when the value in the match field(s) on one side of the
relationship compares successfully with a value in the match field(s)
on the other side of the relationship, according to the criteria you
establish in the relationship.
After you have created a relationship, you can use fields from the
related table just as you would use any fields in the current table: to
display data on a layout, as part of a calculation formula, in a script,
as a match field for another relationship, and so on. You can work with
the data in the related fields in all modes (if you have access
privileges) when you work with records in the current table.
When you display related data in a portal, values from all related
records are displayed. When the related field isn’t in a portal, the value
from the first related record is displayed.
Lookups copy data from the related table into the current table
R20Client ID
NY-ParisRoute
R20Client ID
NY-ParisRoute
In relational databases, data from the related table is only displayed in
the current table, not copied
R20Client ID
NY-ParisRoute
R20Client ID
NY-ParisRoute
Current table
Related table
Current table
Related table
Invoices table
A-200-61
Invoice No
16-Oct-2005
Date
C100
Client ID
Tour
ID Tour Name
T10
Cost $
NY-Roma 550.00
T20 Roma-Istan 700.00
Williams
Name
1,250.00Total
Clients table
C100
Client ID
Williams
Client Name
408-555-3456
Phone
Tour ID
Tour Name
T10
NY-Roma
T20
Roma-Istan
Tours table
T10Tour ID
NY-RomaTour Name
Route
ID Origin
R200
Destination
NY Paris
R42 Paris Roma
Records from the
LineItems table
displayed in a portal