Let's return to the goal of connecting customers and work orders in the Landscaping solution using the Customer No field.
In the customers table, every customer record has a been assigned a unique Customer No value. Every work order record has a Customer No as well but it’s not unique because each customer has ordered different kinds of work on various days:
- because Customer No = 1, the first three work orders are associated with Bryant Research Park
- because Customer No = 2, the next two work orders are associated with Jackson View Park (these are marked in red)
- because Customer No =3, the final three work orders are associated with Magnolia Springs Center
By the way, these customers actually have more work orders than are shown in the screenshots above — we chose not to display them all.
Take a moment to consider how this might translate to the data at your business. Maybe you need to track employees and billable hours, students and class enrollments, or donors and donations.
Connect Customers and Work Orders so that FileMaker Pro knows which Work Orders belong to which Customers.
- Choose the File menu > Manage > Database… ⇧⌘D or Ctrl-D
The Manage Database... dialog box appears.
- Select the Relationships tab.
Previously you learned that groups of fields are stored in tables, and the tables contain information about categories of people or things such as Customers or Work Orders.
The Relationships tab in the Manage Database dialog box adds an important new concept. Instead of working with tables directly, it uses table occurrences, which represent tables.
Table occurrences are not actually tables, though for most purposes they are almost the same thing. Think of them as a way of representing a table the way an alias represents a file.
Their sole purpose is to allow you to represent the same table more than once within your solution, which in turn allows you to connect tables to each other in more than one way. And that means more power and flexibility for your solutions.
Looking at the Relationships tab, you can see there are two boxes already created, “Customers” and “Work Orders”. These boxes are table occurrences — one representing the Customers table, and the other representing the Work Orders table:
Let’s make another digression to help clarify the concept of table occurrences:
- Try clicking on the New Table Occurrence tool in the lower left of the dialog box.
A Specify Table dialog box appears.
- Select the “Customers” table.
The Name field at the bottom of the dialog box is populated with the name “Customers 2”. You can enter any name into this field that you like — as long as no other table occurrence has the same name. Right now, that means you cannot enter “Customers” or “Work Orders”, since you have table occurrences with those names already.
- Click the OK button.
The Specify Table dialog box disappears, and a new table occurrence is created with the name “Customers 2”.
Notice that, with the exception of the name, it is identical to the “Customers” table occurrence. That’s because both of them point to the “Customers” table. As you build interfaces you could use either one.
You would choose one table occurrence over another depending on the connections you've made between it and other table occurrences. That's the whole point here: the ability to connect table occurrences to each other, and then make use of those connections.
If you wanted to, you could connect the “Customers” table occurrence to the “Customers 2” occurrence, perhaps matching the Salesperson field. Then, if you were displaying a "Customers" record where the salesperson is "Steve", you could use the "Customers 2" table occurrence to display all other customers where the salesperson is Steve as well. Pretty cool!
This is the most abstract concept you'll encounter in this training. If you're finding it hard to grasp, consider submitting your questions to the Discussions forum. You may also want to return to the Authors database file and experiment with the relationship there.
Now let’s get back to our goal of connecting “Customers” and “Work Orders” using a relationship.
To create data relationships, simply draw a connecting line between a field in one table occurrence and a field in the other table occurrence. For this example, you'll use the Customer No field.
- Click and hold the “Customer No” field name in the “Customers” table occurrence.
- Drag to the “Customer No” field name in the “Work Order” table occurrence, forming a connection.
This is how it should look when you are done:
Drawing this line represents the following: when the “Customer No” field is not empty and has the same value in both tables, a connection is made between them. You can make use of that connection by referencing the names of these two table occurrences.
To avoid making this exercise more complicated, we’ve set up these “Customer No” fields to create a perfect association between the Customer and Work Order tables. In real life, this would only be the case if they came from a well-maintained database system.
However, if you’re working with spreadsheets, you might need to match by someone’s name, their email address, or their mailing address — and that’s rarely 100% accurate. Often people hire contractors to help clean up their data to achieve a better match between tables. You may want to contact one of our partners to evaluate what your data requirements might be.
- Double-click on the “=” box joining the two table occurrences.
An Edit Relationship dialog box appears. This allows you to specify more complex conditions for when a connection should be made.
It also gives you the option of allowing new records to be created in one table while you’re looking at a connected table. In this solution, you want to add a work order while looking at a customer record. FileMaker will create the work order record automatically and populate the Customer No field for you, establishing the relationship (that is, making the connection).
- Select the Allow creation of records in this table via this relationship checkbox positioned under the “Work Orders” table occurrence.
Since this option is available for either table, be sure you’re making the change on the “Work Orders” side.
- Also select the Sort records checkbox on the “Work Orders” side.
A Sort Records dialog box appears.
- Select the “Date” field on the left.
- Click the Move button.
The “Date” field appears in the Sort Order list on the right.
- Now select the “Date” field that just appeared on the right.
- Change the sort order from Ascending order to Descending order.
Later, when work orders are displayed, this will cause the most recent ones to appear first.
- Click the OK button in the lower-right of the Sort Records dialog box.
- Click the OK button in the lower-right of the Edit Relationship dialog box.
- Click the OK button in the lower-right of the Manage Database dialog box.
Your changes have been made.
You'll be able to see the practical application of this relationship as your work on this goal continues.