Copying Database Structure

Since a large percentage of a database designer's work is fairly repetitive, FileMaker Advanced provides some tools that let you take shortcuts through the tedious process of creating tables, fields, and scripts. By copying work you've already done, you can spend less time defining fields or recreating complex scripts and more time doing the creative work of designing a database.

As this section describes, you can import tables and fields without copying data between tables in the same file and between different files. FileMaker also provides Copy and Paste buttons in several major dialog boxes so that you can reuse fields, scripts and script steps. (You need full access privileges in both the source file and the target file to import or copy and paste elements from file to file.)

19.5.1. Importing Tables and Fields

Back on Section 17.3.2, you learned how to have FileMaker create tables and fields as you import data. But FileMaker Advanced is even cleverer. It lets you import tables and fields without the accompanying data, so you can copy the framework and fill in the information later. Start by choosing File images/U2192.jpg border=0> Define Database and clicking the Table tab. Click Import, and then locate the file that contains the table you want to copy.

You can even choose the current file, effectively copying an entire table or set of tables within the same file. Next, you tell FileMaker which specific tables to import in the dialog box shown in Figure 19-10.

When FileMaker Advanced has finished importing the tables and fields, it displays a dialog box similar to the one you see when you import data (Section 17.3.1). You see a summary of how many tables and fields were created during the import.

Tip: When FileMaker Advanced builds your new tables and fields, it saves a record of its progress in a log file. If you see an error in the summary window, click the Open Log File button to see what went wrong.

19.5.2. Copying Fields

If you have complex calculations that you want to reuse in another table within the same file, or in a different file, use the Copy and Paste buttons on the Fields tab in Define Database dialog box. Use the Shift key to select items next to each other in the field list. Use the key (Mac) or the Ctrl key (Windows) to select fields that aren't next to each other in the list. When you've selected the fields you need, click Copy. Then open the table where you want to create the new fields, and finally click Paste to do the deed.

When you copy calculation fields, your results may vary depending on whether you also copy the field's calculations reference (Section 9.2.1). It usually saves time to plan ahead and make sure the field references already exist in the target table before you copy and paste. But you can always paste the calculation fields and clean up the references later, especially if you intend to use different field names anyway. Here are some general rules:

• If fields matching the references in your copied calculation don't exist in the target table, FileMaker pastes the calculation as a comment (/*Amount * Quantity*/), since it can't find matching fields in the new table. Simply edit the calculations with the new field references, and then click OK.
• To save yourself some editing work, first paste into the target table (or create) fields with names that match the ones in the calculation you're transferring. Then, when you paste the calculation, FileMaker resolves the field references automatically.
• If the field references are local (that is, they refer to other fields within the same table), you can copy the fields referenced in the calculation and the calculation field at the same time. When you paste the set of fields, FileMaker resolves the field references automatically, and you have nothing to edit.
• If you're copying a calculation field that contains a fully qualified field reference (containing both a table name and a field name, like Expenses::Job ID), the calculation again transfers just fine.

19.5.3. Copying Scripts and Script Steps

You can use the Import button in the Define Scripts dialog box to import whole scripts from other files, but if you have FileMaker Advanced, you can avoid that cumbersome process. Simply open the file that contains the scripts you want to copy, then use the Copy and Paste buttons in the Define Scripts dialog box. Copy the scripts you want to reuse, then open the Define Scripts dialog box in the target file, and finally click Paste. Check all pasted scripts to see if any field, layout, or other reference needs to be pointed to another element in its new location. Copying or importing scripts works best when you need the whole script and all the elements referenced in your script were already in place, so that no script steps break on the way in.

You can also copy script steps individually or in chunks. In the Edit Script dialog box, select just the script steps you want, then click Copy. Then you either create a new script or open the script that needs your copied steps. Highlight the script step just above where you want the next steps to land, and then click Paste. Your recycled steps appear in your script. Fix any broken references as needed.

You don't even have to move to a new script to find Copy and Paste useful. If you have a script in which a series of steps could be duplicated, and then edited so that they work slightly differently, just copy and paste them. Sure, you could use the Duplicate button, but then you have to move each step down into place one by one. With Copy and Paste, you get to place the new steps right where you want them.

Note: FileMaker doesn't include break points when you Import or Copy/Paste scripts or script steps.