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 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.