Advanced Relationship Techniques - FileMaker Pro 13: The Missing Manual - page 718

Diagram ) , you'll also want to create relationships and portals that show specific related re-
cords. Then, when you add buttons or scripts that use a Go to Related Record step to those
layouts, users won't need to enter Find mode as often to see the data they need.
Self-Join Relationships
The Invoices layout of the Invoice.fmp12 database has a Line Items portal and a Payments
portal. You can get a better picture of a Job's total costs by creating a third portal that shows
all the other invoices related to the current invoice's job. Sure, there's already such a portal
on the Job layout, but if you're researching the way a particular job was billed, you can see
that data on each invoice, plus a button on the new portal can move you through a Job's in-
voice records without needing to go back to the Jobs layout or do a find on the Invoice lay-
out.
You'll need a special type of relationship, called a self-join , to make this portal. A self-join
relationship is one in which a table is related to itself instead of to another table. Since you
want the portal to show all invoices from the same job, you'll use the Job ID field as the key
for the self-join relationship. Follow these steps to create the self-join you need to show all
invoices for the current Invoice's job:
1. Choose File → Manage Database and then click the Relationships tab .
The Relationships graph appears.
2. Click the Invoices TO to select it and then click the Duplicate button .
The Duplicate button has two plus signs on it. A copy of the Invoices TO, named In-
voices 2, appears on the graph.
3. Drag the Invoices 2 TO under the Invoice Payments TO and then double-click
Invoices 2 .
The Specify Table dialog box appears. Because you duplicated an Invoice TO, this
new TO has the same data source as the original. It's from the Invoices table.
4. Change the TO's name to Invoices_currentJob and then click OK .
As with all things FileMaker, use a descriptive name.
5. Click the Expand button on the Invoices_currentJob TO and then drag the In-
voices::JobID field to the Invoices_currentJob::JobID field .
You're creating a relationship between invoices that share the same Job ID. Compare
your graph with Figure 14-1 .
6. Click the OK button .