FileMaker 17: Data Migration Tool Overview Part Two

FileMaker 17: Data Migration Tool Overview Part Two

Building a DMT Helper File

Robin Story, FileMaker Consultant, IT Solutions

As mentioned in Data Migration Tool Overview: Part One, the Data Migration Tool (DMT) in FileMaker 17 uses the Command Line Interface, which means that the whole process could involve a pretty intricate set of instructions typed into the shell. And the longer a shell command gets, the easier it is for a typo to cause the whole thing to fail. And who needs that frustration? Is there a way to build a Graphic User Interface (GUI) skin for the DMT so that the commands are processed automatically?

The answer is: Yes! For both Mac and PC!

Step 1: Set Up Your Workspace

To get started, we’ll need to set up a working directory.  We’ll keep the DMT executables here, keep our FileMaker front end in it, and output results to this directory.

First, let's create a folder called "Data Migration Tool Helper." This folder can be placed anywhere on your system, but for this demonstration, we'll go ahead and drop it on the desktop. Inside of this folder,  let’s add two FMDataMigrationTool folders – one for the Mac executable and one for the PC executable. In order to distinguish them, I'm going to name the PC folder FMDataMigrationTool_WIN and leave the mac folder named as just FMDataMigrationTool.

Next, we want to create a FileMaker file in our Data Migration Tool Helper folder. We're going to call it .... you guessed it... Data Migration Tool Helper.fmp12

We only need one table to get the basic functionality down, and we're going to start simple, with a source and clone file that are unprotected and unencrypted. So for our demo, all we need is the source filename (Contacts.fmp12 ) and the clone filename (Contacts Clone.fmp12), and then a text field to store the results.. We're going to assume that both source and clone files will be in the Data Migration Tool Helper folder, and that the migrated file will be placed in the same folder. Once we get this working, we can build the rest of the solution out from there:

Here's what that folder for the Data Migration Tool on our desktop looks like:

As you might have already guessed, FileMaker is going to handle the actual run of the DMT via script, so the next step will be to build that script:

Step 2: Build Your Script

Find out what platform you’re running

The important thing to point out as we get started is that the syntax for running the Data Migration Tool is identical between both Mac and PC. The difference between them is how the Mac and the PC handle Command Line paths, and the subsequent results.  So the very first thing your script should contain is some way to determine what platform you’re currently running on. To keep things simple we’ll just set a variable to 1 if the platform is Mac:

Set Variable [$isMac; Value: Get(SystemPlatform) = 1]

Build file path to Source and Clone

As mentioned above, we are assuming both source and clone files will live in the same directory as our FileMaker front end file.  This helps us determine file paths automatically because we can use Get(FilePath) in our front end file to get the path to the parent directory, and build a complete path by combining the parent directory path with source and clone file names.

Get(FilePath) gives a “filemaker” style filepath, however, which won’t work in the DMT command line, so we will need to translate the output of Get(FilePath) into platform-ready variables:

  • To "de-FileMaker" the path, the file:/ prefix (including "Macintosh HD" for Macs) will need to be removed, and for windows, the forward slashes (/) will need to be replaced with backslashes (\). We also need to isolate the path from the output of Get(FilePath) and remove the trailing filename, since we’re going to replace that with a Source or Clone file name.
  • When indicating to a Mac a full file path that may include spaces, a single-quote (') is used to contain the file path (eg: '/Users/ckeefe/Desktop/Data Migration Tool Helper') whereas for Windows, a double-quote (") is used (eg: "C:\Users\rstory\Desktop\Data Migration Tool Helper") 

This means that we have the following variables:

  • $isMac (see above)
  • $q this is a shortcut variable that gives us a single quote (') for Macs and a double quote (") for PCs. 
  • $filesLocation this is the fully-qualified shell path to the folder that the DMT file is in without quotes (eg: /Users/ckeefe/Desktop/Data Migration Tool Helper/)
  • $scriptLocation this is the fully-qualified shell path to the Data Migration Tool script, quoted. (eg: "C:\Users\RStory\Desktop\Data Migration Tool Helper\FMDataMigrationTool_WIN\FMDataMigration.exe")

Build Parameter String for the DMT command line

Next, we'll want to go ahead and create a $parameters variable to store our DMT command's parameters in. We're going to start with just the barest of bare-bones by just having the source and clone path, and also indicating that we want it to run in verbose mode for maximal feedback. Since these parameters are cross-platform, we can put this in one place and not need to write it out twice, which is handy!

Set Variable [ $parameters;
     " -src_path " & $q & $filesLocation & Data Migration Tool Helper::Source_FileName & $q &
     " -clone_path " & $q & $filesLocation & Data Migration Tool Helper::Clone_FileName & $q &
     " -mode -v"

Building the Script
With the parameters in place, we can go ahead and start constructing the script itself.  Let's start with the Mac version since that's a bit simpler:

Perform AppleScript [ 
     "set mySlug to do shell script \"" & $scriptLocation &
     & ¶ & 
     "tell application \"FileMaker Pro Advanced\"¶set cell \"Result\" of current record to mySlug¶end tell"
     & "\" "

What this is doing is telling applescript to run the FMDataMigration shell file in the shell with the provided parameters, and to take the result of that command and place it in our "Result" field of the current record.


For Windows, it's a bit more complicated, simply because Windows doesn't have the ability to return the result directly to FileMaker, so we have to jury-rig this a bit. We can do this by routing all of the output into a text file using the > operator at the end of our command. 

Send Event [ "aevt"; "odoc";
     "cmd /c \"" & $scriptLocation &
     $parameters &
     " > " & Quote($filesLocation & "result.txt") 
     & "\" "

This tells Windows to run the FMDataMigration.exe file with the supplied parameters, but rather than spit the output to the command line window, it will route that information into result.txt, which we can then pull into FileMaker using an Insert from URL script.

The immediate problem is that FileMaker won't wait for the command to complete execution before moving on to the next step. So in order to get that sweet result data, we have to wait for result.txt to appear in our $filesLocation directory.

  • We add a container field (we can stick the field itself off the layout's printable area to still give the script access to it) and use an Insert File script step to insert result.txt to the file. 
  • We Loop 1 second pauses until we see that file pop into place
  • What this means is that we have to delete result.txt from the $filesLocation directory before we run the script to prevent a previous run's success from making FileMaker think that the script is done executing. We can do this by clearing the container field, and then using Export Field Contents without specifying a field target to result.txt. Note–this will result in an Error 102, but since we're trapping errors we can account for this without too much difficulty.

Once we've confirmed that the file exists, we can use Insert from URL to pull the file's contents into our result field:
Insert from URL [ Select; With dialog:Off; Data Migration Tool Helper::Result; 
     "file:///" & Substitute($filesLocation; "\\"; "/") & "result.txt"; 
     Do not automatically encode URL 

(Yes, we're re-reverse-engineering those backslashes into forward slashes since we're using the FileMaker path spec instead of the Windows path spec!)

Now our script, a tidy 42 lines with whitespace, performs a basic data migration on either a Mac or a PC:

And when we run it:

Not bad for a minimally viable product! Of course, this is hardly a production-level system. It is in desperate need of error trapping and data validation, and that $parameters variable can be spun out to include things like security layer and encryption and some of the other optional parameters mentioned in the previous article. But this is a solid foundation to build off of.

Check out Data Migration Tool: Part Three to learn more about the business rule considerations and best practices of migration management.

Download your copy of the Data Migration Tool Helper file!

ITS Email Newsletter Archive

© 2019 IT Solutions Consulting, Inc. All rights reserved. Privacy Statement  |  Site Map