Relationships as Queries

We want to introduce you to the idea of a relationship as a kind of query. Consider a database that stores information about customers and their invoices. On a layout specific to the Customer entity, you can add a portal of invoice information. Figure 7.1 shows that layout.

Figure 7.1. Using a portal to display a particular customer's invoices.

The portal looks into a table of invoices, and is based on the fundamental one-to-many relationship between a customer and an invoice, based on a shared key called CustomerID (the name varies slightly depending on whether it's a primary or foreign key; see Figure 7.2). But the portal also represents a kind of query, which says, "Show me all invoices that have the same customer ID as this customer."

Figure 7.2. A Relationships Graph for a simple customer-invoice system.

That's all well and good, but suppose that a user comes to you and asks for a portal that shows only this year's invoices for that customer. That's a different query than before, and therefore requires a different relationship. Well, FileMaker can do that. Creating this new query requires delving into three new concepts in FileMaker relationships: the concept of non-equijoins, the concept of a table occurrence, and the concept of a multiple match.

Non-Equijoins

Don't let the scary terminology throw you. The concept is simple. Refer again to Figure 7.2, which illustrated the relationship between Customer and Invoice. Notice that the line representing the relationship has an equals sign right in the middle. To explore what that means, you would double-click on the relationship line to edit the relationship. The Edit Relationship dialog is shown in Figure 7.3.

Figure 7.3. FileMaker can use any of seven different operators to compare match fields.

The middlemost box shows the match field or fields defined for this relationship. This current relationship is built between a __kp_CustomerID in Customer and a _kf_CustomerID in Invoice. The match criterion is based on equality, meaning that invoices match (and hence are displayed in a portal that shows records from this relationship) if and only if the _kf_CustomerID in Invoice is exactly equal to the __kp_CustomerID in Customer. This is the correct behavior for the structural relationship represented on the ERD. Such a relationship, based on equality, is often called an equijoin.

The upper part of the Edit Relationship dialog is where the match actually gets defined. And you'll notice, in Figure 7.3, that equality is not the only operator available for defining a match. In fact, you can build relationships based on combinations of any of the seven comparison operators.

How does this help you with that user who wants to see a portal of invoices for this customer for the current year only? Well, it implies that you need to extend the match criteria somewhat. In addition to matching on the customer ID, you also need to restrict the match to just those invoices on which the date is greater than the start of the current year.

The Edit Relationship dialog enables you to build matches between fields in different tables. You know you want to build a match that incorporates the invoice date, but you need to somehow compare that to the start of the current year, and the database doesn't have a field for that at present.

You can solve that problem by defining a calculation field that gives you the start of the current year. The definition for such a calculation is shown in Figure 7.4. It's a simple formula that returns the first day of the current year, expressed as a date.

Figure 7.4. You'll be able to incorporate this calculation (which we've chosen to call CurrentYearStart) into a relational match for invoices within a certain date range.

Note

The figure doesn't show the storage options, but we've chosen to make the calculation unstored so that it updates properly as the current date changes.

With the calculation in hand, you can proceed to build the "this-year-only relationship." You could certainly just edit the relationship you originally created between the two tables, but it's more likely that you'll want to keep that structural relationship intact and add a new one to represent this new query.

Adding a Table Occurrence to the Relationships Graph

We used the term table occurrence sporadically throughout Chapter 6 when referring to the graphical table representations in the Relationships Graph. Why not just call them tables and be done with it? Well, they're not the same thing. An underlying table (or source table, meaning those tables that appear in the Tables tab of the Define Database screen) can appear multiple times in the Relationships Graph. In fact, anytime you want to have more than one relationship between two source tables, you need to add an additional occurrence of at least one of the source tables to the Graph. You cannot create multiple relationships between two table occurrences in the Graph. If you want to relate table A to table B in two different ways, you need two occurrences of at least one of the tables.

In the current example, you want a new view of invoices from the perspective of a customer. Therefore, you need to add a new occurrence of the Invoice table to the Graph. So far, FileMaker has created all table occurrences for you automatically. Anytime you add a new table to a database, FileMaker adds a corresponding table occurrence to the Graph, and gives it a name identical to that of the underlying table. Now you need to add a new occurrence of Invoice to the Graph by hand. To do this, open the Relationships Graph in the Define Database dialog box and click the Add Table Occurrence icon in the lower-left corner. Figure 7.5 shows the resulting Specify Table dialog box.

Figure 7.5. You can add a new table occurrence to the Relationships Graph.

In the Specify Table dialog, choose a source table to include in the Graph. In this case, you want to add another occurrence of Invoice. Notice that FileMaker instructs you to "give this table a unique name in the graph." At the bottom of the box is a place for you to name the table occurrence. Because the original occurrence of the Invoice table is already named Invoice, you need a new name. We recommend a name that says something about the way the new relationship will be used. In this case, something like InvoiceByYear should fit the bill. Figure 7.6 shows the Relationships Graph with the new table occurrence, as well as the CurrentYearStart field you added to Customer.

Figure 7.6. A second occurrence of the Invoice table has been added to the Graph.

All that's left is to create a relationship from Customer to this new table occurrence, which will incorporate the CurrentYearStart field into the match criteria.

Defining a Relationship with Multiple Match Criteria

Chapter 6 showed you how to define new relationships in the Relationships Graph with a graphical technique consisting of dragging from one match field to another. Also, a new relationship can be added simply by clicking the small Add Relationship icon (the second icon in the Tables/Relationships icon group at the lower left of the Relationships Graph). Clicking that icon brings you the familiar Edit Relationship dialog, but it's initially completely empty.

Begin by selecting the two tables that are to participate in the relationship. Choose Customer on the left, and InvoiceByYear on the right. Then define the first match criterion.

Select the correct customer ID field under each table name, make sure that the menu of operators in the middle shows an equal sign, and click the Add button. So far it looks exactly like the Edit Relationship screen for the original Customer-Invoice relationship, as shown in Figure 7.3, except that the table occurrence on the right is now InvoiceByYear instead.

But you still need to tell FileMaker to consider only those invoices on which the invoice date is greater than the start of the current year. To make this happen, you can add another criterion. Select CurrentYearStart on the left, InvoiceDate on the right, and from the operator menu in the middle, select the "less than or equal to" sign. (This signifies that January 1 of the current year must be less than or equal to the invoice date.) Click Add, and the new match criterion is added in the middlemost box, as shown in Figure 7.7.

Figure 7.7. Using a non-equality condition to build a relationship.

Notice what that middle box is saying now. There's a large "AND" in the left margin, which says that this relationship pulls back only those invoices for which the customer ID matches and the invoice date is sometime this year.

Notice also how FileMaker represents this new relationship in the Relationships Graph. Each end of the relationship line forks, to indicate the multiple match criteriaand the operator symbol in the middle of the line is a curious kind of X, indicating a complex match with multiple operators at work. Figure 7.8 shows the Graph with the new relationship.

Figure 7.8. The Graph indicates when a relationship is based on multiple match fields. The [X] comparison operator shows that multiple operators are in use as well.

To use the new relationship, you could draw another portal on the Customer layout. Base it on InvoiceThisYear instead of plain Invoice, and use the same data fields from the source table. The result should be similar to what you see in Figure 7.9.

Figure 7.9. More complex relationships can produce sophisticated views, such as the Invoices This Year view shown here.

These three conceptsnon-equijoins, multiple table occurrences, and multiple match criteriaafford you extraordinary flexibility as a database developer. The sections ahead explore examples that show how to use these tools to solve particular problems of database design.