Working with Data from Distant Tables

The relational model of FileMaker 8 has significant power in its capability to handle a relational tree. As an example, suppose that you have a classic Customer-Product-Invoice system with a Relationships Graph like the one shown in Figure 7.24.

Figure 7.24. Relationships Graph for a system that tracks customers, products, and invoices.

You might want to do a number of things in this system that would have been slightly cumbersome in versions of FileMaker prior to FileMaker 7. Suppose, for example, that you'd like a portal on a customer layout showing all the products that customer had ever purchased. Previously, you might have used a somewhat complicated technique involving global fields and the Copy All Records script step to "hop" from a customer record to the customer's related invoices, and then again from the invoices to their related line items, and finally from the line items over to products. Scripting this process was relatively slow, and the technique was not guaranteed to work for large data sets.

By contrast, in FileMaker 7 and 8 it's as simple as creating a portal on the Customer layout and choosing the Product table occurrence as your data source. FileMaker correctly navigates the Relationships Graph from Customer to Product and brings you back a listing of all products ever purchased by a given customer. Although this is somewhat difficult to illustrate concisely (we'd need to show the intervening invoices), Figure 7.25 shows what the result might look like. Even though some of the products have been entered on several invoices for this customer already, each one appears only once, as you would expect.

Figure 7.25. FileMaker 8 lets you perform a deep relational view, allowing you to display, for example, all products a customer has purchased in a given year.

The technique could be reversed. For example, from the viewpoint of a Product record, you could just as easily see a portal of all customers who had ever purchased that product.

This capability to perform multistep relational navigation in a single swoop is a powerful feature of FileMaker. Combined with some of the multimatch and non-equijoin relationships you saw earlier, this technique can produce quite sophisticated queries. Think back to the example that used a relationship to isolate a customer's invoices in the first quarter. Well, if those invoice records eventually contained a link back to a products table, you could use a deep relationship (as we sometimes call them) to produce a portal of just those products purchased by a given customer in the first quarter.


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

Flylib.com © 2008-2020.
If you may any questions please contact us: flylib@qtcs.net