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.


Part I: Getting Started with FileMaker 8

FileMaker Overview

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

Implementing Security

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

FileMaker Mobile

Documenting Your FileMaker Solutions

Using FileMaker 8
Special Edition Using FileMaker 8
ISBN: 0789735121
EAN: 2147483647
Year: 2007
Pages: 296 © 2008-2020.
If you may any questions please contact us: