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.