FileMaker 17: Data Migration Tool - Part One

FileMaker 17: Data Migration Tool - Part One

Colin Keefe, FileMaker Developer, IT Solutions

One of the exciting new features released with FileMaker 17 is the Command Line Data Migration Tool. Exciting to me anyway, because I geek out on technical stuff like this!

To begin, let's first identify two common scenarios for when data migration is needed:

  • Deployment: A FileMaker app is deployed through multiple releases, and you need to pull the data from the previous app into the new version.
  • Revert: You have a corrupted file and need to revert back to a clean backup clone.

Before FileMaker 17, strategies for data migration typically included a scripted import process, which can be time consuming, not to mention comes with some limitations. The new Data Migration Tool (available to FileMaker Developer Subscription members) can help us tremendously with deployment scenario above. And while this tool won't magically clean your corrupt data, it can help speed up the data repair process as described in the revert scenario. 

Given a source file to pull data from, and an unopened clone file, the Data Migration Tool will create a new file based on the clone and copy data (and optionally accounts and value lists) from the source into the new file (by default called <originalfilename> migrated.fmp12).

The DMT (as I've chosen to abbreviate it) attempts to solve the challenges associated with conventional migration strategies by bypassing the FileMaker application layer and copying data (and other supporting elements) directly from one file to the other, with table/field matching criteria that I'll describe further down in this article. 

Advantages

DMT is FAST
Scripted migrations are time consuming and dependent on the Import Records script step. When it comes to large datasets, importing data can take a very long time.  Both large record sets and large table column counts can slow down imports.  In solutions with many large tables, migrations can run hours on end.

The deployment and revert scenarios above typically have constrained time windows in which they need to happen.  

Deployments usually happen after hours.  If a migration runs longer than 16 hours (yes, it can happen), then deployments may have to be scheduled over a weekend - and weekend warriors won't have access to the file during the migration.  These time constraints also give less room to recover from failed deployments - there's no time left to re-run a migration.

DMT copies accounts and passwords
Scripted migrations can't move accounts from one file to another.  In fact, when moving from an older version of a FileMaker app to the latest version, FileMaker scripting can't copy over FileMaker user accounts at all. Prior to FileMaker 17, solutions to this problem usually rely on user tables - tables where every user account has a corresponding record that contains the account name (but no password, for obvious security reasons). 

After importing data you can recreate accounts in the new file - but when you do so you have to supply default passwords.  But the process is messy, error prone and disruptive to users, since they have to change their passwords again.  There are other methods too but they all suffer drawbacks.

DMT has an option to copy FileMaker Accounts from one file to another, which if used correctly can resolve this problem completely.  Users will be able to log into the new solution with the same authentication they used previously, with no "change password on first login" or other forcing mechanisms.

DMT copies value lists
Scripted Migrations can't copy value lists from one file to another.  DMT has an option to copy Custom Value Lists from one file to another.  This is useful in situations where the Custom Value Lists are not curated by the developer, i.e. they can be edited by end users.

How Data Migration Tool Works

The Data Migration Tool is a Command Line Interface (CLI) tool.  As such, you will run it in Terminal (on Mac) or PowerShell (on Windows) by typing commands with specified parameters in a shell window.  The file, called FMDataMigration, sits in a folder you determine, and you call it from a shell.

Here's what the file looks like on a Mac.

If you double-click the file in the Finder, a terminal window will open and explanatory text will print to the window describing the application and its usage, as shown.

On a Windows machine, you can run PowerShell and type (pathtofile)\FMDataMigration to see the same text print to screen:

Let's break this down a bit.

Usage: FMDataMigration -src_path <path> -clone_path <path> [<other options>]

When calling FMDataMigration, at a minimum you need to supply two parameters:

-src_path: the path to the file you're pulling data out of

-clone_path: the path to the clone file you're copying data into.  This file must be unopened.  DMT checks for this and refuses to run with an invalid (opened) clone.

<other options>


Let's try it!

For this walkthrough we'll use Terminal on a Mac.

Step 1

Download this ZIP file* and extract the compressed contents to your desktop.  You should see something like this:

  • Contacts.fmp12 is the FileMaker17 starter Contacts template, prepopulated with a few records.
  • Contacts Clone.fmp12 is an unopened clone of the Contacts.fmp12 file.
  • FMDataMigration is the Data Migration Tool Unix executable we'll be sending commands to.
  • Usage Syntax is a text file with some copyable syntax that, with path modifications, ought to get you started.

Step 2

Find Terminal and launch it.  You can find it easily by launching Launchpad from your dock and searching for it:

Once launched, you should see a terminal window like this:

Step 3

Paste the following into Terminal, after editing the paths to point to your own User folder (assuming your name is not Colin Keefe - if it is, we have some things to discuss):

'/Users/colinkeefe/Desktop/DMT Demo/FMDataMigrationTool/FMDataMigration' -src_path '/Users/colinkeefe/Desktop/DMT Demo/Contacts.fmp12' -clone_path '/Users/colinkeefe/Desktop/DMT Demo/Contacts Clone.fmp12' -mode -v

 

Before you hit enter, your terminal window should look like this:

Step 4

Hit Enter!

Because you used "-v" (Verbose Mode), you should see the following output in your terminal window:

Lots of words.  We'll get to them later.  Let's first look and see if anything actually happened.  Go back to your DMT Demo folder on your desktop. 

You should see a new file called "Contacts migrated.fmp12".

Let's open that file up in FileMaker Pro 17 to see what's in it.

We're done!

That's the process in a nutshell.  There are a few things to note in this specific example.

  • These files were exact matches, which fits a rollback/restore type scenario.  But DMT can migrate into similar but not identical files, which means migration use cases are supported with this technology.  We'll get into that in subsequent articles.
  • Container data copied over!  Isn't that neat?

Further Exercises to Try

Now that you have a good feeling for how this works, it's time for some experimentation.  Here are some things to play around with:

  • Add custom value lists to the source file and run the DMT
  • Add accounts to the source file and run the DMT
  • Encrypt the source or destination file and run the DMT
  • Try pointing the DMT to divergent source and destination files:
    • V1 and V2 of a solution
    • completely different solutions, but with identical table names

Did you find this post helpful? If so, check out Part Two: Building a Data Migration Helper File

We hope this offers you some valuable information about the new Data Migration Tool in FileMaker 17. If you'd like help with the migration of your data or would like to talk with us about upgrading to FileMaker 17, contact us today. 

*The FileMaker data migration tool is only available through a FileMaker Developer Subscription.  In order to work with these demo files in this series, you need to sign up as a FDS subscriber to download the command line tools. Click here to subscribe!

ITS Email Newsletter Archive

© 2018 IT Solutions Consulting, Inc. All rights reserved. Privacy Statement
IT Solutions
Top