Exporting Grouped DataA 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.
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.
|
Exporting to Fixed-Width FormatsMany computer systems exchange data in some form of fixed-width format. This term refers to formats in which an individual field always contains a certain number of characters of data. Data that's too wide for the field width is sometimes truncated to fit. Data that takes up less space than the field width allows is padded with a padding character, such as a zero or a space, to bring it up to the specified width. For example, the number 797 in a 10-character fixed-width format might be rendered as "0000000797" (left-padded with zeroes). The name Tomczak displayed in a 15-character fixed-width format might be displayed as "Tomczak" (right-padded with spaces). Fixed-width formats also sometimes simply run all the columns together into a single big fixed-width string. There's no need for internal field separatorsbecause the exact width of each field is known, it's easy to determine where each field's data starts and stops. If you need to export FileMaker data to a fixed-width format, you'll need to do a bit of work by hand; FileMaker has no built-in support for exporting to a fixed-width format. At a minimum, you'll need to define some calculations to perform padding and concatenation. If you want to build a more permanent framework for working with fixed-width data, you can consider developing a small library of custom functions to do some of the work. Padding data is a straightforward activity using FileMaker calculations. Say you have a number field called OrderTotal. To left-pad this number with zeroes and enforce a fixed width of 10 characters, you would use the following calculation:
Right( "0000000000" & OrderTotal; 10) If you think about that for a moment, it should be clear how it works. The calculation tacks 10 zeroes onto the left of the numeric value, and then takes the rightmost 10 characters of the result. Likewise, to right-pad a text field called FirstName with spaces to a width of 10 characters, the calculation would look like this:
Left( FirstName & " "; 10) Finally, if you needed to run a set of these fields together into a single fixed-width row, a calculation that concatenated all the individual padding calculations together using the & operator would suffice. You could also create a single row-level calculation without bothering with individual calculations for each field:
Right( "0000000000" & OrderTotal; 10) & Left( FirstName & " "; 10) Calculations such as these will work fine for simple or occasional fixed-width exports. FileMaker also ships with an XSL style sheet, called fixed_width.xsl , that can be applied to a FileMaker data set on export to produce a fixed-width export. The style sheet supports only a single fixed width for all output columns. For more complex needs, you can build a tool of some sort to streamline the process. |
Similar books on Amazon
Similar books
Similar materials
- Dot Commands
- Analyze Risks
- Create new contacts
- About DVD-Recordable Technology
- Writing to Tape
- Attaching Sound Files to PDFs
- Sending Output Back to the Client Machine
- Apple Training Series GarageBand 3
- Summarized Reports
- Appendix C: Case Study Illustrating Possible Supplemental Treatments Under the IOSCO Recommendations