FileMaker Consolidation - merging multiple files

Will LovingPresident, Dedication Technologies, Inc.
CERTIFIED EXPERT
Professional FileMaker Developer since 1992.
Published:
Updated:
Conversion Steps for merging and consolidating separate Filemaker files

The following is a step-by-step guide for the process of consolidating two or more FileMaker files (version 7 and later) into a single file with multiple tables. Sometimes these files have been created separately intentionally or by someone who wasn't aware of the ability to have multiple tables within the same file, and sometimes such files originated as FileMaker Pro 6 or earlier files which were always one table per file.

This guide does not delve into the important issue of re-design and optimization of the database structure to take advantage of the consolidation, which should be an integral part of such a consolidation process. It simply addresses the how-tos of the process so as to accomplish the consolidation with as little 'breakage' as possible. The procedure list or order of steps below is my own summation of the process we worked out that would allow us to accomplish the process most quickly and spend as little time as possible doing repairs.

With regard to consolidation, in to any serious FileMaker development work in general, I strongly recommend the use of: FM Perception, Inspector Pro, or a similar utility to assist with this process. Using the Database Design Report available in FileMaker Advanced, FM Perception, Inspector Pro and similar utilities allows you to easily see where a script, field, layout, external file, etc. is referenced – or not. Which can be very helpful when you are trying to get rid of excess baggage OR figure out what got broken when you moved or deleted something. You can just use the Database Design Report but I find Inspector’s visual interface and search functions very helpful.

A. Initial steps BEFORE merging

1. Primary File: Decide which is going to be the master file, which might be obvious or not. Generally speaking you want the file that has the most layouts, scripts and tables already to be the Master file that you are merging the others into.

2. Layouts: If there are lots of layouts, you may wish to rename Layouts in original files with a 1-3 character prefix such as "INV" for Invoices or "CON" for Contacts. You can do this manually or I use QuicKeys if there are really a lot. It’s important to make sure that each layout has a unique names in the final file and the names should be changed BEFORE merging.

3. Relationships: Rename Table Occurrences (TOC) so that wherever possible the TOC has the same name in both files, so for example, if there are two files, Contacts and Invoices, and you have a relationship in each file to the other, make sure the names of the Table Occurrences are the same. If there are multiple table occurrences for the same table then choose your TOC names logically. (see note below)

4. Scripts: Rename scripts so that the script names are unique before merging files. If you have a large number of scripts (hundreds or thousands) consider sequentially numbering all scripts in each of the original files, e.g. "Print Invoice" becomes "078 Print Invoice". I do this using a looping set of steps (aka a macro) in QuicKeys. It’s obviously optional but can very helpful is there are many scripts coming from many tables. If you do number your scripts, consider the order that you want them to appear in the consolidated file and apply numbering to each (unconsolidated) file in that sequence.

5. Check for File References (Manage -> External Data Sources) for old or unnecessary references, but play carefully. Deleting a reference without checking it in Inspector or the DDR for usage can result in non-functioning scripts, etc.

------------------------------------------

B. The Merge Process

1. Import Tables and Data: Open the Master file and Import Tables the tables from the files you are merging in. You can do this one of two ways: 1) using FM’s import table function, which just brings in the fields, or 2) go to File -> Import Records, select the file and table to import, and then in the upper right popup menu, select “New Table”. This second option import both the table and the data with it.

Import tables in order from least to most important to reduce breakage. For each import, a new layout and Table Occurrence will be created. In some cases you will want to delete both of those. In the example above of Contacts and Invoices, you don’t need an extra TO for Invoices since one already exists in the graph, it’s just pointed at the external file (indicated by the fact that the name is in Italics). Once you have the Invoice table imported, you can double click on the old Invoices TO and change the reference for the table for the External file and table to the new internal table in your Master file. If there was no relationship in the Master table to the imported one then you can just leave the TO on the graph.

2. Fix the Relationship Graph: Once all your tables are imported, fix your Relationship graph to match the relationships in the original files. Depending on the complexity of the system you may create relationships now or later. If there are lots of calculations that depend on existing relationships you may have less breakage if you add relationships as you add tables.

If there are just a small number of files, then it’s possible that there is only one Table Occurrence Group (TOG) with all tables related to all other across the group. However, in larger systems this will probably not make sense and instead it is best to have multiple TOGs, each centered around one of the primary TOCs like “Contacts” or “General_Ledger”. (also known as “spoke and wheel” arrangement). In this case using a naming convention that prefixes all the related tables with the name of the ‘starting’ table will make sense. E.g. all TOCs in the Contacts TOG will start with “Contacts_” as in “Contacts_Invoices” and “Contacts_Payments”. If the Invoices has it’s own TOG then it’s central TOC will be Invoices and it will have a TOC called “Invoices_Contacts”.

When creating new relationship between TOCs, be sure to capture any Sort Order and Record Creation/Delete options that may exist in original relationship.

------------------------------------------

IMPORTANT: The next steps are very import to do in order. Failure to do so will usually result in lots of broken references on your layouts.

3. Value Lists: Re-create all value lists in all original files. If there are similar value lists with slightly different names, make the names the same BEFORE importing the actual layout objects. If there are similarly named value lists with different value sets give them different names BEFORE importing them into the Master file.

NOTE: It is a critical part of this process that you recreate layouts in the order and manner described in the next three steps. Do NOT add fields to the layouts you create in Step 4, simply re-create with the exact name of the layout in the other file but NO fields. In Step 6 you can copy and paste all layout objects from the original.

4. Layouts, Part I: Create Placeholder layouts (no fields) by exact name in Master file. Layout name, margins, part size (in pixels) and sub-summary sort order should all match the original file layout. This is usually the most time consuming part of doing a consolidation. Using duplicate layout can help speed things up. Resist the tempation to copy the fields in at this point. You can use Layout groups (folders) as well as dividers to organize your scripts.

5. Import scripts from each file into new Master file. Create Script Groups (folders) for each set of scripts imported from separate files.

6. Layouts, Part II: Select All and Copy/Paste all layout objects on the original layout in the original to the new placeholder layout that you already created. Be sure to check Viewing and Printing Options and Margins for the layout. If fields are appearing properly, check that the “Show records from” option is listing the correct table occurrence.

------------------------------------------

C. Post-merge Cleanup and fixing Script "Breakage"

If you've done the above in order, your cleanup should mostly be limited to issues within scripts as detailed below. If you find that you have layouts with <field missing>, <table missing> or broken buttons, then you missed a step along the way. With regard to scripts, there are two main issues that occur, both having to do with fixing External References to other files. Although I list them separately below, in practice, I generally fix them at the same time.

1. Fixing External Script References: There are three primary places that script references can get broken: 1) scripts in your primary file that contain an "External" script reference, meaning a reference to another script in one of the files you are consolidating into the primary; 2) scripts in your non-primary files that reference a script in another file (aka an "External" script); and 3) Go To Related Record scripts steps that use and "External" reference, meaning refer to a table in another file. Each of these must be fixed and using the DDR (Database Design Report) and/or a tool like Inspector, can be particularly helpful in fixing these or hunting for references.

Check each script that contains a Perform Script to make sure that it references the imported script in the consolidated file rather than the External script in the old file. Likewise, for each "Go To Related Records" script step, make sure that it references a layout in the new consolidated file. When you revised your Relationship graph the table reference should have switched to the new internal table that you imported in, but the Layout will not be correct.

While you are doing this, it's best to keep your old files in the same folder as the new consolidated file. But if you want a quick check to see whether you missed anything, try removing those old files and open your new consolidated file by itself. You'll quickly find it complaining if you've overlooked references to files that are no longer available to it.

2. Adding New Window/Go To Layout/Close Window Script Steps: Before merging your files, if you ran a script that used a Go To Related Records step or called a sub-script that was in another table (file), then that file would open or or it's window become visible/maximize, the script would run, and then you would usually use a "Hide Window" step to remove it from view. Since all tables are now together in your new consolidated file, you will want to add some additional script steps to deal with the fact that a new window (the separate file) no longer opens at those points in your scripts.

For example, your main Contact file may have had a Report script that included a Perform Script in the Invoices file:

Perform Script [ "Print Invoice" from file: "Invoice" ]

If you have changed your relationship graph so that the table occurrence of the "Invoice" table now references the imported Invoice table, that script step should now read simply: Perform Script [ "Print Invoice" ] because it refers to a script that is now internal to the consolidated file.

Since this script no longer references a separate file, you will need to open a new window and go to the correct layout to perform the actions of the script. To do this add the following steps to the beginning and end of the script:

New Window [ <the window name> ]
Go To Layout [ <name of layout here> ]

....existing script steps...

Close Window  [ <the window name> ]

You could of course also use Go To Layout [ <name of layout> ] and Go To Layout [ original layout ] but the advantage of opening a new window is that you can perform whatever operations you want in the new window while keeping the user's original window completely intact and unchanged. When your script is done - the report is previewed, printed, whatever - then just close the Window.

Likewise, if you have a Go To Related Records script step, you can use the "New Window" function to create and name a new window either on or off-screen, perform the rest of the script and the close the window.

Going further, you will find that many of your scripts can now be consolidated. In the example above, the "Print Invoice" script in Contacts might look something like this:

Go To Related Record [ Show only related records; From table "Invoice"; Using layout: "Invoice - Print" (Invoice); New Window ]
Perform Script [ "Sort, Preview, Print Invoice" ]

If the separate script called "Sort, Preview, Print Invoice" ] is only used by the the "Print Invoice" script, then you can simply copy those script steps into the primary script and delete the script step and the no longer needed script. For frequently used sequences such as:

Print Setup [ Restore; No Dialog ]
Enter Preview Mode []
Adjust Window [Resize to Fit]
Pause/Resume Script [indefinitely]
Print []
Close Window[]

See the note below on creating a set of modular Master Scripts. Using master sub-scripts such as the one above for Previewing, Printing and Closing a report, can hugely simplify your scripting.

------------------------------------------

D. Followup & Optimization

1. Navigation: When consolidating a number of tables/files, it’s usually a good idea to create or revise your navigation system since layouts and script lists will have become much longer. This can be as simple as you like, but at minimum a simple button bar across the top of the primary user layouts which takes the user to those layouts can be very helpful.

2. Custom Menus: Similar to providing a Navigation bar, using Custom Menus, you can create an organized “Reports” menu that keeps users out of the Scripts menu and only shows them what they need to see. You will need FileMaker Advanced to edit Custom Menus.

3. Master scripts: Many people doing consolidations find that after the consolidation they can gradually reduce the number of scripts used by 40-60%. In one case, where I was consolidating a system that was originally in FileMaker 6 and had a complete set of Navigation scripts in each file, I was able to reduce over 120 Navigation scripts to a single script in combination with a couple of global repeating fields to hold primary layout names and button types to use.

One key way to reduce your scripting overhead is to create a set of Master scripts for common functions and which can be re-used in multiple places. Some of my Master scripts include:

  • RTN: Preview/Print/Close – US Letter, Portrait
  • RTN: Preview/Print/Close – US Letter, Landscape
  • RTN: Preview/Print/Close – A4, Portrait
  • RTN: Preview/Print/Close – A4, Landscape
  • RTN: Print Setup – US Letter, Portrait
  • RTN: Print Setup – US Letter, Landscape
  • RTN: Print Setup – A4, Portrait
  • RTN: Print Setup – A4, Landscape
  • RTN: Create New Window OFF-screen {Script Parameter = New Window Name}
  • RTN: Create New Window ON-screen {Script Parameter = New Window Name}
  • RTN: Center Window On-screen

The last four could be consolidated to a single script by creative use of script parameters, but you get the idea. My master script set is about 50-60 scripts which do various standard functions. I even have a set of generic Find scripts which work from any layout as long as my Layout naming conventions are consistent between Form, List and Find layouts. (The "RTN:" means "Routine". I also use the prefixes "BTN" for Button, "RPT" for Report, "ST" for Script Trigger and "MENU" for Custom Menu scripts.)

© 2020, William M. Loving - will@dedicationtech.com
Reproduction permitted as long as attribution is retained.
5
12,484 Views
Will LovingPresident, Dedication Technologies, Inc.
CERTIFIED EXPERT
Professional FileMaker Developer since 1992.

Comments (1)

If you need help with the just the boring mechanical process of combining the files together into one single file as described in Step B, the FmPro Migrator Developer Edition software can help with this part of the project.

But the pre-consolidation and post-consolidation tasks listed in this article are very important - and cannot be automated with any software. I have read about solutions having 150 pages of scripting reduced to 2 pages as part of the merging process.  We have lots of ways to reduce code with newer FileMaker versions, like passing parameters and just basically not having to have little bits of code scattered among separate files will help a lot.

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.