Data Migration Master

What Is It?

The Data Migration Master is a tool I created using FileMaker Pro to

• Simplify the process of doing 1:1 data migrations
• Improve accuracy and reliability of migration scripts
• improve error reporting on migrations
• Standardize the format we use for our migration scripts

Why Are Data Migrations Typically Problematic?

Unlike a lot of the big SQL databases available, FileMaker combines both the database and the GUI into a single file. This comes with its own benefits, but also has its own pitfalls too. One of the biggest pitfalls that I’ve seen is that it’s difficult to update systems.

It’s a very common occurrence for us to create a brand new system for a client. We’ll deliver that system to the client, and they start using it. Once they see what it can do, they often come back to us with new features that they’d like added.  Usually, this entails working on an offline copy of the files in order to add the requested feature set.

Once the features have been developed though, there will come a time when the new system needs the new data, and so we have to merge those two items together in what we commonly refer to as a 1:1 data migration.

The major problems that I’ve identified with FileMaker for the purpose of setting up data migrations

1. FileMaker lacks a proper identifier for PRIMARY KEY designations, and instead,  typically rely upon an “auto-enter serial number” property on a field that needs to be updated after a migration
2. FileMaker only allows importing a single table at a time
3. Data Migrations need to be consistent so that the test-run will mirror how the production-run will work

What Would The Perfect Solution Be?

Since it’s going to be primarily FileMaker Developers who will be wanting to create data migration scripts for FileMaker databases, I would picture the perfect solution to this problem as being something in FileMaker that could automate the import process for us.  One of the show stoppers to getting everything working ideally is the fact that auto-enter serial number fields need to be manually updated. 

First of all, they’re not smart enough to know to skip values that have already been used (not a problem in the world of SQL), and secondly, when trying to automate the update of such a process, one cannot dynamically specify the field to update using a formula (you have to pick a specific field from a list).  This basically prevents us from being able to come up with a universal updater solution that we can use for all systems.  Writing a new data migration script for every project we work on though is a very tedious task, and can literally take days to do.

Thinking Outside of The Box

At this point in time, I knew the limitations I faced, and was determined to overcome them. I knew that instead of having FileMaker loop through all of the tables in my system, and automatically perform imports, and updating the serial number fields, that I would need to take a different approach. I figured that since it can’t do exactly what I wanted, that I needed to find the next best thing. The next best thing that I could think of would be if FileMaker Pro were to actually write the data migration scripts for me. Now, this was also a problem, because you can’t have a FileMaker script directly create another FileMaker script.

However, there was a glimmer of hope.

Within FileMaker Pro, there are a number of things that you can copy/paste.  Often times when you use these commands though, the data that you’re copying (or pasting) will utilize FileMaker’s own internal (and proprietary) clipboard instead of just using the underlying operating system’s clipboard.  The information that gets stored in FileMaker’s clipboard is stored in XML format.  FileMaker Scripts are one of those things that get stored in FileMaker’s internal clipboard whenever you copy/paste them. Using a 3rd party tool (FM Butler’s “Clip Manager Express”), I was able to capture data from FileMaker’s internal clipboard, and reverse engineer the XML format that gets used for scripts, script folders, separators, etc.

Armed with that information, I was able to come up with a solution that could generate the XML needed in order to create a whole folder of scripts that would be customized for the data migration that I’m working on.

The Outcome

Writing data migration scripts for FileMaker systems typically takes a long time.  Even if it’s just a 1:1 data migration, it will typically take me a full day of work to write the scripts.

When we did this, we could find an older system that we’ve written data migration scripts for, but we didn’t have a standardized template to work from. Furthermore, these scripts still needed to be hand-written, and were often lacking in error trapping/logging. With this new Data Migration Master, we now have a way to greatly reduce the time investment in writing these data migration scripts, ensure better accuracy with them, add error logging, and even draw your attention to potentially problematic areas within your migration.

This tool was created during development of a system for Crystal Sensations, and I’ve further refined it during development of a couple of other systems.  I used it during my most recent data migration for UHAC, and it saved a ton of time. 

UHAC is a medium-sized system, so it would have probably taken me about a day to write the migration scripts for it. Knowing how to use this new tool, I was able to have a fully functional data migration written in about 45 minutes.

That’s a win in my books.

How It Works

It’s a simple, 12-step program