How To Merge FileMaker Files

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.

Note: This article is based on FileMaker Pro 11 Advanced. If you have an older version of FileMaker or do not have the Advanced version, you may need to work a little harder.

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.

Important Tip: Whenever you import or copy functions, scripts, script steps, tables or fields from one database file to another, FileMaker will create a file called Import.log. For local files, it will place this file in the same folder/directory as the database file that is being copied or imported to. For remote files, it will place the file in your documents folder (ever wonder why those little Import.log buggers keep appearing in your folders?).

Open this file and scroll to the end to see the results of your import and whether any errors had occurred. Alternatively, you can use the Import Log Interpreter tool to easily browse through the import results, see exactly what was imported and see the errors associated with each object in an easy to read format.

I can’t stress enough the importance of reading the little Import.log file, whether you do it manually or using the Import Log Interpreter tool. It will be you’re friend throughout this process. Although, you can check all your calculations and script steps manually, not only will that take you a really, really long time for any complex database, but the likelihood is very high that you will miss at least one little problem that produces troubleshooting headaches for you in the future.

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.

Alert: You may be tempted to glaze over this step, but don’t give in to that temptation. One small mismatch can break your buttons, layout objects and scripts. Believe me when I say that, fixing these problems now is much easier than fixing them after you’ve copied your scripts and layouts over, even if that means backing up a few steps before moving on.

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.

13 Comments

  1. ron
    Posted February 19, 2012 at 3:43 am | Permalink

    Good article.

    How would you do it in script?

  2. Posted February 20, 2012 at 11:37 am | Permalink

    @ron – I’m not sure I understand your question. It wouldn’t be possible to script the process of merging two files. I don’t know why you would want to. What do you want scripted?

  3. ellen reiser
    Posted July 24, 2012 at 2:46 pm | Permalink

    is the import log reader app going to be updated for fm12?

  4. Posted August 21, 2012 at 5:34 am | Permalink

    Crikey this looks like a lot of work for not much gain…

    I’m just wondering – I’ve got two databases; one listing inventory items, and one listing the production process and the ordering system for raw materials. Things like pH electrodes and dosing pumps.
    Ideally I’d like to populate the ordering fields of database 2 with the component information and pricing information from database 1.
    I was thinking of bringing the two databases together but looking at the above this seems quite complex. Can I pass a variable like $$PARTNUMBER from Database 1 to database 2 using a script?

  5. Posted August 21, 2012 at 9:40 am | Permalink

    @awe- For your purposes, it not necessary to merge the files, you can access the data and scripts of one file from the other by simply adding the other as a data source. Variables are not persistent from one file to another, but you can easily call a script in one file from the other and pass a script parameter.

  6. Posted August 21, 2012 at 10:19 am | Permalink

    Thanks Danny,

    So if I understand this correctly:-

    In database 1 if I have 4 fields – db1_field_1, db1_field_2, db1_field_3 & db1_field_4.

    In database 2 I have db2_field_1, db2_field_2, db2_field_3 & db2_field_4.

    I can use the value of db1_field_1 to lookup a value in db2field_1 by using a script to set a variable e.g. $$field1 using the value of db1_field_1 and scripting a perform find in db2_field_1.

    Then I would populate the fields d2_field_2 and db2_field_3 from the values of db1_field_2 and db1_field_3

    Am I correct in thinking that the script can be executed from Database 1?

    On a slightly different note – db2_field_1 is a is a name and db2_field_4 is a serial number generated on the record creation. Database 2 predates database 1 by a long way and rather than manually enter the corresponding serial number in database 1 I was wondering if I could auto populate db1_field_4 based on the value of db1_field_1

    So – if db1_field_1 should be the same as db2_field_1 then is there a way of making db1_field_4 equal to db2_field_4 when db2_field_1 equals db2_field_4 without using a calculation field and manually entering the various calculations (e.g. = if db1_field_1=”x” db1_field_4=”a” if db1_field_1=”y” db1_field_4=”b”.

    I was thinking about creating a new table in DB1 where the corresponding values listed db1_field_1 db1_field_4 and then using a lookup chart but I’m not sure whether this is correct or not…

    Sorry about all the questions – hopefully other folks will find the answer useful too!

  7. Posted May 28, 2013 at 12:02 pm | Permalink

    I’m not sure I would call this process “merging.”

  8. Posted May 28, 2013 at 12:06 pm | Permalink

    What would you call it, Jim?

  9. Posted May 29, 2013 at 3:10 am | Permalink

    I would call it really difficult…

  10. Posted May 29, 2013 at 8:18 pm | Permalink

    I won’t argue with that, awe.

  11. Todd Mitchell
    Posted August 12, 2013 at 5:54 pm | Permalink

    Greetings, I am new to Filemaker, and I defined several datasets. By that I mean that the “Layout:” in the ruler above the records only shows one dataset. How do I merge 5 datasets containing different information into a single dataset. Thank you!

  12. Miss A
    Posted September 26, 2014 at 2:51 am | Permalink

    Hi there, got here looking for solution to attach 2 or more pdf files as email attachment in filemaker – One file is layout converted\saved as pdf, the other file exists in a folder…..i would like to attach both files to one email through script, is this possible – please can you give me way?

  13. Rebecca
    Posted October 11, 2014 at 10:30 am | Permalink

    Thank you for this. Very helpful. I’m in the middle of cleaning up a huge, old solution, and really need this info to keep me on track. Thanks.

2 Trackbacks

  1. By Filemaker tips by geb - Pearltrees on December 15, 2011 at 11:24 am

    [...] 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. How To Merge FileMaker Files – FileMaker Inspirations [...]

  2. [...] on filemakerinspirations.com Like this:LikeBe the first to like [...]

Post a Comment

Your email is never shared. Required fields are marked *

*
*