Advanced Relationships

In this chapter and the previous one, you've only created simple relationships with one rule and keys that match exactly, but you can create relationships that go far beyond those basic concepts. And even if you don't have a very complicated database, you need to know how these advanced relationships work.

For example, you might have a table of Student Test Scores that contains all the tests for every kid in your school. You can view a classroom's scores on a specific test by making a relationship that matches two key fields: the Classroom ID and the Test ID. You can also find all the kids who're failing by matching every test score that's less than 70 points. Sure, you can perform a search in your Test Scores table and add some summary fields to a report to do the same tasks, but you're limited by the fact that the subsummary parts and their fields don't show up in Browse mode, and by the need to perform the search again every time you want to see the data. With the techniques below, you can make this data available with just the switch of a layout or a Go to Related Record command (Section 8.1.3).

8.7.1. Relationships with More than One Criterion

First, you can assign more than one rule to a relationship. For instance, you already added a relationship between Customers and People that matches based on state. Suppose you want to be more restrictive: You want to show only people who live in the same city and state.

It's easier than it sounds:

1. In the Customers database, go the Define Database window's Relationships tab (Section 7.3.3), and select the relationship line between Customers and People.

   The easiest way to select a relationship is to click the "=" box in the middle of the line (it provides a nice big target). When you select the relationship, the line gets slightly thicker and the box highlights.

2. Click the Edit Relationship button (Section 8.1.2).

   You can also just double-click the relationship. Either way, you see the Edit Relationship dialog box, as shown in Figure 8-25.

   Figure 8-25. Last time you saw this window (Section 8.4.3 ), you were focused on the bottom third. Now it's time to look up. Most of this window is devoted to building the rules that define this relationship. You get to pick which fields to use and how they should match. And you can add as many matching criteria as you want.

    
3. In the left table's field list, choose City.

   The City field highlights.

4. In the right table's field list, choose City.

   You've now described a new criterion for your rule: Customers::City = Other Leads::City

   ---

   Note: The Edit Relationship dialog box tries to mimic the layout you've created on your Relationships Graph. That is, if your Customers TO is to the left of your People TO in the graph, that's how they'll appear in the dialog box. If two TOs are on top of one another (vertically aligned), then the table on top appears at the left of the dialog box. The order shown in these lists has no effect on how the TOs relate to one another, but if you're having trouble reading this dialog box, close it, rearrange your graph, and reopen it.

   ---

5. Click the Add button.

   FileMaker adds this criterion to the criteria list. You now have two criteria for this relationship:

   Customers::State = Other Leads::State AND Customers::City = Other Leads::City  

6. Click OK.

   FileMaker uncovers the relationship graph.

You've just defined a relationship with two criteria, sometimes called a multi-key relationship, for the obvious reason that it uses two key fields. In order for two records to relate, they must meet both criteria. In other words, a person must be in the same city and the same state as a customer to show up in his portal. Figure 8-26 shows the new relationship in the graph.

If you test your Other Leads portal now, it shows only people living in the customer's city.

8.7.2. Other Relationship Operators

In addition to adding more rules to a relationship definition (by defining multiple criteria), you can also base relationships on different kinds of rules. Each relationship in your ER diagram for your database mandates that the fields on either side match exactly. This type of relationship is called an equijoin, in honor of the = sign that defines it. Sometimes, though, you want to relate records without an exact match. When you make a non-equijoin relationship (don't you just love technical terms?), you use FileMaker's other comparative operators:

• Equals (=). The keys on both sides of the relationship match exactly.
• Not Equals ( ).All records but those with matching keys relate to one another.
• Less Than (<). Keys in the table on the left side of the dialog box must be less than keys in the table on the right side.
• Less Than or Equal To (). Keys in the table on the left side of the dialog box must be less than or equal to the keys on the right side of the table.
• Greater Than (>). Keys in the table on the left side of the dialog box must be greater than keys in the table on the right side.
• Greater Than or Equal To (). Keys in the table on the left side of the dialog box must be greater than or equal to the keys on the right side of the table.
• Cartesian Join (x). All records in the table on the left are related to all records in the table on the right, regardless of the value in their key fields.

As you work with your database, you might decide you need a quicker way to find invoices. You often want to see every invoice in a specified date range, and sometimes only those that are above a certain total amount due. Using relationships, you can create a new layout that makes it easy for you to see the invoices you want. You enter a start date, end date, and minimum amount into global fields. A portal displays every matching invoice.

8.7.3. Creating the Invoice Finder

To set up your new relationship, you need three new global fields (Section 8.4). Since the global fields aren't associated with any particular table, you can create a new table to hold them. You also need a new relationship that matches fields in the Invoices table with these global fields. This job is the sort that benefits from its own table occurrence group, as you saw on Section 8.4.2. Call this group Invoice Finder.

To get the portal to show the right invoices, you need a relationship that uses your new global fields, and it'll have slightly more complicated rules than you've seen before:

Invoice Finder: Globals::Start Date images/U2264.jpg border=0> Invoice Finder: Invoices::Date AND Invoice Finder: Globals::End Date Invoice Finder: Invoices::Date AND Invoice Finder: Globals::Minimum Amount images/U2264.jpg border=0> Invoice Finder: Invoices::Amount Due

These rules say that an invoice should match if its date is on or after ( ) the global start date, on or before () the global end date, and its amount due is Below are the steps for creating a new table, defining some global fields in that new table and then using your creations in a new table occurrence group.

You've done most of this stuff before in other tutorials, so although there are a lot of steps, they should all be pretty familiar already:

1. In the Customers database, go to the Define Database window's Tables tab. Create a new table called Invoice Finder.

   FileMaker adds the new table to the table list.

2. Click the Fields tab. Then, in the Field name box, enter Start Date. Make the field a Date type, and then click Create.

   This field holds the starting date you're searching for.

3. Click the Options button. When the Field Options dialog box appears, click the Storage tab and turn on the "Use global storage" checkbox. Click the OK button.

   Since this field isn't holding data about an entity, it makes sense to use a global.

4. Repeat steps 23 to create two more global fields. One, a global date field, should be called End Date. The second is a global number field called Minimum Amount.

   You've finished creating your three global fields.

5. Switch to the Relationships tab. Double-click the Invoice Finder occurrence FileMaker made for you and change its name to Invoice Finder: Globals.

   Now this occurrence has the prefix you want for the new table occurrence group.

6. Add a new occurrence of the Invoices table called Invoice Finder: Invoices, and a new occurrence of the Jobs table called Invoice Finder: Jobs.

   Since your portal shows invoices and the job associated with each invoice, you need these tables in your group as well.

7. Drag the Invoice Finder: Invoices::Job ID field onto the Invoice Finder: Jobs:: Job ID field to create a relationship.

   You've just told FileMaker that the Job ID field relates these two tables. Now you'll use that field to complete the relationship.

8. Click the New Relationship button (Figure 8-27) to open the Edit Relationship dialog box. From the left table pop-up menu, choose Invoice Finder: Globals. Then, from the right table pop-up menu, choose Invoice Finder: Invoices.

   Figure 8-27. You haven't used the Add/Edit Relationship button before because you've always used the drag-and-drop method to define relationships. But if the relationship you're creating is more complex, this button skips the drag altogether and goes straight to the Edit Relationship dialog box, so you don't have to delete or edit the equijoin relationship that FileMaker creates when you drag.

    

   The left list is filled with the global fields you created in the earlier steps; the right list shows the fields from the Invoice Finder: Invoices table occurrence. Next, you select the two key fields and tell FileMaker how to relate these TOs together.

9. From the left table field list, choose Start Date. From the Operator pop-up menu (it's between the two field lists), choose (Mac OS X) or <= (Windows).

   less than or equal to the field you select on the right side.

10. From the right table field list, choose Date, and then click Add.

    The Date field tells the relationship what value to compare to the Start Date in the Globals table. In other words, you want the relationship to match only when the Start Date is before the Invoice Date.

    Later on, you'll create a portal that uses this relationship to display only certain records. The criteria you chose tell the portal to show only Invoice records that are dated on or after the date entered in the global Start Date field.

11. Repeat the steps, but this time, from the left table field list, choose the End Date field. From the right table field list, choose the Date field, and from the Operator pop-up menu, the or >= option.

    Repeat steps 9 through 10 again, choosing Minimum Amount from the left table, Amount Due from the right table, and or <= for the operator. Click OK when youre done with the third criteria.

    The third part of this complex relationship system lets you type a minimum value in a global field to further restrict the display of related records in the portal.

    FileMaker returns you to the graph. It should look like Figure 8-28.

To test your new relationship, create a new layout and attach it to the Invoice Finder: Globals table occurrence. Add the three global fields and a portal based on the Invoice Finder: Invoices relationship. As you put values in the three global fields, the portal updates to show the matching invoices. You can see it in action in Figure 8-29.

Note: The Invoice Finder table has no records. Since it has only global fields, you'd think this lack of records wouldn't be a problem, but it is. Portals don't work properly when you have no records. To clear this problem up, just create a new empty record.

So what has all this work gotten you? As a designer, this technique of using global fields in a complex relationship to populate a portal has improved your understanding of relationships and tested your skill in applying that knowledge. People who use your database benefit most practically. Now they can see a group of invoices at a glance, without having to do a search, then go to a list view of their data. In the next section, you'll learn some techniques that you can apply to make this layout even handier. You'll learn how to create a calculation field that summarizes the value of the invoices in your portal (Section 9.3).