Working with Related Data

So far in this chapter you've learned how to create additional tables in a FileMaker system and how to build relationships between those tables based on well-constructed match fields. This section shows you how to begin to use your relationships to work with and create data in multiple tables at once.

Using a Portal to View Related Child Data

The town records database system has two tables in it now. But how do we use them? Say there's a record for a town called Gorre, with a TownID of 1, and you want to enter information about the mayor and town councilors of Gorre. One way would be to navigate to the TownOfficer layout and create a record for each official, assigning a TownID of 1 to each. That seems like a tedious way to create data, and it's potentially error-prone because a user must know and correctly enter the TownID of every town. Additionally, there's no way to see a list of town officials when looking at the master town record in the Town table.

FileMaker solves both these problems with a tool called a portal. A portal is a special FileMaker layout element that lets you work with data across two (or sometimes more) tables at once. In the case of the Towns database, a portal lets you look at records in the TownOfficer table while you're sitting on the "parent" Town record.

To see a portal in action, navigate to a layout that has the parent table (Town, in this case) as its table context. After you're on this layout, drop into Layout mode and you'll see the portal tool among the available tools in the status bar (see Figure 6.9).

Figure 6.9. This is a layout in Layout mode, showing the FileMaker portal tool.

Note

You should be able to tell the table context by the fields on the layout, but you'll recall that the Layout Setup dialog, accessible from the Layouts menu while in Layout mode, gives the definitive answer.

Click once on the tool, and then, on the layout, drag out a box wide enough to show an entire town officer's name and release the mouse. You'll get a dialog box asking for details about the portal's contents, behavior, and display. The dialog is shown in Figure 6.10.

Figure 6.10. FileMaker's Portal Setup dialog.

In general, when you set up a portal for the first time, you'll need to do the following:

• Choose a table occurrence from which to display data
• Choose additional portal options
• Choose data fields for display in the portal

More details on each of these steps follow.

First, you need to specify where the portal gets its data. In the Portal Setup dialog, the Show Related Records From list enables you to choose which table to draw data from. The list is divided into sections: one for related tables and one for unrelated tables. (The question of whether a table is "related" or "unrelated" is determined by the Relationships Graph.) In the current example, for a portal on a layout in which the table context is the Town table, there should be only one available choice in the menu: the TownOfficer table, which is the only other table related to Town in the Relationships Graph. By choosing TownOfficer from this menu, you're instructing FileMaker to show you all town officer records that are related to the currently visible town record (in other words, all that share the same town ID).

The Portal Setup dialog contains a number of other choices as well. For now, you can opt to display just 12 portal rows, and put a vertical scrollbar on the portal so that you can scroll down if a town has more than 12 officers. You can also apply coloring or striping to the portal if you choose.

FileMaker also displays a dialog at the end of the portal creation process, asking which fields from the related table you want to show in the portal. For this example, we chose to show FirstName, LastName, Position, and PositionStartDate from the TownOfficer table. You can always change or add to these selections later as well.

Back in Browse mode, you should see a list of town officers in the portal (assuming that you created some and they're still around), as shown in Figure 6.11. It would be ideal if you could type right into the rows of the portal and enter your data that way. By default, this isn't possible (try it and see), but it's easy to add this capability to the portal.

Figure 6.11. A portal can display multiple records from a related table.

Using a Portal to Add Related Records

Portals can be used for data entry, as well as data viewing. It's possible to configure the portal and its underlying relationship so that a user can add officers to a town record by typing directly into the portal rows.

To accomplish this task, you need to edit the relationship between Town and TownOfficer. On the Relationships Graph, double-clicking on the relationship line between the two tables brings up the Edit Relationship dialog, shown in Figure 6.12.

Figure 6.12. The Edit Relationship dialog is where you can edit individual relationships in the Relationships Graph.

For each of the tables participating in the relationship, there's a check box under it called Allow Creation of Records in This Table via This Relationship. If you check this box on the TownOfficer side of the dialog, it becomes possible to create TownOfficer records via this relationship.

If you check this option, and return to the Town layout in the parent table, you'll discover that you can now click on an empty row of the portal and type in an officer for this town.

This is all good in its own right, but another payoff comes if you flip over to the TownOfficer layout to get a direct look at the records in the TownOfficer table. You'll notice that there are new records in TownOfficer, corresponding to those you entered in the portal. Notice in particular that the _kf_TownID has been set automatically to the ID of the town record from which you were performing the data entry. FileMaker has created the foreign keys in TownOfficer for you automatically. Figure 6.13 shows a possible view of the TownOfficer table after portal-based record creation.

Figure 6.13. Adding records to the portal on the Town layout creates linked records in the TownOfficer table.

With portals, it's easy to view, create, and manipulate records on the "many" side of a one-to-many relationship.

Working with Related Parent Data in a Child File

You've seen how to use a portal to add related (child) records to a master (parent) record. Suppose that you want to turn the problem around, and see information about the town, on the officer's data record. Figure 6.14 shows a somewhat bare-bones rendition of the TownOfficer layout. In addition to information about the town officer, you can see the TownID, but you can't tell what the name of that town is, much less any other information about it. Given a record for John Samuel, who's the Sheriff of the town of Gorre, you'd like to be able to view the town's name, county, and population on John's data record. So far, this database doesn't do that. But it's not hard to configure.

Figure 6.14. A simple view of a town officer record. No information about the associated town is shown, other than the TownID.

To make this happen, you need to edit the TownOfficer layout a bit. Enter Layout mode and shrink the _kf_TownID field so that there's some room to the right of it. Drag another field to the right of _kf_TownID. You'll see the Field/Control Setup dialog, as shown in Figure 6.15. If you inspect the menu at the upper right of the box, you can see that it indicates that the box is currently allowing you to choose fields from the TownOfficer table (the current table). But you can change the menu to show fields from a related table; in this case, Town is your only choice (TownOfficer is not related to any other tables in the Relationships Graph). Figure 6.15 shows the dialog after the user has chosen to show fields from the related Town table.

Figure 6.15. It's possible to display individual fields from related records on a FileMaker layout.

Change the menu to read Town, and select the Name field you find there. Back in Layout mode, you'll see a subtle change in the display. The field name now has two colons preceding it, as Figure 6.16 shows. This is FileMaker's cue that the field doesn't come from the current table, but from a related table. At this time, you can use the same technique to bring in the County and Population fields from the Town table.

Figure 6.16. Fields from related tables are displayed with a preceding double colon in Layout mode.

Drop back into Browse mode and you'll see that the Name field now shows the name of the town of Gorre, along with its county and population. Just to understand the point, clear out the _kf_TownID field. Notice that the town name and other information disappear. Reset the _kf_TownID field to 1 again, and back comes the town data.

Portals Versus Related Fields: Which Is Which?

You might be puzzled that previously we used a portal to access and view related data, and here we're using this seemingly quite different technique. Which is which, and how do you know when to use each one?

The answer is actually quite simple:

Use a portal to view multiple related child records from the perspective of a parent record. Use related fields to see a single parent record's information from the perspective of a child record.

In the town example, we used a portal on the parent record (the town) to view multiple related child records (the town's officers). Looking from the other side, we used individual related fields on a child record (the town officer) to view information from the single parent record (the town).

To put this in terms of the "one-to-many" language we've been using, a portal is used to look at the "many" from the perspective of the "one," and single related fields are used to look at the "one" from the perspective of the "many."