FileMaker Extra: Exploiting the FileMaker-to-FileMaker Import

FileMaker Extra Exploiting the FileMaker to FileMaker Import

You saw earlier that it's possible to import data into one FileMaker table from another. Those tables can be in the same FileMaker file or different ones. This capability has a number of useful and interesting applications.

Duplicating a Found Set

Occasionally, you'll encounter situations where you want to duplicate a found set of records. Of course, as with most things in FileMaker, there are several approaches. You could write a script that would start at the beginning of the found set and loop through it, duplicating as it went. But you'd quickly find you had some tricky record-position issues to deal with. (Duplicating records can change which record is the current one, so it can be hard to keep your place when looping through a found set.)

One general rule for speeding up FileMaker operations goes something like this: Where possible, replace scripts, especially looping scripts, with built-in FileMaker operations. FileMaker's Replace command is much quicker than a script that loops over a group of records and performs a Set Field step on each record. FileMaker's Delete Found Records command is quicker than a script that loops over a set of records and deletes each one. And so on.

Another choice is to export these records to a separate table, then import them right back into the original table again. A single script can control both the export and the import, and the logic is much easier to read and understand.

Duplicating Between Tables

Suppose that you have a simple order tracking database. The database has tables for customers, orders, order lines, and products. Each order, of course, has one order line per product on the order.

Suppose also that users have said that they want to create new orders by checking off a number of products from a list and then having a new order be created with one line for each selected product. So a user would check off Screwdrivers, Milk, and Roofing Tar in a product list, click a button that says Make Order, and see a new order with lines for the three selected products.

Again, you can do a number of things with scripts, but one elegant solution is to gather up the selected products into a found set and then simply import that found set (well, the relevant fields from it, anyway) into the Order Lines table, thus creating one new order line per selected product.

Moving, Consolidating, and Re-creating Tables

With the added capability to create a new table when importing data into FileMaker 8, it has also become possible to use the FileMaker-to-FileMaker import to perform some important migration tasks. If you're converting files from FileMaker 6 or earlier to FileMaker 8, and you intend as part of your conversion to consolidate several older files into one new file with several tables, this feature can help. Creating a new table via import will preserve all aspects of the table schema. Still, if you're performing consolidation as part of a conversion, we recommend you invest in FileMaker Pro Advanced and its more heavy-duty features for copying and pasting schema elements.

For a full discussion of consolidating multiple tables, see "Repointing Table Occurrence References," p. 561.