User manual

62 FileMaker Pro User’s Guide
1 Define a lookup to copy data from a related table into a field in the
target table. The copied data is now stored in two places, just as if it
were copied and pasted into a target field. Looked up data is current
at the time it is copied, but once copied it remains static unless it is
re-looked up or the lookup is triggered again.
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.
For more information about relationships, see Help.
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