FileMaker Go as a front end to SQL Server

With the release of FileMaker 12 FileMaker has made FileMaker Go free. The reduction in cost makes Go the best product for rapid application development for iOS devices. While using Go within an all FileMaker network with good wireless is trivial its more difficult to use Go as a front end to a SQL Server especially when using an intermittent 3G signal.

The solution to this issue breaks down into 4 distinct problems:

  • Syncing between an online and local version of the database. Syncing also includes allowing the user to define which records they wish to move onto the local device
  • Transactionally moving data around to prevent issues with data loss/incomplete sync records
  • Gracefully switching between online and offline, including offering the user clear feedback as to which mode they are operating.
  • Connecting the Go device to the SQL Server

The two most complex issues are syncing and dealing with transactions so I will deal with those here.

We have written a few syncing models, one syncing system is based on two tables and the other is a simpler ESS link for use with MySQL and SQL Server.

In the table based sync model one table handles the sync event, the other has one record per field modified. The sync table is hosted on a FileMaker Server, the sync records can either be fed from the ipads or from the SQL Server using a script on the server.

The simpler model allows the user to find the records from SQL Server using ESS. The records are then imported to the ipad version of the database. As a a push only model this works well.

The more complex model allows two way syncing but requires a lot more work. Moving data around transactionally is the second part to the data integrity. Depending on the method used either a JSON string and checksum or transactional model within FileMaker is used. A JSON string with a simple length attribute is the easiest way to move the data around but its also trivial to use and MD11 checksum on the string. Pass this separately to the JSON and compare the hash to make sure the JSON is complete and unaltered.

Other aspects worth considering are avoiding sync issues altogether. For example the typical ‘status’ field can be replaced by a table and with some simple logic (ie: latest date takes precedence unless any status is cancelled in which case the status is cancelled, or use a  table of notes instead of a single text field). Clever use of approaches like this can remove the requirement for syncing at all and provide a more detailed history for a given entity.

Damian Kelly

Damian has been a database developer with Linear Blue since 2007. During this time he has developed complex FileMaker solutions in a broad range of industries from Finance and Investment to Manufacturing to New Media Promotion. Damian graduated in Production Engineering from the University of Hertfordshire in 1996, attained his Masters of Engineering from Kingston University in 1997 and MBA from Heriot Watt in 2006. He holds certification in FileMaker versions 7 through 12 and in Oracle MySQL 5.

More Posts - Website

1 thought on “FileMaker Go as a front end to SQL Server”

  1. Very great post. I just stumbled upon your blog and wanted to mention that I’ve really enjoyed
    browsing you blpg posts. After all I will be subscribing on your feed and I’m hoping you write again very soon!

Leave a Reply