FileMaker Extra: Building a Three-Way Join

FileMaker Extra Building a Three Way Join

In the "FileMaker Extra" section at the end of Chapter 5, we sketched out the ERD for a many-to-many-to-many relationship among truckers, trucks, and truck routes. We follow up on that discussion here and show you how you might build such a thing in FileMaker.

Structurally, it's not too complexwe already worked out the relationships at the end of Chapter 5. You need a four-entity system: Trucker, Truck, Route, and the central three-way join entity, called in this case RouteAssignment. The Relationships Graph for such a structure might look as shown in Figure 6.24.

Figure 6.24. The FileMaker Relationships Graph showing table occurrences and relationships for a three-way join.

What about data entry? This is a bit more challenging. In theory, you could have a portal in any of the parent tables that would enable you to create RouteAssignment records. If the portal were in the Trucker table, you'd be entering a Truck and a Route on each portal row to make the assignment. If the portal were in the Truck Table, you'd enter a Trucker and a Route on each row. If the portal were in the Route table, you'd enter a Truck and a Trucker on each row.

Well, these portals are probably useful for data viewing. Certainly, if I'm on a trucker record, I'd like to see a portal with all that trucker's route assignments. Same for a truck: I'd like to see a list of all the routes over which the truck has been driven in its service lifetime. But none of these is obviously the right place from which to do data entry.

In a case like this, it may be best to set aside the portal-based method for entering data in a related field and allow the user to create the assignment records directly. You still want to use value lists to assist data entry; if it's hard to remember one set of keys, it's surely impossible to remember three sets! So you'd define three value lists, one based on each table, with the first field in the value list being the primary key for the table, and the second field being some nice identifying field from the rest of the table. Such a set of value lists is shown in Figure 6.25.

Figure 6.25. These value lists speed data entry into the join table in the midst of the three-way join.

Tip

This last point raises a difficulty with two-field value lists. You're limited to a total of two fields, so if the first field is a key field of some kind, that leaves you a total of one field of identifying data. Sometimes that's not enough. For the truckers, last name may be enough for the second field. For trucks, the license plate number might suffice. But for routes, we'd really like to see both the start and the end city of the route. The only way to do this is to create a calculation field to display the start and end nicelysuch as Poughkeepsie-Hopalong. You can then use this calculation as the second field in the value list.

After you have the three value lists, you can create a layout, based on the RouteAssignment table, where users can easily create a new route by using the value lists to populate the three key fields. You could set things up so that the data entry would take place in Table view, as shown in Figure 6.26.

Figure 6.26. You can use value lists, as well as the display of appropriate related fields, to create a usable data-entry interface for this complex three-way join setup.

Notice that related fields have been added from each of the three main tables to make the display more intelligible.

Although FileMaker excels at modeling and implementing the standard one-to-many and many-to-many relationships, it's equally capable of working with more esoteric structures as well. In such cases you're likely to find that modeling and building the relationships is relatively straightforward, whereas designing the data-entry interface takes some more thought. The techniques described in this section may be instructive, or you may find you need a different solution, depending on the nature of the problem and the needs of your users.