Scripted Exports

All the techniques covered so far involve manual export operations, in which the user drives the process by hand, including the selection of output file type and filename, and the selection of fields for export. Exporting, though, is often an operation you want to be able to perform repeatedly, on demand. You may need to export a membership list to a text file periodically, or create a file containing information on this month's invoices to send to an accounting system that doesn't interact with FileMaker. In such circumstances it's typical that you'll want to export the same set of fields, for different data sets at different times. In these cases, it makes sense to consider using a script to perform the export.

All aspects of exporting can be scripted, from the selection of the records to be exported, to the determination of output file type, filename, and location, to the choices of specific fields and export options. Consider the script shown in Figure 20.11.

Figure 20.11. Exporting can be fully automated by using a script.

This script first establishes its context by navigation to the Member_Detail layout. The layout establishes context and thus governs which source table the exported records will be drawn from. The script then performs Show All Records so that all member records currently in the system will be exported. The Export Records script step performs the bulk of the actual work, and the script then returns the user to his original layout (the layout from which he invoked the script).

The Export Records script step is quite powerful, capable of automating all export settings. The available script step options are shown in Figure 20.11. You can set the output file and file type, as well as any additional XML or Excel options; you can set the export order, including related fields and grouping options; and you can choose to perform the import without displaying dialogs to the user.

Using the Specify Output File dialog, you can configure not only the file type and filename, but the file location as well. The Output File Path List, shown in Figure 20.12, works much like a file reference. Here you can specify one or more possible output locations for the file. As with a file reference, FileMaker will search through these locations one by one until it finds one that exists in the current environment. This makes it possible to create an export script that works correctly across different computer configurations, such as on an office machine and on a laptop on the road, or across both Mac and Windows configurations.

Figure 20.12. You can define a file reference to specify the location of the exported file. The file reference is stored with the script.

Note

In the Specify Output File dialog, you'll also specify the file type of the output file. If that file type is associated with additional options (as, for example, the XML and Excel file types are), additional dialogs will prompt you for those options. Once chosen, all of these options will be saved with the script, and can also be returned to and edited later.

Next, you'll have the opportunity to specify and save all the settings that can be made via the Specify Field Order for Export dialog (shown previously in Figure 20.9). This includes fields, field order, grouping and formatting options, and the output file character set. These are exactly the options you're familiar with from performing a manual export.

Finally, when using the Export Records script step, you can choose whether to display any dialogs to the user during the export process. Unless you plan for your user to interact with the export options in some way, such as tweaking the list of fields or field order, you may well want to check this box so that the export "just happens." In the case of the script shown in Figure 20.11, the user would see a short pause, perhaps a progress dialog that tracks the progress of the export, and then the file would simply appear in the location designated in the export settings. A more elegant script might check for errors in the export, and then report the results to the user.

The Export Field Contents script step can also be used, especially when exporting container fields. The available export options are similar to those for the Export Records script step. The chief difference is that because Export Field Contents exports data from only one field and record at a time, it would need to be invoked from within a looping script of some sort in order to export data from all records in the current found set.