Upgrading FileMaker Pro, Part 2 - File Consolidation

Kevin Hammond – September 2, 2009

This is part two of a two-part article tackling the challenge of converting and consolidating FileMaker Pro .fp3 and.fp5 file types to .fp7 and above. In part one, we covered the FileMaker Conversion process. In part two, we'll cover the process of Consolidating FileMaker Files.

Consolidating FileMaker Files, Tables First

Start the consolidation process by first putting all the tables into a single file. You do this by importing all the tables from each file into the single .fp7+ file solution (requires FileMaker Advanced to copy/paste tables, use FileMaker 8 Advanced or greater).

One of the new features in FileMaker 7+ is the ability to add timestamp fields for creation and modification. If you like, go ahead and add those fields now. Also, if you want to create a script to import records from the .fp7 files into the main file, you should do that now, too.

While pasting your tables, an import.log file will be created by FileMaker. Save this log as you will need to fix any errors from lookups to auto-enter calculations that utilize relationships not currently in the solution.

Add Relationships

Create all Relationships (TOGs) from the individual files you are integrating into your new consolidated file, naming the relationships exactly as the individual files relationships are named. Be sure to keep any prior relationship rules (sort, del, cre) present.

Import.log

Review the import.log file and Edit fields of all Tables imported that generated errors. The errors will include commented-out fields (ie. calculations etc) and broken lookups.

Add Layout Names

Create layouts with the exact name of the layout in the individual files you are integrating into the new single file solution. Don’t copy/paste layouts yet. First, name layouts identically to those in your current FileMaker version, remember to set layout type (form or list), and set fixed print margins in pixels to ensure identical layout setups.

Add Valuelists

Add all Value lists used in .fp5 files to single .fp7 file.

NOTE: You cannot import value lists in FileMaker as of version 10 (although that would save us time here).

Import Scripts

Import all Scripts from each file. Look for import errors (they will be saved to the import.log file), typically from identical script names or relationship names not being identical. Rename the scripts as needed, ensure the relationship names are correct and then delete the scripts you imported with errors and repeat the import until absolutely zero errors are present upon import.

Copy and Paste Layouts

Copy and Paste layouts from each file into the appropriate layout. Make sure any buttons on the layout are now linked to the correct scripts and fields are linked to the correct tables/relationships. This is where all the hard work of conversion and consolidation comes together and why it is so critical to ensure unique names for relationships and scripts. The only bad news here is the tab order will have to be reset for these layouts, the good news is now in FileMaker 10 tab orders are much easier to adjust.

Tab Orders

Set tab order for each layout that was copied/pasted into the consolidated file(s). Update Date Fields to use drop down calendar.

External Scripts Now Internal Scripts

Consolidate all external script references into the correct file and select the same script name. The MetadataMagic Conversion Issue Report will help you as well.

Review MDM Conversions Issues Report

"Review/Update Go to Related Record" calls to ensure you are in the correct context/layout.

"Review All New Record, Duplicate Record, and Set Field" calls to ensure a commit record request is present to prevent record locking.

Any field validation may need to be reviewed as in .fp7 field validation occurs when any field is modified and the field may not be present on the layout.

Test, Test, Test

In consolidating files, the script consolidation rewrites and testing of solutions is 80% of the development (e.g. Print Scripts, consolidating navigation).

Custom Menus (Optional)

Consider using Custom Menus to improve your application and the overall user experience. When you use Custom Menus, you should first (and always) remove the Delete Found Records menu from Records menu set. Next, you should further customize the Records menu set further for each area of your application to help improve the user experience. For example, in the Contacts area of your application, you can rename "New Record" to "New Contact". You can then continue reassigning each layout to their own appropriate custom menu as you see fit.

DDRs and Third Party Developer Tools (Optional)

Run DDRs and tools like Goya's Base Elements or Inspector to fix any errors that exist.