FileMaker Pro 9: The Missing Manual

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 should 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 sub summary 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 (Go to Related Record).

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 Mars Investigations ...