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:
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.
You can also just double-click the relationship. Either way, you see the Edit Relationship dialog box, as shown in Figure 8-25.
The City field highlights.
You've now described a new criterion for your rule: Customers::City = Other Leads::City
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
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:
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.
|FREQUENTLY ASKED QUESTIONS
Globals and Relationships
I don't get it. I thought relationships were supposed to hook different records together, but a global field isn't associated with any record at all. How come you keep using global fields to create relationships?
When you set out to design your database in Chapter 7, you learned how to organize your information into tables and construct the relationships that make them work together. But relationships can do a whole lot more.
As the Invoice Finder example (Section 8.7.3) and the Expense Assignment example (Section 8.4.2) illustrate, relationships can be part of an overall interface (or layout) that performs a specific task. For example, when you use a global field in a relationship, it works just fine. Put an ID in the global and FileMaker makes a match one or more records on the other side.
This kind of relationship doesn't define a connection between recordsrather, it just gives you temporary access to related records.
Since global fields can't be indexed, the relationship doesn't work in the other direction. As the picture here shows, FileMaker doesn't connect the relationship line in the graph directly to the global field. This picture is a visual cue to let you know this relationship works only one way.
One last point: If you want to use a global field from another table, you don't need a relationship at all. Since global fields aren't associated with any record, you can view and modify them from anywhere. For example, you can put the Global Invoice ID field on any layout in the entire database, and it works just fine.
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:
FileMaker adds the new table to the table list.
This field holds the starting date you're searching for.
Since this field isn't holding data about an entity, it makes sense to use a global.
You've finished creating your three global fields.
Now this occurrence has the prefix you want for the new table occurrence group.
Since your portal shows invoices and the job associated with each invoice, you need these tables in your group as well.
You've just told FileMaker that the Job ID field relates these two tables. Now you'll use that field to complete the relationship.
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.
less than or equal to the field you select on the right side.
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.
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).
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