In order to show work orders for each customer, you need to make a connection between them.
In FileMaker, this sort of connection is called a relationship.
Let's take a moment to explore a simple relationship example before returning to the Landscaping solution.
Learn about how data is connected using relationships.
There are no steps in this section — just a new file to explore, the "Mountains" database in your Example Data.
This database has two tables: Continents and Mountains.
- The Continents table contains two fields, Continent and Highest Point.
- The Mountains table contains three fields, Continent, Mountain, and Height.
We have connected the two tables with a relationship.
Relationships match a record in one table with one or more records in a second table. The match can also be made in the other direction, from the second table to the first.
This is how a relationship is represented in Filemaker Pro (we'll come back to this in the next section):
This particular relationship makes a match when the Continent is the same in both tables.
The example data
Now let’s look at what this relationship means in terms of data.
We've entered data into these tables to represent various continents and some of their most famous mountains.
Here is the data in the Continents table:
And here is the data in the Mountains table:
Two points of view
By connecting these two tables through a relationship, we can view data in the Mountains table at the same time as we are looking at data in the Continents table. The relationship can be viewed from two points of view:
- Looking at Mountains from the point of view of a Continent
- Looking at Continents from the point of view of a Mountain
The screenshots below show how the connected (or as FileMaker Pro says, related) data is displayed from the point of view of the Continents table for the first three records.
You can see that there is more than one mountain for each continent. The way to display these related records is through a special object called a portal — which is like a list inside its own scrolling box.
From the point of view of Continent record #1:
From the point of view of Continent record #2:
From the point of view of Continent record #3:
In the case of the Mountains table, there is only one continent per mountain, so we don't need a portal and can show these records as a list.
From the point of view of each Mountain record (in a list):
By viewing data through the relationship, you can learn more about the continent for any given mountain — in this case, the highest point on the continent. This allows you to compare whether the mountain is the highest on the continent or not.
If we added more fields to the Continents table, all of them would be accessible through the relationship.
Primary and Foreign Keys
The field used for making the match — in this case "Continent" — is called the key to the relationship. When it uniquely identifies records in a table (as in the Continents table), it is called a primary key. When it is not unique (as in the Mountains table), it is called a foreign key.
The “Continent” field describes something about the continent, in this case it's name. When a key is descriptive in this way, it is called a natural key. When it is an arbitrary ID like a serial number (something with no real-life or business meaning), it is called a surrogate key.
Natural keys can be convenient and easy to understand. But they also can get you into trouble.
For example, what if you were tracking Authors and Books instead of Continents and Mountains -- and your Authors table included two different people with the same name? Those two authors cannot be uniquely identified using the “Author Name” field, so we could no longer count on each book having only one author. In fact, we couldn't be certain which of the two authors we were talking about. For that reason, it's often better to use a serial number or other type of surrogate key.
The Landscaping example uses the Customer No field to join Customers and Work Orders. It could have used the Customer Name, but since there potentially could be two customers with the same name, it's a better choice to use a unique serial number like the Customer No field.