Creating a Many-to-Many Relationship

 <  Day Day Up  >  

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. 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.

graphics/06fig17.gif


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 ( ShiftID and MilitiaMemberID ) in the middle ShiftAssignment table.

For additional discussion of the key structure of join tables, see "Many-to-Many Relationships: Solving the Puzzle," p. 140 .


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.
graphics/06fig18.jpg

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 either to 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 that 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 her record, and see on the same screen a list of all of her 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'll be a portal showing records ” not 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 table ”only her 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 her 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, "FileMaker Fundamentals: 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 F ile, D efine, V alue Lists, and create a new value list. In the Edit Value List dialog, rather than choose the third radio button (Custom), 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.

graphics/06fig19.jpg


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 MilitiaMemberID from the field list in the first column. In the second column, choose the check box that says Also Display Values from Second Field, then choose LastName from the list of fields in that table. At the bottom left, choose the Include All Values radio button. You can also choose the button at the bottom right that instructs FileMaker to sort the value list by the second field. This assures 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, allowing you to specify an additional field to display in the value list.

graphics/06fig20.jpg


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 scroll bar 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 different tables: You can choose fields from the intermediate ShiftAssignment table or from the more " distant " MilitiaMember table itself.

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

graphics/06fig21.jpg


In this case, you need to do both. Choose the 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

graphics/new_icon.jpg

If you're used to previous versions of FileMaker, you'll recognize that this ability to reach more than one relationship "deep" is a huge advance. Making this happen was possible in previous versions, but did require setting up additional calculations in the join table to "pipeline" data through to the portal. In FileMaker 7, 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 MilitiaMemberID field in the portal. Choose Forma t , Field Fo r mat. In the Field Format dialog box, choose Format Field as Pop-up List and choose Display Values from Members, since "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.

graphics/06fig22.gif


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 is changed.


 <  Day Day Up  >  


QUE CORPORATION - Using Filemaker pro X
QUE CORPORATION - Using Filemaker pro X
ISBN: N/A
EAN: N/A
Year: 2003
Pages: 494

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net