by John Mark Osborne, <jmo@best.com>
Summary Export
BONUS: SCRNSHOT.FP3
The export option called summarize by is very powerful yet seldom used. It can be used to simply export the results of a sub-summary report for use in another program like a spreadsheet or for more complicated solutions like deleting duplicate records.
The best place to start with summarize by is to describe the basic capabilities of the feature. Summarize by was designed to enable the export of sub-summary reports. So, make sure to study sub-summary reports before reading this article since they will not be described in detail.
The basic premise of a sub-summary report is to organize like records based on the contents of a field. For instance, a database solution might have a Category text field, Sales number field and Total Sales summary field. What a sub-summary report based on the Category field can do is group all of the records with like values and then report the sum of all the Sales field using the Total Sales field. For instance, a report might look like this:
Category Total Sales
A 143
B 99
C 210
What summarize by allows one to do is export a sub-summary report to a text file.
Summarize by exporting is not as straightforward as it could be so the steps to perform a basic summarize by export are covered in the next few paragraphs. The SCRNSHOT.FP3 bonus file that comes with this issue of the magazine includes three screen shots that will be referenced.
Figure 1 displays the Export dialog with a red circle around the summarize by button. Before exporting, a summarize by export requires the database to be sorted just like a sub-summary report layout. The field that is sorted by in the example is the Category field.
Figure 2 shows the dialog that appears when the summarize by button is clicked. The checkmark in the screen shot is circled in red because it is the most common mistake made when using summarize by. What happens is that someone new to the feature clicks on the field and it highlights. This makes them figure that the field has been selected and they click okay to accept the dialog. Unless the column to the left of the field is clicked so that the checkmark appears, then export will not be summarized by the sorted field. One indicator that the process worked correctly is the italics version of the field that appears in the export order.
In figure 3, this field is Total Sales by Category. Now all that needs to be done is for the Export button to be clicked.
Sub-summary reports come in two different flavors. One flavor includes a body part and another flavor does not have a body part. The difference is that one lists record detail as well as summaries while the other only lists summaries. For instance, the example report shown above lists only the summary values while a report that had a body part would list every single record that made up the totals. In contrast, summarize does not have the ability to export record level detail. All it can export is summary information. If record detail is needed then the best technique would be to use calculation fields with the GetSummary function.
Another bonus file that is included with this article is called DUP2.FP3 which comes from the upcoming Scriptology workbook and CD to be released in September 1997. It demonstrates how to use the summarize by feature to delete duplicate records. Since the summarize by feature only exports the first record of each set of records, it is easy to see how this feature can delete duplicate records. All that needs to be done is to create a calculation field that uniquely identifies records. This could be a simple concatenation of the first and last name fields or as complicated as a formula that combines part of the phone number and address fields as well. For instance, the following formula will help to prevent John Smith from Florida and John Smith from California from being seen as the people by summarize by:
First Name & "^" & Last Name & "^" & LeftWords(Address, 1)
^ designates a space
This formula uses the first word in the Address field which should be the number designation for the street to help differentiate the two John Smiths. Also, notice that the fields are all separated by spaces. That's because FileMaker only indexes the first 20 characters of each word. The spaces make sure long names are completely indexed. If not then the database will not sort properly.
Once the export file is created, it can be imported into a clone of the file or back into the original after all the records have been deleted. The bonus file uses the later method to show how cool this method can be for deleting duplicate records. If the later method is used then make sure a backup of the database is made before running the script.
One last feature that can be added to this technique is to sort by a creation date field in addition to the calculation field. This will make the most recently added record sort to the top so that it is the one that is kept when exported.
## END ##