Claris FileMaker tips, techniques, and news.

By Kevin Hammond  Posted on  March 7th, 2013  in  Claris FileMaker

Upgrading FileMaker Pro, Part 2 - File Consolidation

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 .fmp12. 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 or .fmp12 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 or .fmp12 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.

TIP: If you are using a Mac, install 2 copies of FileMaker Pro. Then run one with the consolidated FileMaker file and one with the non-consolidated FileMaker files so you can easily copy and paste between them.

Add Relationships

Create all Relationships (TOs) 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.

TIP: If you used the Hub and Spoke approach where you are consolidating all files into your largest file, then you need to update any existing TOs that are using an external data source. FileMaker makes it easy to identify external TOs by putting them in italics. Double click the TO and change the data source to the current file and the table you created in the single file and verify the relationship is correct.

Add Value Lists

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

Tip: Consolidate value lists when possible by giving them the same name in all the files. Do this when you see a value list that is using another file value list. Make sure these value list names are the same name as their source value list. When you paste the layouts in a later step, they will all use the same value list.

Note: As of FileMaker 16, you can now copy and paste value lists between files saving you time here.

Fix Fields Errors

Review all the fields in the tables. Use the Import.log file to identify the errors and resolve them. The errors come from importing the tables without having the table occurrences present. Errors will be in the Calculation fields, Auto enter calculations of fields, and the Lookups in fields. The calculations will be commented-out from the importing of the tables and the lookups will be broken. You may wish to rewrite the lookups to auto-enter calculations here as well.

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.

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, as of 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 Metadata Magic Conversion Issue Report will help you if the system came from FileMaker 6 or prior.

Preventing Conversions Issues

The last thing anyone wants is to have the client or user tell you about a problem that is related to your file consolidation. So take the proactive steps below to make the system bug free.

  • Review all the Go to Related Record calls to ensure you are in the correct context/layout. Once consolidated, the Go to Related Record may incorrectly state 'current layout' to the related table.
  • Review all New Record, Duplicate Record, and Set Field calls to ensure a commit record request is present to prevent record locking.
  • Import Records script steps need to be reviewed. They may have pointed directly to one of the files that has been consolidated.
  • Export Records where a Group By is checked by default. This is rare to see, but if the system exports many things to Excel, this is more likely to be seen.
  • Custom Functions, if you're consolidating files from FileMaker 8 or higher, make sure the custom functions are in place and have no errors.
  • Adjust Window Hide. Remove them from the files that have been consolidated, or the main window will hide.
  • Close File, same as above with Window Hide, remove them from the scripts, or the main file will close.
  • 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.
  • Review manage security if you're consolidating to make sure all permissions are in place and work as expected.

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 its 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. For us, this is not optional.


FileMaker file consolidation provides the developer with an ideal environment that leads to time savings when developing. You can save 15-25% of your time working in a consolidated FileMaker application as you reduce the number of redundancies in your application and complexity passing variables and data between files. Keep in mind that separate files do make sense in some applications, though you must use your judgment as to what makes the most sense for your application. Happy FileMaking!

Did you know we are an authorized reseller for Claris FileMaker Licensing?
Contact us to discuss upgrading your Claris FileMaker software.

Kevin Hammond thumbnail
Kevin Hammond

Kevin is CEO and Business Project Manager for DB Services. He also a founding member of the FileMaker Partner Council and certified in FileMaker 18, 17, 16, 15, 14, 13, 12, 11, 10, 9, and 8. Kevin is passionate about FileMaker as a custom application platform and is constantly on a mission to improve how he approaches each assignment.

FileMaker 18 Certified Developer
FileMaker 17 Certified Developer
FileMaker 16 Certified Developer
FileMaker 15 Certified Developer
FileMaker 14 Certified Developer
FileMaker 13 Certified Developer
FileMaker 12 Certified Developer
FileMaker 11 Certified Developer
"We were actually able to add more features than we thought would be possible within our budget. We always experienced a ‘can do’ attitude and DB Services was incredibly patient and easy to work with."
Courtney Hartman
Art Director
"The new FileMaker custom website interface is very user-friendly and easy to follow for our clients. In addition, it is much faster than the old Instant Web Publishing (IWP) interface. Thank you and all the others who have helped us out at DB Services. I am very grateful for the excellent service you provide us. Its nice to know we can call you for help if we have an issue."
Wayne Capek
"We needed a solution that would simplify the administration of our responsibilities under our contract with the State of Indiana. We have seen a dramatic increase across the state in the number of potential foster and adoptive parents that have begun the preparation and training process. This increase has resulted in a significant opportunity for children available for adoption. That’s a big win for everyone! And it’s all because we can refer potential parents to the proper state contacts efficiently and quickly."
Chris Morrison
Executive Director
"Thank you for all of your expertise and valuable help. I am so grateful to have found DB Services."
Linda Findlay
"Thank you for our new database system. We transitioned from a carbon copy paper based system to a digital database. The software has saved us time and money. We used to archive all our jobs in cabinets, now we can look up a job in seconds from anywhere. The software allows us to easily email estimates to our customers in pdf format. In a year there was a return on investment just from eliminating the purchasing of our carbon forms."
Todd Cartmel
"The new system allows us to create and track jobs for customers along with inventory, something we had been doing with separate word and spreadsheet files. Because it’s now so intuitive, new staff members are able to begin using the database immediately without our usual training session and ‘cheat sheets’ for getting around within the file."
Tom Andrews