Setting Your FileMaker Solution up for Syncing

Video: Setting up the Sample Assets FileMaker solution for sync

Syncing your FileMaker solution – Overview

LCFM Native allows you to sync your data from your Android app back to your FileMaker database and vice versa. If you don’t want to sync, you don’t need to do this, your app will work without changes. However LCFM Native comes with the ability for your app to run offline and for that, you might need to make minor changes to your solution to make it work. This is supported in Beta 3 so you can try it out today.

The (Sample) Assets solution is one provided with FileMaker 17 as an example of a complete, albeit relatively simple, solution.

The solution allows tracking of assets within an organization of various types, with photos, serial number and including calculation of depreciation values. Assets can be checked-in, checked-out and a review of the condition of the asset applied – the latter all being recorded in a history. The last modification time and modification user of each asset is tracked.

The solution consists of two tables, Assets and History.

The Assets table has a primary key field ASSET ID MATCH FIELD of type number. The field is set to be auto-enter on create serial with increment 1 – meaning each newly created record has it set to an integer one higher than the previously created record. Additionally, it has an auto-enter on modify modification name field (Modified by User) and an auto-enter on modify modification timestamp field (Modified Timestamp). The latter two fields will get changed to the current logged in user name and current timestamp whenever a change is committed to the record.

The History table has a foreign key field ASSET ID MATCH FIELD.

There is an equals join relationship between the Assets and History tables via the ASSET ID MATCH FIELD in both tables which causes it to be a one-to-many relationship. The join is set to allow deletion when records are deleted from assets.

This arrangement of tables and relationships means that each record in assets essentially ‘owns’ its own set of history records.

One important thing to note about the history table is that records in it are ‘create only’ – there is no way (through using the solution alone) for a record in the history table to be modified after it has been committed.

As it stands the (Sample) Assets solution is not syncable with LCFM Native – fortunately though, it is easy to make it so!

We first explain why it is not syncable in the next section; then show how to make it syncable in the last section.

Why the Sample Assets solution by default is Non-Syncable

FileMaker is designed and built to either run entirely locally by a single user or by multiple users connecting to a solution either hosted in FMProAdv or in FMServer.

When operating in the hosted mode FileMaker requires that there is always a connection to the machine hosting the solution – if the connection disappears (e.g. because the local network fails, or the internet fails briefly, or the devices wifi or cellular connection drops) then the solution cannot continue to function at all until the connection re-appears and a new connection can be made.

The reason it requires this is that FileMaker uses what is called a ‘pessimistic locking model’ – where in order for a client to modify the database in any way it needs to tell the host about the change before it happens so that no other (currently connected) clients can make conflicting modifications and this lock remains (and continues to affect all other clients) until the changes are committed and sent to the server.

LCFM Native is different – it is designed to be able to offer you the best of both worlds.

When running in sync mode, LCFM Native will run and allow continued use of an app regardless of whether there is a connection to the hosting server – indeed it works the same as if you were running the solution locally all the time. The difference occurs, however, when it detects that there is a connection to the server. When it detects a connection it will ensure that any remote and local changes are merged together and committed back to the server – this is the sync process.

For the sync process to work, however, there are two very important rules which the solution’s database needs to adhere to:

Identification of records in key relationships (i.e. those between primary and foreign key fields) cannot rely on any global values held by the server.
There must be a way to be able to decide which changes to choose when a client finds that both it and another client have modified a record since the last sync process ran.

Breaking rule (1) causes problems because LCFM Native clients cannot always know what the global values on the server are at any one point of time – as they might not have a connection to check.

Breaking rule (2) causes problems because LCFM Native can’t know at the point its data is modified whether the same data is being modified in other clients.

The (Sample) Assets solution breaks rule (1) here – the primary key field of the Assets table uses a serial auto-increment – which is a global value (FileMaker stores the last value assigned to a serial increment field per serial-increment field on the server).

It doesn’t break rule (2) though – the Assets table already has a suitable field which allows LCFM Native to choose what changes to select in the case a conflict occurs – the Modification Timestamp field. It seems quite reasonable, in this case at least, that the most recent change is probably the best one to choose.

It should be noted that the History table is absolutely fine as it stands. History records are identified by the id of the asset the history is attached to and they can never be modified after creation – so conflict can never occur.

Making (Sample) Assets Syncable

As identified in the previous section there is only one thing which makes Assets non-syncable – the ASSET ID MATCH FIELD – as it relies on an on-create serial increment auto-enter value. i.e. It requires knowing the last used value the server holds, and LCFM Native can’t always interrogate the server.

Fortunately, this is really easy to fix – the ASSET ID MATCH FIELD just needs to have its auto-enter on-create serial setting turned off and instead have an auto-enter calculation of Get(UUID). Using Get(UUID) gives you a unique value which is not tied to the server and means the asset records created on any client will all have a unique identifier for their primary key field.

Step by Step Instructions.

Here is how you do it on the Sample Assets solution from FileMaker.

First, open the local version of the solution in FileMaker

Next we need to make some changes to the fields in the database tables. These are only minor changes.

Still within FileMaker, Click File > Manage > Database

First we need to modify the primary key field in each table to autoenter calc = Get(UUID).

Highlight the primary key field then change the type to text. Now click change.

With the primary key field still highlighted, click options.

If the serial number checkbox is already checked, uncheck it.

Now check the calculated value checkbox and click specify.

At this stage enter Get(UUID), then press OK.

That is the first modification complete, only one more to make.

In order for sync to work properly, each table must have a modification timestamp field. As explained earlier the Assets table already has a suitable field which allows LCFM Native to choose what changes to select in the case a conflict occurs – the Modification Timestamp field.

However if it didn’t have this field, here is how you would add it.

Still within manage database, enter the field name “Modified Timestamp” and set the type to Timestamp.

Now click Create, then Options.

Check the Modification Checkbox.

And that’s it! Your solution is now LCFM Native Sync Ready.

Remember you will have to make these small changes to each table used in your solution.

Try it for yourself, here is a short video showing how syncing is working in LCFM Native Beta 3 with the modified sample assets solution.