Table Occurrences

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.

Figure 8-9. In this version of the ER diagram, the Expenses and Line Items tables have been emphasized, along with the relationships that connect them. The only relationship between an expense (which you need to be reimbursed for) and a line item (which allows for reimbursement) is through the Jobs table and the Invoices table.

---

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:

• A new primary key (Section 7.3.2) to hook the two tables together.
• A way to represent the new relationship in your relationship graph without throwing the whole thing into disarray. That's righta new table occurrence.
• New fields on one of your layouts (the Expenses layout in this case), to use the newly related data.

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.

Figure 8-10. This revised ER diagram now includes a relationship between Expenses and Line Items. It's a one-to-one relationship because an expense is billed only once, and a line item bills only for one expense. Notice, too that you now have two relationships between Expenses and Line Itemsone direct, and one through Jobs and Invoices. FileMaker can't deal with that kind of ambiguity, which is why you create a new table occurrence to represent the relationship (Section 8.2.2.2).

 

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.

8.2.2.1. 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 8.2.2.2 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 8.2.2.3.

---

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.)

 

8.2.2.2. 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 8.2.2.3 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.

1. On the Relationships tab of the Define Database dialog box, click the Add Table Occurrence button.

   You see the Specify Table dialog box (shown in Figure 8-12).

2. Select the Line Items table from the list, and then, in the Name of Table Occurrence box, enter Expenses Line Items.

   ---

   Tip: If you don't see the Line Item ID field, it may be because your table occurrences (TOs) are collapsed and they show only fields used in relationships. Click the little button in the upper-right corner of the TO to expand it. Since the TO cycles through three states, you might have to click twice before you see all the fields.

   ---

   

   Figure 8-11. This diagram uses a new table occurrence to achieve the new relationship between Expenses and Line Items, while you avoid the problem of ambiguity shown in Figure 8-10. This shows you how you set up this relationship in FileMaker's relationship graph. Although you have only one Line Items table, you tell FileMaker it has two meanings: a line item on an invoice, and a line item that bills for an expense.

    

   

   Figure 8-12. Clicking the Add Table Occurrence button (shown back in Figure 8-2) opens the Specify Table box, so you can tell FileMaker which table to make a new occurrence of. You also get to give the new occurrence a name. You can then drag fields to the new TO to create relationships as usual.

    
3. You want a descriptive name so you can pick it out of a lineup later. When you click OK, FileMaker adds the new occurrence to the graph and selects it for you.
4. Drag the Expenses::Line Item ID field to the Expenses Line Items::Line Item ID field.

   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 create a new occurrence of the Line Items table, you're giving that table a new meaning in the relational structure of your database. You can access this new TOExpense Line Itemsfrom any table occurrence in the system. In particular, you can add Expense Line Item details to the Expenses layout. (You can't put fields from Expenses on the Line Items layout because that layout is attached to the Line Items table occurrence, not the Expense Line Items occurrence.)
• If you decide to create a new occurrence of the Expenses table instead, then the opposite is true: You can't view Line Item information from the Expenses layout, but you can view Expense information from the Line Item layout. In this case you've given Expenses a new meaning (Billed Expenses, perhaps).

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.

8.2.2.3. 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.

---

WORKAROUND WORKSHOP
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:

1. Drag a new field onto the layout. When the Specify Field dialog box appears, in the pop-up menu at the top, choose Expense Line Items.

   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).

2. Choose the Invoice ID field from the list, and then click OK.

   This field lets you view the Invoice ID on which this expense was billed.

3. Add the Expenses::Line Item ID field to the layout as well.

   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.)