In Chapter 7, you learned that a relationship graph doesn't really depict tablesit shows table occurrences. They're really just graphical representations of the underlying table. Because these occurrences are representations, you can make new occurrences of a table without duplicating it (and all its data). FileMaker's not trying to confuse you; it's actually helping you relate to the same table in different ways.
|POWER USERS' CLINIC
GTRR On Steroids
With the "Show only related records" option, you can use Go to Related Record to display a found set of records (from a related table) in a portal. But what if you want to something really cool and complicated, like find a couple of customers, who don't have anything in common other than not having paid their invoices yet, and somehow see their customer records in a set without performing a complicated search?
GTRR shows its juice in a new-to-FileMaker 8 feature called Enhanced Go to Related Record (eGTRR). Here's how it works: Make a button on your Invoice layout and give it a GTRR step that goes to the Customers table occurrence using the Customers layout.
Then, in the Options window, select "Show only related records" and "Match all records in found set" (the second option is the enhanced part). Do a regular find to show Invoices from just Daniel and Alithey're always late payers. You could just omit Mr. Miyagi, who pays as soon as he gets his invoice, thank you very much. Click the eGTRR button to see a new found set in the Customer table that shows only Daniel and Ali's records.
Apply eGTTR liberally throughout your database, and your users won't need to do as many complicated searches to find the data they're looking for.
When you create a very simple database, you have only one Table Occurrence on the graph for each table, so it's tempting to think of them as the tables themselves. But as you add new and more powerful features to your databasesand the relationships to make them worksooner or later you need to graph the same table multiple times so you can keep everything straight.
8.2.1. Understanding Table Occurrences
There are few databases that can manage all their tasks without multiple occurrences of the same table. Your Customers database is no exception. Here's an example: Your database has Expenses to record what you buy to service your customers. It also has Line Items to record what you charge your customers for. Right now these are connected only by way of jobs. Figure 8-9 illustrates how your tables are currently related.
You can do better than connecting expenses and line items via jobs, however. When you do connect them this way, it means you don't know which line item is actually billing for a particular expenseso you can't be sure you've actually billed for that expense yet. In other words, when you look at a line item, you can't tell which expense it's billing for.
Of course you can type this information into the Line Items::Description field, but this does nothing to help FileMaker know how these are connected. What you really need is a whole new relationshipone that directly connects Expenses and Line Items. This new relationship lets you use Go to Related Record commands to toggle between expenses and invoices, for example.
Tip: Later, when you learn more about calculations (Part 4) and scripting (Part 5), you can use this relationship to have FileMaker tell you exactly what has and hasn't been billed.
One-to-one relationships are fairly rare birds. One example you've seen in this book is a table of photographs and a table of their captions, with a one-to-one relationship between each. Each image file had one corresponding description. This new one-to-one relationshipexpenses to line itemsis even rarer, in that it connects two completely different types of data. It's also unusual in that it's not integral to your database's structure. Unlike the other relationships in this database, you could delete it and your system would work just fine. This relationship exists for one purpose only: to give your database a new level of ability.
As Figure 8-10 explains, however, when you add that relationship to the existing graph, you create ambiguity in FileMaker's digital mind. You can relate Line Items to the Expenses table, but to avoid interacting with existing relationships to Line Items, you need to create a new occurrence of that table.
To create this relationship and really make it work, you need three things:
To put these new database elements into play, you need to acquire some new skills and learn new twists on some old ones. The next section covers the entire process.
8.2.2. Relationships with Table Occurrences
FileMaker doesn't require you to follow a set order when you create new table occurrences, relationships, and layouts. Sometimes you create a new layout, thinking it's just for displaying a set of data and only later realize you need a new relationshipand a new table occurrenceto make it work. Fortunately, FileMaker makes it easy to switch modes so that you move freely from one part of the program to another creating and arranging various bits and pieces to make your new features work. In this section, you'll create a new key field and a new table occurrence, and then you'll add fields to a layout so you can assign invoice items to the proper expense item. The following sections take you through the process in one possible, and logical, order.
126.96.36.199. Adding a new key field to a table
Before you can hook up table occurrences in a new way, you've got to make sure both tables have the proper key field for the new relationship. In this case, the Expenses table needs a Line Item ID. (See the box on Section 188.8.131.52 for more detail.)
In the Customers database, choose File Define Database and switch to the Fields tab and the Expenses table. Create a new text field called Line Item ID, and click OK when youre done. This new field is how you relate the Expenses table to Line Items.
Note: You may be tempted to create your new relationship now by dragging the new Line Item ID field to the Expenses table. In fact, you may have already tried thatand gotten a confusing error message. To find your way out of that conundrum, see the box on Section 184.108.40.206.
|FREQUENTLY ASKED QUESTIONS
Putting New Keys in New Key Fields
I need to add a new key field to a table, but it's already got a lot of records in it. Since my serial numbers are created only when I create new records, am I stuck?
No, you're not. Lots of people need to "retrofit" key fields and add serial numbers after the data's already in a table. First, create a new field and give it the Auto-Enter serial number option. Put it on a layout that's tied to one of that table's table occurrences. If you have only one table occurrence for that table, so much the betteryou don't have to make a choice.
Choose Records Show All Records to make sure that all your records get a value in the key field. Then click in your new key field and choose Records images/U2192.jpg border=0> Replace Field Contents. Select the "Replace with serial numbers option. FileMaker figures that you'll have to do this stuff pretty regularly when you're designing, so the values in the "Initial Value" and "Increment by" fields are already set correctly for this task. Select "Update serial number in Entry Options?" then click the Replace button. FileMaker does the hard work of giving each record a unique serial number. It even updates the serial number so that the next new record you create has its own serial number. (See Section 7.3.2 for the full story on creating key fields.)
220.127.116.11. Adding a table occurrence
You need to make a new relationship between the Expenses TO and the Line Items TO using your new Expenses::Line Items ID key field. Since you want to access Line Item details while you're on the Expenses layout, you create a new TO for the Line Item table. In general, it's the information that you're using in a new way (the Line Items data in this case) that gets the new table occurrence. The Expenses table is fine as it is. (See the box on Section 18.104.22.168 for more detail on how to make that decision.)
Figure 8-11 shows a picture of what that relationship might look like with the new Line Items TO. With the new graph in mind, here's how to create that additional TO in your Customers database.
You see the Specify Table dialog box (shown in Figure 8-12).
FileMaker creates the relationship.
|UP TO SPEED
Deciding Which Table Needs a New Occurrence
Once you know that you need a new table occurrence, as in the ExpensesLine Items example, you immediately face another challenge: Of which table do you make a new occurrence? To answer that question, ask yourself this one: Which table has the information I need? Here are the three possible scenarios: See which one you think makes the most sense.
If you want, you can create new occurrences of both tables. If you do, you can see Expense information from a Line Item and Line Item information from an Expense. But unless you plan to use your data that way, you're only creating more work for yourself.
Now that you have both relationships defined, you can switch the table occurrences back to their just-the-key-fields modes if you want, and arrange the graph however it suits you. When you're done, click OK to dismiss the dialog box. Now it's time to add fields from your new table occurrence to the Expenses layout. If no fields on at least one layout access the table occurrence you just created, it's not going to do you a bit of good.
22.214.171.124. Adding fields for new table occurrences
To put your new relationship to work, switch to Layout mode and go to the Expenses layout. As discussed on Section 8.2.2, you want to add a couple of fields that let you view invoice line items and assign them to the corresponding expenses. That's the whole point of all the trouble you've gone to these last four pages.
Tip: Adding the fields using the Expense Line Items is like adding any other fields, but make sure you're using the Expense Line Items table occurrence, not the original Line Items.
When Dragging Doesn't Do It
When you first learned to create relationships, it was by dragging fields on the relationships graph (Section 7.3.3). Since there's a clear path from the Expenses table to the Line Items table, you may be tempted to drag the Line Item ID field to connect them. You may have already tried itand gotten the nasty message shown here.
FileMaker's not trying to be difficult. It's just letting you know that what you're trying to do doesn't work, and even going out of its way to suggest a solution. It's offering to create a new Line Items occurrence right now. FileMaker makes this choice because you dragged from Expenses to Line Items. (If you had dragged the other way, it would be suggesting a new Expenses occurrence instead.)
In the Name of Occurrence box, type Expense Line Items and click OK. Whenever you see this message box, you can click Cancel and try to figure out how your ER diagram conflicts with FileMaker's relationships graph, or just let FileMaker create the new TO. A new TO may not be what you had in mind, but you can rest assured that FileMaker's doing what it needs to do to make your desired relationship work.
FileMaker adds the new occurrencewhich you can move to a less intrusive place on the graphand creates the relationship.
Here's how to add the fields that do the trick:
The fields from the Line Items table appear (remember, this is an occurrence of the Line Items table, just like the Line Items occurrence itself).
This field lets you view the Invoice ID on which this expense was billed.
And this field lets you assign a line item from that invoice to the expense. Again, make sure you pick the Expenses::Line Item ID field, not the Line Items::Line Item ID field.
If you switch to Browse mode, you can start entering expenses. Make some expense records, and put the ID of a line item in the Line Item ID field. You see the associated invoice number through the relationship. (If you haven't created invoices yet, make a few, or else the fields can't show you any data.)
Part I: Introduction to FileMaker Pro
Your First Database
Organizing and Editing Records
Building a New Database
Part II: Layout Basics
Advanced Layouts and Reports
Part III: Multiple Tables and Relationships
Multiple Tables and Relationships
Advanced Relationship Techniques
Part IV: Calculations
Introduction to Calculations
Calculations and Data Types
Part V: Scripting
Part VI: Security and Integration
Exporting and Importing
Sharing Your Database
Part VII: Appendixes
Appendix A. Getting Help