Exporting Related Fields

Table of contents:

All exporting in FileMaker takes place from the context of a single table. In general, then, it's not possible to export data from several tables independently in one stroke. It is possible, though, to export data from tables related to the current one, whether immediately or more distantly.

Doing so is a simple matter of choosing fields from related tables when specifying fields for export. Consider a typical ordering system, in which a table holding order records is related to a table holding customer records via some sort of customer ID. The system probably doesn't store the customer name in the order table because this violates some principles of good data modeling. In FileMaker, the customer name probably is stored in a Customer table, and displayed on an order record via related fields.

Because the customer name is stored in a related field or fields, if you want to export the customer name along with the order, it will be necessary to export the related fields as well. Figure 20.5 shows what this might look like.

Figure 20.5. You can export fields from the current table, or from any related table as well.

Tip

Notice a useful aspect of FileMaker's new field list filtering feature: Because the related fields for the customer's first and last name are displayed on the current layout, they are automatically available for export in the default (layout-based) field listno need to go hunt for them in the field list for the related table.

The case of orders and their parent customer records is fairly clear-cut. Because there is only one customer record per order, the export produces one row per order, with the associated unique customer data as part of the row. But what if you try to export related fields from a child table, one that exists in a many-to-one relationship with the current table? Suppose that you shift to a detail layout showing individual orders, and export fields from that layout, including fields from related order lines? The result might not be what you'd expect, and may or may not be useful. Figure 20.6 shows a sample of such data, displayed in Excel.

Figure 20.6. You can export fields from child tables, in which case you may get records with partial data.

Notice that although only five orders were exported, there are 10 rows in the output. FileMaker has output one row for each related record. The first row contains all data from the parent record (the order) along with data from the first related line item. Subsequent rows include only data from the line item, and leave the columns for data from the order record blank.

The behavior of exports that include related fields, then, depends on the number of records related to the current record being exported. When only one related record exists, one row will be output. If multiple records exist, a row will be output for each related record.


Exporting Grouped Data

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