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.
Part I: Getting Started with FileMaker 8
Using FileMaker Pro
Defining and Working with Fields
Working with Layouts
Part II: Developing Solutions with FileMaker
Relational Database Design
Working with Multiple Tables
Working with Relationships
Getting Started with Calculations
Getting Started with Scripting
Getting Started with Reporting
Part III: Developer Techniques
Developing for Multiuser Deployment
Advanced Interface Techniques
Advanced Calculation Techniques
Advanced Scripting Techniques
Advanced Portal Techniques
Debugging and Troubleshooting
Converting Systems from Previous Versions of FileMaker Pro
Part IV: Data Integration and Publishing
Importing Data into FileMaker Pro
Exporting Data from FileMaker
Instant Web Publishing
FileMaker and Web Services
Custom Web Publishing
Part V: Deploying a FileMaker Solution
Deploying and Extending FileMaker
FileMaker Server and Server Advanced
Documenting Your FileMaker Solutions