Exporting Grouped Data

A typical export will output some data from each record in the current found set (or, as discussed in the section "Exporting Related Fields," you may sometimes get multiple sets of information per current record, if you export related fields). But what if you don't want data for each and every record? What if you want to export only data that summarizes information from the current recordset, such as you might see in a subsummary report? FileMaker makes this possible as well.

Consider the example of a system that tracks sales and salespeople. Each salesperson has a country, and many associated transactions. You'd like to export a data set that contains one row per salesperson, with the following data: salesperson name, country, and total transaction volume. Assume that the initial database structure is as shown in Figure 20.7.

Figure 20.7. You might want to export summary data from a database of sales transactions.

To output summary data, it's necessary to have one or more summary fields defined. In this case what's desired is a count of transactions per salesperson. Here, you could define a summary field, called, say, TransactionCount, defined as shown in Figure 20.8.

Figure 20.8. To export summarized data, you need to define one or more summary fields.

The field is a defined as a Count. The count is performed against a field that is known to contain data always, such as a primary key field.

For more information on summary fields and summary reporting, see "Working with Field Types," 69, and "Summarized Reports," 287.

It now just remains to use this summary field in an export. The process is similar to that required for preparing a subsummary report for display. First, isolate the transactions to be summarized (for example, to summarize across all transactions, you would perform Show All Records). Next, sort by the field that would be the break field if you were displaying the data in a subsummary report. Here you want to group by salesperson, so you would sort based on _kf_SalespersonID. Finally, you'd begin the export, and set your export options as shown in Figure 20.9.

Figure 20.9. It's necessary to choose grouping options when exporting summarized data.

This export is set to group by the salesperson ID. The export contains some related fields from the Salesperson table, as well as the summary SalespersonCount field, and an entry called TransactionCount by _kf_SalespersonID. That extra field, rather than the raw TransactionCount field, is the one you want; it's triggered by adding TransactionCount to the export order, after which the TransactionCount field can be removed from the export order, leaving the group count field behind.

If you were then to export this data to Excel, the result would look something like what's shown in Figure 20.10.

Figure 20.10. When you export summarized data, the output contains one row per summary group.

Using more complex sorts and summary field choices, more complex summarized exports are possible.


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