User manual

8-14 FileMaker Pro User’s Guide
Accessing related data from a third file
There may be times when you need to access values from a field
whose values are accessed from another file. You can’t directly
access a field in a file several relationships away that’s already used
as a related field in another file. However, you can define
intermediate calculation fields to access those fields that are more
than one relationship away.
Suppose you want data from a Part Name field (which is in a Parts
file) in an Invoices file. The Invoices file uses related data from a
Line Items file, and the Line Items file uses related data from the
Parts file. However, the Line Items file doesn’t use the Part Name
field from the Parts file. (The Invoices file uses other values from the
Line Items file, and the Line Items file contains some fields that the
Parts and Invoice files do not.)
Using a calculation field in this way ensures that data in that field
(Part Name in the Line Items file) changes as the value in the Part
Name field in the Parts file changes, and prevents data duplication
errors from occurring.
To access data from a third file:
1. In the intermediate file, create a new field, and make the field a
calculation field.
In the example, create a Part Name field in the Line Items file.
2. Define a formula for the new field using the syntax:
Relationship name::Related field name
Relationship name is the relationship defined from the intermediate
file (in the example, Line Items file) to the first file (Parts file).
Related field name is the name of the field in the first file whose data
you want calculated (Part Name field in the Parts file) in the
intermediate file (Line Items file).
3. Use the new calculation field as a related field in the third file.
In the example, place the related field Part Name from the Line Items
file on a layout of the Invoices file.
Note You can also define a lookup in a field in an intermediate file
to copy data from a field in a third file, when you want the data to
remain unchanged in the intermediate file. You can then use that
field as a related field in a third file. In the example, the Unit Price
field in the Line Items file is a lookup to the Unit Price field in the
Parts file. The lookup uses the Part ID relationship.
Invoice ID
Part ID
Unit Price
Quantity
Extended Price
Part Name
Line Items
Customer ID
Agent Name
Invoice ID
Order Date
Invoices
Part ID
Unit Price
Part Name
Parts
Invoice ID
relationship
Part ID relationship
Calculated field