BlogFileMaker

Introducing FileMaker 17’s FMDataMigration Command Line Tool

By May 15, 2018 10 Comments

An Overview of FileMaker Development

Since FileMaker’s inception, it has been a binary file with its own proprietary format. As programming languages evolved and the world wide web became the dominant platform, open programming languages have thrived. Most programmers work in text editors and with specific file formats such as .html, .js, .php, etc. These formats make it easy to compare one version of a file to another. They have allowed version control services such as GitHub to flourish. This approach also provides developers with an easy way to deploy their solutions.

Former FileMaker Deployment Methods

Traditionally, you could deploy a new version of a FileMaker file using a few different methods. You could split a solution into a data and a UI file or write a FileMaker script to import data from the live production file.

With data and UI files, you may find challenges with adding new tables or fields as FileMaker doesn’t truly support a separation model at this level. However, with repetition and experience, you can overcome them to build vertical market solutions in FileMaker.

When ready to deploy, you simply replace the UI file in production with some caveats. When new fields, value lists, tables or table occurrences are created, you need to update the data file in some cases. This is a linear, time-consuming process. You can easily miss steps or perform them out of order, causing problems after deployment.

Potential Data Migration

With an import script, you can automate a data migration process. However, this turns into a slow process with large data sets and indexed fields. It involves writing a script using the “Delete All Records” and “Import Records” script steps in FileMaker for each table. This process works well in a custom development solution with a single FileMaker file and is generally easy to understand.

If the solution uses sequential primary key fields, maintaining and updating those sequences is crucial. You must account for them in this scripting process by using the “Set Next Serial Value” script step in each table. Both methods have their drawbacks, but they do allow for deployment on the FileMaker platform.

With the release of FileMaker 17, FileMaker solutions still remain as binary files. However, my team and I see steps toward easing deployment challenges.

FMDataMigration: An Introduction

The FMDataMigration executable is a command line tool that can dramatically speed up the data migration process. It provides a text-based command syntax that includes options to meet most deployment needs. You can use this tool to replace or supplement your existing data migration process when deploying changes from a production file.

Running this tool requires a full access user account or an account using the “fmmigration” extended privilege. This extended privilege does not require access to any table, scripts or layouts and thus provides a secure method for migration. Both the source and clone file must have matching account names (case insensitive). Non-full access users will only see a summary log upon migration completion. Only full access users have the option for normal or verbose logging.

FMDataMigration Tool Versions

There are separate versions of the FMDataMigration tool for Windows and macOS. You can only download them with a $99-per-year FileMaker Developer Subscription. FDS also includes a FileMaker Server development license and access to the iOS App SDK — great value for the price.

Key Benefits: Speed and Integration

The key benefits to this tool are speed and integration. In my testing, it takes about the same amount of time for this tool to process a database with 10 records as it does to process the same database with 10,000 records. Your results may vary depending on the number of fields, tables and records, but this tool is fast.

This benefits developers working with very large data sets. The FMDataMigration tool presents a huge time saver with an offline production system. Since this is a command line tool, you can use free plugins such as BaseElements or MBS FileMaker Plugin to issue system commands (Terminal on the Mac and Command Prompt in Windows) to perform this migration right from within FileMaker. Doing so can simplify and structure the deployment process for less experienced developers.

FMDataMigration does not create a log file automatically but will output results to standard output via the command line. If you have experience using the respective command prompts, you can easily redirect the output of FMDataMigration to a log file location. In our demo file, you will see this process used to bring the output log back into FileMaker for review.

Backup Recovering with FMDataMigration

FMDataMigration also recovers from a “last known good backup” when file corruption occurs. It allows you to import data from your recovered file into a clone of a “last known good backup” file. This provides a faster method over traditional data import methods, ensuring you don’t use a damaged file after recovery.

FMDataMigration Tool Caveats

FMDataMigration goes a long way to solve some of the challenges developers face during deployment but comes with its own caveats. For instance, developers with little experience with the command line tool may find it intimidating.

Outage Scheduling

I must note: both the source and target file need to be closed locally (not open in FileMaker Pro) and taken offline (if hosted with FileMaker Server). In some larger development environments, this means you’ll need to schedule an outage.

Cloning the Target File

The target file must also be a clone. This has a specific meaning in FileMaker. You can make a clone file navigate to the “File” menu and select “Save a Copy As…” in FileMaker Pro. In the ensuing save dialog, choose the “Clone (no records)” option.

You can also create a clone file as part of a server scheduled script from the FileMaker Server Admin Console. Part of what makes a clone file special is the absence of localization configuration and any data. Once a clone file is opened, localization is set, and the file is no longer considered a clone by FileMaker Pro. If you run this tool on a clone that has been opened, it will show an error that says, “The specified file is not a clone.” This tripped me up the first few times I attempted to migrate with this tool, so be aware of this requirement.

Here’s how to complete development changes in a separate development copy and then create a clone of your development file to use in conjunction with FMDataMigration.

Deployment model diagram for FMDataMigration tool

Deployment model diagram

Setting Migrations

There are a few other aspects you should keep in mind with this tool. In addition to moving table data, this tool will also migrate user accounts and privilege sets from the source file to the migrated file. This means it will override any accounts or privilege sets in the clone file, with those in the source file, by default. There is an optional flag “-ignore_accounts” in the command syntax that will turn this behavior off.

External Data

Your externally-stored container data is neither converted, copied or moved. You must move the externally stored container folder hierarchy after completing the migration process. Or, you can replace the source file with the migrated file in the source’s server environment using FileMaker Server’s Admin Console.

Production File Changes

Lastly, be careful with urgent or “hot fixes” in your live production file while actively developing inside your development file. Schema changes such as scripts, layouts, field names, tables and table occurrences, are not merged from your production file with your development file.

If you must make urgent changes on the production file, you must make the exact same changes inside your development file, to ensure you don’t lose these changes after using FMDataMigration.

FMDataMigration Command Syntax

Let’s now explore the command syntax. Take these steps to follow along:

  1. Download the executable file
  2. Open your command prompt (Terminal on the Mac and Command Prompt in Windows)
  3. Change the current directory to where the executable file is downloaded
  4. Type in the name of the executable
  5. Hit enter

You will see the documentation and version number for this tool:

FMDataMigration 17.0.1.125 (02-28-2018)

Description: Migrates all record data from a source FileMaker Pro Advanced file to a copy of a clone file.

Usage: FMDataMigration -src_path  -clone_path  []

-src_path 
-src_account  (default is Admin)
-src_pwd  (default is blank)
-src_key 

-clone_path 
-clone_account  (default is Admin)
-clone_pwd  (default is blank)
-clone_key 

-target_path  (default is source path with " migrated" added)
-force (overwrite existing target file)
-ignore_valuelists (use custom value lists from clone instead of source)
-ignore_accounts (use accounts and decryption key from clone instead of source)
-ignore_fonts (assume no font mapping required for field contents)
-v (verbose mode)
-q (quiet mode)

On the “usage” line, as a bare minimum, we need to specify a source path and a clone path for this process to complete successfully. The source path points to your original file containing the current production data. The clone path is the blank clone containing your development changes.

We also have additional options that we can tag onto this command. We can provide the account name and password, along with an encryption key if we use Encryption at Rest. We also have some “ignore” options. Additionally, verbose and quiet mode specify varying levels of logging and can help determine the cause of errors.

Soliant Data Migration Tool

Soliant Consulting Senior Systems Architect, Wim Decorte, put together a FileMaker-based tool for running the FMDataMigration tool. This file provides a graphical user interface to make it easier to try this new tool. It can provide a framework to integrate into your FileMaker solution. The Soliant tool can help you understand this new FileMaker tool and requires the free BaseElements plugin.

It will also save the output of the tool into a text field for your review. Here is some common output you may see in this log file:

  • Matching source and target field by the name “FIELDNAME” and internal id
  • Matching source field “FIELDNAME1” and target field “FIELDNAME2” by internal id
  • No compatible target found for source field “FIELDNAME”
  • No compatible source found for target field “FIELDNAME”

FMDataMigration attempts to first match fields between the two files by internal id and field name. The internal field id is generated based off creation order by FileMaker. Therefore, if fields are created out of sequence in one file compared to another, this tool will attempt to notify you of this.

If the name of the field differs, as seen in bullet point #2, it will match by id but notate that the field names differ. This may lead to importing data into the wrong field. If the internal id cannot be matched, the data in the source field will not be imported into the target field. If you have created new fields in your target file, you will likely see this in the log file. It is important to note that you cannot manually choose the field mappings, at this time. If you want to use this tool you will have to follow the source file to target file model, as seen above.

Log File Review

However, this may not be an issue, since the field does not exist in your source file. I encourage reviewing the log file after each migration to ensure data is not imported in the wrong fields and no other issues occurred during migration. The original source file is not modified, so if the migration process does not complete as expected, you can attempt the conversion again.

As FileMaker’s deployment process continues to evolve, FMDataMigration is a great step forward. This tool is ideal for large scale deployments, or for recovering from a last known good backup. It will provide a path to simplify and speed up the data migration process. Our team welcomes it to our deployment process for FileMaker 17.

Resources

Moving Forward

If you have any questions about the FMDataMigration tool or any other new features included in the FileMaker 17 release, please contact our team. We’re happy to help your team determine the best way to leverage them within your FileMaker solution.

Matt Hintz

Matt Hintz

Matt is a Senior Application Developer at Soliant. He is FileMaker 11, 12, 13, 14, 15, 16, and 17 certified developer. When Matt is not working in FileMaker he enjoys taking his dog Zeppelin for walks and playing in a local rock band.

10 Comments

  • Avatar Neil says:

    Hi Matt, Great article an associated demo video and really looking forward to getting my teeth into this new tool. Do you know if it is possible to use this within the iOS SDK for running app upgrades? I have an app that consists of a Launcher file and the main app file and it would be great to use this to allow for updates.

    • Matt Hintz Matt Hintz says:

      Hi Neil! Thanks for the feedback and question. In terms of the FileMaker iOS SDK, FileMaker’s Data Migration Tool only runs on windows and Mac through their respective command line prompts. So my assumption is these commands cannot run from within a sandboxed iOS application, including FileMaker’s iOS SDK. The launcher file method that you mentioned is the only way I am aware of to make updates to a file in FileMaker’s iOS SDK. Cheers!

  • Avatar David Wikstr├╢m says:

    Great sample file!
    I seem to be having an issue with getting the output written to a file on Windows when the path to the executable contains a space (and probably any other character that would require escaping the path). Simply enclosing the path to the executable in quotes did not work (at least not a first attempt).
    For many uses, this is of course a non-issue as you can make sure the exe is not in such a location, but I’ve got my own variation of a FileMaker-database driving data migration, where the migration can run server-side (on FileMaker Server using PSoS), and I’d prefer having the migration tool under FileMaker Server\Data\Documents or \Scripts if possible – rather than e.g. directly under C which is what I currently do – so I’d be interested to hear if you have any ideas on workarounds…

    • Matt Hintz Matt Hintz says:

      Hi David! Thanks for the feedback. Correcting the path for windows is on my list for a next version of this tool but I just haven’t had time to complete this change. I will update you once I have had a chance to complete this update.

  • This isn’t immediately clear from the article or video, so can you clarify: a) A subscription to FM Developer license is required to use the handy Soliant tool; and b) the development and production database files need to be on your local machine or on a remote machine you can connect to as a remote drive.

    • Matt Hintz Matt Hintz says:

      Hi Michael,

      Thanks for the comment.

      A) Yes, FileMaker Inc. has developed a command line tool called “FMDataMigration” and to download/use this tool you need to purchase the FileMaker Developer Subscription from FileMaker. Once you have that download, our tool (like most others that use FMDataMigration) will use your copy of the command line tool from FileMaker to operate and perform the commands.

      B) The development and production files do not need to be on your local machine BUT this process must be performed on files that are closed and offline. I would recommend performing this process with both files located on the same hard drive, whether that’s on your local computer or closed on a server.

      Hope that helps clarify.

      Matt Hintz

  • Avatar James says:

    Hi Matt, I have a field called Auto Enter Serial Number. This has a high value in the Production file when a new record is generated as records are constantly created but a low value in my Development file where I don’t regularly create records. My question is, can the Data Migration Tool update the Auto Enter Serial Numbers so the next value will not be a duplicate of previous values. I know I can use the UUID but I don’t want long ID fields.

    • Matt Hintz Matt Hintz says:

      Hello James, yes, the auto enter serial key will carry over from your production file along with the data from your production. Hope that helps!

  • Avatar sam c says:

    Hi Matt. Thanks so much for your work on this migration solution and for the tutorial video. I noticed a couple of settings in your solution – “Cache Size” and “Original Page Locking” – for which I could not find any official FileMaker documentation. What do these settings do ? Thanks again !

    • Matt Hintz Matt Hintz says:

      Hi Sam. It looks like these are settings from the beta period of the solution and are currently not used in the available version of the tool from FileMaker Inc. Thanks for commenting!

Leave a Reply