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.
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.
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.
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:
- Download the executable file
- Open your command prompt (Terminal on the Mac and Command Prompt in Windows)
- Change the current directory to where the executable file is downloaded
- Type in the name of the executable
- Hit enter
You will see the documentation and version number for this tool:
FMDataMigration 18.104.22.168 (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.
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.