Today we bring you an article by a guest writer, one of our colleagues in the FileMaker community, Kirk Rheinlander. You can connect with Kirk online here or at his blog. We are excited to bring you this content, because it reaffirms our feelings about the FileMaker Data Migration Tool that we spoke of in a previous blog post: The New Way to FileMaker. As Kirk says, it really is a game changer. 

With the advent of FileMaker 17, the FileMaker Developer Subscription (FDS – available for a $99 annual fee) provides access to FileMaker development tools. This includes – among other things (see blow) – a new command line interface (CLI) program called Data Migration Tool (DMT).

It is a game changer, and here is why.

Typical legacy development and support

FileMaker defaults to a model, where the data, user interface (UI) and code are all contained in a single file. This makes it easy to keep everything together, but also makes ongoing maintenance and support more challenging. The primary issue is that if you work on the code/UI in an off-line copy, the data is out of sync with the revised app. Working on a live FileMaker app, although possible, has the potential to lock users out of records or corrupt data unless special care is taken. There are a few legacy alternative approaches to work with this problem space, each with pros and cons.

Option 1 – Live DevOps

Edit the scripts and layouts on live production code: You need to double and triple check your changes, especially those that can modify or delete data, before accepting the change. It is often a good idea to make a backup just prior to each change you make, so you have a recovery point, that hopefully does not lose any data entered in the interim, if something goes wrong and you have to restore t the last known good condition. However, many, many changes do not impact data content, so operating on a live app may not kill the patient. And don’t even think about changing the schema, or even opening the data management window, as that action is like a record lock on the whole system, blocking user actions.

However common this practice is (and it is likely the most common approach), it remains a dangerous practice at best. As a result, you end up doing a lot of the more data-impactful changes after hours, so that the data is static, and a unforced error requiring restoration of a recent backup, would retain data integrity.

Option 2 – the Separation Model

The more advanced approach to this conundrum was to create a data separation model, where the data was in one file and the UI and business logic was in another, and the two files were connected. That way, you could use an off-line copy of both data and UI files, making changes to the UI file and testing them out. Then when ready, just deploy of UI file in place of the existing production one, as the current data remains in the production data file. It is a good model and tried and true.

But this still has its own set of maintenance challenges, as well as some additional complexity in setting it up initially, but is a very workable method for advanced FileMaker programmers.
Todd Geist of Geist Interactive has an excellent video on how to create a data separation model from an existing app.

Option 3 – Off line plus manual data migration

You also could pull a copy of the app off line and work on it, making mass changes, and test them all on the then stale production data that came with the copy. That works great, until you have to place it back into production with the changes, and the data in production is out of sync with the point-in-time stale data from your offline copy; the production system has moved forward with new content.

The process of importing the data is a lot of tedious manual effort. You need to do the import field matching, then the importing data transfer time can be long and tedious based on the number of records and cascading calculations from data changes. The order in which you import those tables, can have an unforeseen impact on related calculated data. As the number of tables and amount of records grows, that time expands rapidly. Many typical production systems have to be migrated over the weekend, as the data load process can take a lot of time, and very large systems, even a weekend of downtime is an insufficiently large window to transfer the data before people come back to work on Monday. Just the quality checks on the data integrity from the resultant transfer, make take a large chunk of time.

The Game Changer – Enter FM17, FDS, and DMT.

Option 3 now has new life. You can now make changes in an offline copy, test them out against the stale production data, then using DMT, migrate the current production data into your new, improved copy.

So what is different from Option 3 above?

The Data Migration Tool (DMT) is fast. I recently took an 18 table FileMaker app with around 100,000 records, that took the better part of a day to migrate manually and quality check the data transferred. Using DMT, I did the import in under 2 seconds. Yes, you read that right, 2 seconds. This product is fast! Another developer I spoke to about this, told me of a 19gb FileMaker app that took more than 3 days to manually migrate, was a 35 minute process in DMT.

So now you can move an app off line, make the changes with impunity, then have a way to quickly place it back into production with current data newly imported. Awesome!

One more thing…

There was a bit of a licensing concern over the FDS license structure. FDS includes a development server that supports 3 connections AND provides 3 FileMaker Pro Advanced licenses. However, these licenses are legally constrained to only access the licensed development server included – no production server access allowed (although not technically constrained at this point of time).

How could this work when you have FileMaker apps in production that need to be maintained?

Now with the advent of DMT, the FDS licenses for FileMaker make sense; you can pull a production system copy offline, fix things using the dev server and FDS licenses, then migrate the data and redeploy the app. So after a decade of struggling with this particular FileMaker idiosyncrasy, the biggest, most important and beneficial change in FileMaker 17, I believe is the advent of a command line tool known as the Data Migration Tool.