You may already know that merging FileMaker Pro database files can be a tedious and challenging process. After copying, you could spend hours fixing broken links, missing fields, remapping buttons, etc. For this reason, many people simply choose to maintain multiple files even when that may not be the ideal or preferred setup.

With the method outlined below, you can merge databases with a minimal amount of broken links. There may still be some tedious grunt work to do, but by following these steps in the order given, you can significantly reduce the time and tedious tasks involved.

The Quick Step-by-Step List

In general, when copying functionality from one FileMaker file to another, you will want to do so in the following order:

1. Rename table occurrences and fields in source file (throwaway file) to match target file.
2. Copy any necessary custom functions
3. Create any necessary value lists. These can be blank for now.
4. Copy tables and relationships.
5. Copy any necessary fields into existing tables.
6. Check Calculated Fields (check the Import.log file or use the Import Log Interpreter tool)
7. Copy the value lists
8. Create Blank Layouts
9. Copy Scripts
10. Copy Layout Objects and Recreate Layout-Based Script Triggers

This will insure that any buttons, calculations or other objects that rely on other objects will maintain their links. See more details below.

Step 1: Rename table occurrences and fields

The main idea behind this step is that you want to make sure that the two files have matching table occurrence and field names. This way when you copy scripts and other objects from one file to another, it will properly map the tables and fields. You don’t want to have to go through each layout and script and fix all of the “<field missing>” or “<table missing>” errors manually.

At this point, you are not copying any fields or tables yet, you simply want to make sure than any of the table occurrences and fields that already exist in your target database match the corresponding table occurrences and fields in the source file (the one you will be throwing away).

Step 2: Copy custom functions

If there are any custom functions in the source file that do not exist in the target file, copy or import them from the source to the the target file. We do this now in case custom functions are referenced in calculated fields or script calculations. We don’t want to have to fix “<function missing>” errors in our calculations.

There are two methods to achieve this:

Method 1:

1. Open the Manage Custom Functions dialog in the target file.
2. Click the Import button.
3. Browse to source file.
4. Check the functions you want to import.
5. Click OK.

Method 2:

1. Open the Manage Custom Functions dialog in the source file.
2. Select the custom functions you want to copy (use CMD or CNTRL click to select multiple).
3. Select Edit->Copy or CMD or CNTRL-C to copy.
4. Close the dialog and optn eht Manage Custom Functions dialog in the target file.
5. Select Edit->Paste or CMD or CNTRL-V to paste.

Step 3: Create value lists

If there are any values lists in the source file that do not exist in the target file, create them in the target file. It is not necessary at this point to define the values for the value lists; they can be empty for now. For value lists that are populated from table data, it won’t be possible to define the values. At this point, empty value lists will work fine. We want to do this now before we copy tables and fields in case any of the fields use value lists for validation.

I’ve found that the easiest way to do this is to grab a screenshot of the Define Value Lists dialog in the source file and open the Define Value Lists dialog in the target file. Create the value lists one by one while going down the list in the screen shot.

Step 4: Copy tables and create relationships

Now, we’re ready to copy any tables that exist in the source database, but not in the target database, and set up the table occurrence relationships for those tables. Copying the tables is the easy part, but unfortunately, there is no shortcut for replicating the relationships. Here’s my favorite method:

Inspect and take notes on the existing relationships:

1. Open the Define Database dialog in the source file.
2. Print the relationship graph from the source table.
3. Manually inspect the relationships in the source file relating to the tables to be moved moved.
   Hint: Select a table occurrence of your source table and press CMD or CNTRL-U on your keyboard. FileMaker will automatically select all occurrences of that source table in the relationship graph so you can quickly see all the places that table is referenced in the graph.
4. Take notes on the printed relationship graph regarding which relationships need to be copied and the properties of those relationships.

Copy the tables:

The easy part! 🙂

1. Under the tables tab, select the tables to copy.
2. Click the Copy button near the bottom of the window
3. Close that dialog and open the Define Database dialog in the target file
4. Click the Paste button.

Create relationship:

Now, set up the relationships to match those in the source file:

1. With the Manage Database dialog still open, add each of the table occurrences and relationships that you noted on the printout to the target file.

It may only be one step, but depending on the complexity of the relationships you need to replicate, it could be the most time consuming part of this whole process.

Step 5: Copy necessary fields into existing tables

If there are tables in your target database that will be used in place of tables that existed in the source table, copy them from the table in the source database to the table in your target database.

For each table that has fields that need to be copied. Do the following:

1. In the source file, open the Manage Database dialog and go to the field list of the table with fields to be copied.
2. Click to select each field you need to copy. CNTRL or CMD-Click to select multiple.
3. Click the Copy button in the lower right area of the window.
4. Close the dialog (you can click Cancel since you made no changes).
5. In the target file, open the Manage Database dialog and go to the field list of the table for the fields to be pasted into.
6. Click the Paste button.

Step 6: Check Calculated Fields

The tables and fields copied in steps 4 and 5 could have problems if any of the following occurs:

• Field name mismatch
• Table occurrence name mismatch
• Missing relationship
• Missing custom function
• Mismatch in custom function name

The easiest way to find and fix these is to check the Import.log file. You can do this manually with a text editor or with the Import Log Interpreter tool.

Alternatively (though not recommended – see the Important Tip in Step 2), you could use the brute force method and simply check every field and table that you copied. Double check any fields that use calculations or lookups for table missing or field missing warnings. Also check for calculations that are completely surrounded by block comments: i.e. “/*” at the beginning and “*/” at the end (without the quotes). This indicates that FileMaker could not resolve all of the references in the calculation. Remove the block comments and click OK to save the calculation. At this point, FileMaker will probably give you an error message highlighting the problem with the calculation. You can then do some troubleshooting.

Step 7: Copy the value lists

Now that the table occurrences and fields are in place, you can set up the value lists that depend on them. Depending on how many you have, this can be a tedious process. Here is the most efficient method I’ve come up with:

1. Open the Define Value Lists dialog in the source database.
2. Open each value list you want to copy.
3. Take notes in a simple text document about each value list. Either note the relationship used and the appropriate settings or copy the list of custom values. Alternatively, you can take a screen shot of each value list dialog and the corresponding “Specify Field…” dialog and paste it into your notes document. If you choose this route, be sure to also copy and paste custom values text so you can easily copy and paste them into the target file.
4. Open the Define Value Lists dialog in the target database.
5. Go through each of the value lists you added here in Step 3 and set them up based on your notes in the notes file.

Using this method is much more efficient than going back and forth between the files to copy each value list separately.

Step 8: Create Blank Layouts

Create a blank layout in the target file for each layout that exists in the source file if it does not already exist in the target file. Do not copy the objects from the layouts at this time. Many layout objects will depend on scripts and other layouts which have not yet been copied. The most important factors at this point are the layout names and the table occurrence.

One efficient way to accomplish this is to open the Manage Layouts window in the both files and position them side-by-side. From there, you can go down the list in the source file and create the layouts in the target file.

Be on the lookout for duplicate layout names and change them where necessary. You want them all to be unique. And, if you change any of the layout names, be sure to do it in both files. They must match.

Step 9: Copy Scripts

Now we’re ready to copy the scripts. At the surface, this process is quite simple, though you will want to spend some time double checking the Import.log file or using the Import Log Interpreter tool.

There are two methods.

• Method one: open the Manage Scripts windows in both files, select and copy the scripts from the source file and paste them in the target file.
• Method two: click the import button near the bottom of the Manage Scripts window in the target file, navigate to the source file, check the scripts to copy and click OK.

Whichever method you use, be sure to copy all of the necessary scripts at one time. Doing so will assure that any scripts that call other scripts will find their subscripts.

Once the scripts are copied, you’ll want to go through the Import.log file to make sure that everything copied correctly. Things to look out for are broken layout, field and table references.

Step 10: Copy Layout Objects and Recreate Layout-Based Script Triggers

This step is one of the more tedious steps in the process. You will need to go through each layout you added in Step 8, and modify the layout in the target file to match those in the source file.

As in Step 8, you can open the Manage Layouts window in both files and position them side-by-side. Then, go down the list and open each matching layout side-by-side.

For each layout, you will do the following:

1. Add and adjust all the layout parts in each layout to match the source file. Using the Inspector, you’ll want to make sure the header, body, footer, etc. are all the exact same size: to the pixel. And create the necessary sub-summary parts to match those in the source file.
2. Copy and paste all the objects from each layout in the source file to the corresponding layout in the target file. Simply select all in the source file. In the inspector window, notice the exact Left and Top positions of the selected objects. Copy. Paste into the layout in the target file and enter the Left and Top coordinates in the inspector window to match what was in the source file.
3. Open the Layout Setup dialog in the source file and check the Script Triggers tab. If there are any defined, add them to the target file as well.

Do this for each layout and then…

You’re Done!

You should now have a single FileMaker file with all the functionality that existed in the two separate files. However, I would strongly recommend you do some serious testing at this point to make absolute certain everything copied over properly.

The Future: Will This Get Any Easier?

This article is based on the tools included in FileMaker Pro 11 Advanced. The ability to import custom functions, tables and scripts is a recent addition to the FileMaker features list. One can only assume that it will just be a matter of time before tools are included to import layouts, value lists and possibly even relationships. But, we won’t know until that happens. So, for now, this is the process. It’s easier than it used to be, but harder than it might someday be.

And finally…

As always, if you have any questions or comments or could use some help, feel free to post a comment below or contact me directly.