Creating Ranged Relationships

FileMaker Pro's new non-equijoin feature has a great number of usestoo many to cover exhaustively. We'll touch on a couple more before moving on. In an earlier example, we looked at a relationship that let you see a customer's invoices for the current year only. What if you wanted to refine those criteria further, and look at invoices for just the first fiscal quarter (January through March)?

Let's return to the Invoicing example (shown in Figures 7.4 through 7.9). The first "date filtering" relationship was created when a calculation field was added in the Customer table to produce the start of the current year. For this new query, you need to add one more calculation fieldone that will give you the end of the first quarter. This one is also quite simple; the new calculation is shown in the field list in Figure 7.21.

Figure 7.21. This table of customer data also contains two calculation fields for performing ranged relational comparisons.

To factor this new calculation into the query logic, you need a new relationship. That in turn means a new table occurrence in the Relationships Graph. Open the Relationships Graph and add a new table occurrence called InvoiceFirstQuarter. Then add a new relationship. Define it to have Customer on the left and InvoiceFirstQuarter on the right. You need three match criteria in this case: __kp_CustomerID = _kf_CustomerID, CurrentYearStart InvoiceDate, FirstQuarterEnd images/U2265.jpg border=0> InvoiceDate. All three have to be true for a match to exist. Figure 7.22 shows the Edit Relationship dialog for this new relationship, and Figure 7.23 shows the Customer layout with a third portal added to show just the first-quarter invoices.

Figure 7.22. This complex relationship is intended to find all of a customer's invoices between two dates.

Figure 7.23. FileMaker's multiple match criteria can easily be used to create relationships that pick out ranges of related records.

For a range of additional techniques for doing interesting things with portals and nonstructural relationships, see Chapter 16, "Advanced Portal Techniques," p. 471.