Creating a Many-to-Many Relationship

The preceding sections introduced you to most of FileMaker's fundamental tools for working with multiple related tables. Now it's time to extend those concepts and see how to use them to create a many-to-many relationship structure.

Building the Structure

Let's say that you've been asked to create a database for a town militia (assuming that databases were prevalent in Colonial America). You need to keep track of militia members in their own right, and you also need to be able to assign them to different guard shifts. An ERD for the proposed system is shown in Figure 6.17. On the one hand there are guard shifts, each of which can be staffed by several militia members. On the other hand, there are militia members, each of whom can work many shifts. This is a classic many-to-many relationship. You'll recall from Chapter 5 that such relationships are resolved with an intermediate join entity. In this case, we'll call the join entity a "shift assignment." Each shift assignment records the posting of a single person to a single shift.

Figure 6.17. An ERD for a system that tracks guard shifts and shift assignments.

As before, we map the abstract entities directly onto FileMaker tables. This produces a three-table system. The appropriate key structure is essential: primary keys in the Shift and MilitiaMember tables, and two foreign keys (_kf_ShiftID and _kf_MilitiaMemberID) in the middle ShiftAssignment table.

We're going to assume that you're familiar enough with the field and table definition process by now that we can skip over the details. We'll take you straight to the Relationships Graph. Figure 6.18 shows the Graph after we've created all three tables and established the two relationships into the ShiftAssignment table.

Figure 6.18. The FileMaker Relationships Graph with three related table occurrences representing a many-to-many relationship.

Creating a Data Entry Interface

As before, the structural part is not so hard. But it takes some work to make data entry easy. Creating and editing militia members or guard shifts is pretty straightforward: The user navigates to either the Shift layout or the MilitiaMember layout and adds, edits, and deletes records there.

But what about shift assignments? As with the earlier example of town officers, shift assignments are a type of child record you want to create in association with a parent record of some kind. In the town/officer example, the data-entry interface we discussed enables users to add officers to a particular selected town. In the guard system under discussion, the intent is to be able to choose a shift, view that shift record, and assign militia members to that specific shift. In a similar vein, users should also be able to choose a militia member, view his record, and see on the same screen a list of all of his current shift assignments.

Let's assume that you've created records for a few militia members and a few shifts already. You'd like to edit the Shift layout so as to be able to view and create shift assignments from the Shift layout itself. You can add a portal to the layout. It will be a portal showing recordsnot from the MilitiaMember table, but from the ShiftAssignment table. We want users to be able to add multiple assignments to this shift and specify a militia member for each assignment.

Recall the data structure from Figure 6.17. There's no way to record a member's name in the shift assignment tableonly his primary key. That's not a very friendly data-entry mechanism. The watch commander is more likely to know his staff by name, not by database ID. We can fall back on a familiar FileMaker tool for this data entry task: the value list.

Before doing this, by the way, you're going to want to make sure that the relationship between Shift and ShiftAssignment is configured to allow creation of related ShiftAssignment records. This is necessary if the portal is to be used as a data-entry tool.

Using a Value List for Data Entry

Even though users know militia members by name, the key structure of the data is rigid (as it should be). What the user needs to enter into each shift assignment record is a member's primary key, not his name. But it's not realistic to memorize member IDs. You need some kind of data-entry mechanism that will give a hint as to which member ID goes with which member.

FileMaker's value lists are the right tool for this. Chapter 4, "Working with Layouts," discussed creating value lists from a hand-entered list of custom values. In the current example, to speed the assignment of members to shifts, what you need is a way to build a value list dynamically, based on the contents of the MilitiaMember table, so that the value list has one entry for each member in the database. And each entry should show two pieces of data: not only the member ID, but also the member's name.

Defining a Value List to Draw Data from a Table

Choose File, Define, Value Lists, and click New to create a new value list. In the Edit Value List dialog, rather than choosing the third radio button (Use Custom Values), choose the first one, Use Values from Field, as shown in Figure 6.19.

Figure 6.19. This is the first of two dialogs you use to create a new value list.

When you do this you'll get a second dialog box. Go to the menu that says Use Values from First Field. This lets you pick first a table, and then a field from that table. Choose __kp_MilitiaMemberID from the field list in the first column. In the second column, select the check box that says Also Display Values from Second Field, and then choose LastName from the list of fields in that table. At the lower left, select the Include All Values radio button. You can also select the radio button at the lower right that instructs FileMaker to sort the value list by the second field. This ensures that the list will be sorted in order of last name, rather than in order of the member ID.

This second dialog box is shown in Figure 6.20.

Figure 6.20. The second value list dialog allows you to specify an additional field to display in the value list.

With the value list created, it's time to return to the ShiftAssignment layout and build the data-entry portal. The first step is, of course, to use the Portal tool to draw the portal. Choose whatever features you like, such as vertical scrollbar or row striping. When you're finished, FileMaker prompts you to choose fields to put into the portal.

Figure 6.21 shows the Add Fields to Portal dialog. Notice that the table selection menu at the left lets you choose fields from either of two tables: You can choose fields from the intermediate ShiftAssignment table or from the more "distant" MilitiaMember table itself.

Figure 6.21. In FileMaker 8, a portal can display records from tables that are more than one "hop" distant from the current table.

In this case, you need to do both. Choose the _kf_MilitiaMemberID field from ShiftAssignment. You need to fill that field in with a member ID, so it needs to be in the portal. But when you're viewing the portal, you're really more interested in the member's name. Well, that field isn't in the join table, so you just need to reach farther down to get to it. Switch the Available Fields menu to show fields from the MilitiaMember table and select LastName.

Note

If you're used to older versions of FileMaker, you'll recognize that this capability to reach more than one relationship deep was a huge advance in FileMaker 7. Making this happen was possible in earlier versions, but did require setting up additional calculations in the join table to pipeline data through to the portal. In FileMaker 7 and 8, it's possible to look several levels deep, if necessary, to bring back related information.

With the portal created on the layout, you're almost finished. You still need to apply the value list to aid in record creation. In Layout mode, select the _kf_MilitiaMemberID field in the portal. Choose Format, Field Format. In the Field Format dialog box, choose Format Field as Pop-Up List and choose Display Values from Members because Members is the name of the member value list you created.

Back in Browse mode, if you click into the ID field, you should see a neatly formatted list of possible members to add to the shift. Figure 6.22 illustrates the behavior.

Figure 6.22. Using value lists built on table data is a powerful way to aid data entry in related tables.

Caution

As before, you should use the Field Behavior dialog box to prevent users from entering into that LastName field while in Browse mode. If they edit the name there, the name on the original MilitiaMember table is changed.