User manual

Chapter 4 | Working with related tables and files 102
For example, a database for a bakery might have these tables: a Products table, which stores
information about bakery products and their current prices; a Customers table, which stores
customer information such as customer names and contact information; an Invoices table, which
keeps a record of each order; and a LineItems table, which stores sales data for each line of the
invoice, including the item 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.
Relational database terminology
Before you begin working with relational databases and lookups, you should understand the
following terms. These terms are explained in the sections that follow.
Term Description
Current table For relational databases, the table that you are currently working in.
For lookups, the table that the data is copied to.
External table A table outside of the current file, in another file.
Lookup target field
(for lookups only)
The field in a table that you want data copied to during a lookup.
Invoices table
003Invoice ID
16-Oct-2012
Date
3
Customer ID
FB7
Unit Price$
Fruitcake $15.50
FB2 Chocolate Cake $22.50
Williams
$38.00Total
Customers table
3
Williams
Last Name
408-555-3456
Phone
Product ID
Product Name
FB7
Fruitcake
FB2
Chocolate Cake
Products table
FB7Product ID
FruitcakeProduct Name
Bakery
ID Location
B20
Ships From
France Paris
B46 Italy Roma
Records from the
LineItems table
displayed in a portal
Customer ID
Last Name
Product ID
Product Name