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.


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.

Part I: Getting Started with FileMaker 8

FileMaker Overview

Using FileMaker Pro

Defining and Working with Fields

Working with Layouts

Part II: Developing Solutions with FileMaker

Relational Database Design

Working with Multiple Tables

Working with Relationships

Getting Started with Calculations

Getting Started with Scripting

Getting Started with Reporting

Part III: Developer Techniques

Developing for Multiuser Deployment

Implementing Security

Advanced Interface Techniques

Advanced Calculation Techniques

Advanced Scripting Techniques

Advanced Portal Techniques

Debugging and Troubleshooting

Converting Systems from Previous Versions of FileMaker Pro

Part IV: Data Integration and Publishing

Importing Data into FileMaker Pro

Exporting Data from FileMaker

Instant Web Publishing

FileMaker and Web Services

Custom Web Publishing

Part V: Deploying a FileMaker Solution

Deploying and Extending FileMaker

FileMaker Server and Server Advanced

FileMaker Mobile

Documenting Your FileMaker Solutions

Using FileMaker 8
Special Edition Using FileMaker 8
ISBN: 0789735121
EAN: 2147483647
Year: 2007
Pages: 296 © 2008-2020.
If you may any questions please contact us: