Help

Working with related tables and files
F
ILEMAKER PRO HELP 375
• 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 relookedup or the lookup is triggered again.
Important Whenever you want to use data from another table — either in a relational database or
for a lookup — you must first define a relationship between the two tables.
For example, a typical Sales database may have these tables: an Invoices table, which keeps a
record of each invoice; a Products table, which stores the products and their current prices; and a
LineItems table, which stores sales data for each line of the invoice, including the item being sold,
the quantity, and the price at which it is sold. Because invoices are a mix of dynamic and static data,
you use both related fields 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 the same, 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.
When you display related data in a portal, values from all related records are displayed, subject to a
user’s
access privileges. When the related field isn't in a portal, the value from the first related
record is displayed. See Creating portals to display related records.
Note Use access privileges in the source table to limit or prevent access to related data. For
example, users must have access privileges to view a related match field to see the related fields
from that relationship. See
Creating and managing privilege sets.
You can sort related records before displaying them. When you place a related field directly on a
layout, you see the value from the first related record in the sort order (such as the lowest or highest
value). When you display related fields in a portal, the related records are displayed in the sort order
assigned to the portal, which takes precedence over any sort order in the relationship definition.
Notes
• You can base relationships on one or more match fields in each table. Match fields should
be the same type, for example, number fields or a calculation field that returns a numeric
result.
• Relationships are always created between two tables, or, in the case of self-joining
relationships, two occurrences of the same table in the relationships graph. You can
connect relationships together in a series and access related data from any point within that
series, but each relationship is created between only two tables.
• Two or more related tables cannot form a cycle. Each series of relationships must have a
beginning table and an ending table.
• The number of relationships is limited only by disk space.
• Relationships are bi-directional, although you can set independent record sorting, creation,
and deletion options for each table in the relationship.