Syncing
General Info
This database was designed for a particular syncing solution involving a single desktop database and multiple copies of databases on remote devices (other computers or iOS devices). The terminology used in this file and documentation is one of a parent/child relationship. The master desktop file is the parent file (that is why it is titled PS_11_Parent). The master database is duplicated and copied to the other devices. Those copies are considered the child databases. At some point the child databases are copied back to the desktop computer holding the parent database. Scripts are run from the parent database to compare the information between the two, import when necessary, and ask for a manual resolution for conflicting data. The child databases are then archived or discarded, and a new child database set is created from a duplicate of the parent database.
The remote devices are only supposed to edit certain records. That is, one iOS device is used in one trench for data entry. I am not scripting for the ability of two remote devices editing the same record.
The sync routine in this database is done on the record level, not the field level.
The database also records every entry and edit into every field so a complete audit trail of the database can be viewed.
This file was created with FileMaker Pro 11 and might include script steps that are incompatible with older versions.
Basics
The outline of the syncing strategy is simple: taking the example of the trench table, the parent database contains a Trench table but also a Trench Shadow table that is almost an exact duplicate. The child records are imported into the Trench Shadow table, and compared to the Trench table. If the record is new it is imported. If it has been edited in the child table but not the parent table, the data in the parent table is replaced by the data in the child record. If the record has been edited in both tables, the user is directed to a screen to resolve the differences manually. The last scripts stamp the table with a LastSync timestamp and removes the shadow table data.
To start the sync scripts, create a few records in the PS_11_parent file. Close the file and duplicate the file. Rename the duplicated file PS_11_Child. Add and/or edit some records in that file. Close the PS_11_Child file. Open the PS_11_Parent file and select Scripts->Sync->1_1 Import Audit Trail. That starts the series of scripts necessary to sync the tables.
The first time you run the script you might see a message that no records have been edited on the device. If there were, they will show up in the third phase, where you can check for conflicts. That will only happen once.
To see the audit trail for a field, click into the field and then select Scripts->Show history of this field. A new window will be created with the audit trail of that particular field in that particular record.
Details
UUID: Each record in the database needs to have a unique serial number. Since records in this database can be created on different devices, that serial number has to be unique no matter where it was created. This database uses a custom function called UUID.New. Custom functions can only be added and edited using FileMaker Pro Advanced, but they can be used with FileMaker Pro. The UUID.New function adds a unique serial number to each table upon creation that consists of a timestamp, a random number, and the MAC address of the computer making the record. The time and MAC address can be pulled from the UUID field with two other custom functions, UUID.GetTimeStamp and UUID.GetNICAddress. These functions were downloaded from http://www.fmfunctions.com/functions_display_record.php?functionId=274.
The UUID field is the field that forms the relationship between the Trenches table and the Trenches Shadow table.
The Trenches table also includes a Synced field, which is set to auto-enter “n” when a record is created. There is a LastSyncTimestamp field which is global. Once the sync scripts run, that field holds the last time that particular table was synced. This is so that the field ModifiedAfterSync can calculate whether the field has been modified since the last sync scripts were run.
The Trenches Shadow table differs slightly from the Trenches table. The CreationDate and ModificationDate fields are not set to auto-enter, so the record can be modified within the shadow table without triggering a change in the ModificationDate field. There is a field in the Trenches Shadow table that does not appear in the Trenches table, SameRecordAsShadow: If ( ModificationDate > Trenches::ModificationDate ; “g” ; If ( ModificationDate = Trenches::ModificationDate ; “e” ; “n” )). This is to help calculate what has changed in the child record when there is nothing in the LastSyncTimestamp field.
Resolve Trench Records Layout: a new layout has to be created for each table that syncs to resolve any differences between the two tables. The fields from both the Trench and Trench Shadow tables are compared side by side and the fields that differ between the two records are highlighted in yellow. This works by using Conditional Formatting. You can see that in layout mode by selecting a set of fields and then Conditional… from the Format menu.
Audit Trail: the audit trail works by using Script Triggers. You can see this in layout mode in both the Trenches and the Resolve Trench Records layouts. Click on a field and select Set Script Triggers… from the Format menu. These triggers have to appear on every layout and field that could be entered. When a field is entered, the script stores the old data in a series of variables. When leaving the field, another set of variables is created. If there is a difference in the field contents, a new record is created in the auditTrail table.
Extending this technique
The sync scripts are intentionally modular. To add an SU table to this database, add the tables, then in the Manage Scripts window, copy the scripts 2_1 through 2_6 and paste them back into the window. Rename them 3_1 to 3_6 and customize them for the new table. You can connect the script sets by adding a new step to the last in the previous series. So to connect 1_1 to 2_1 add a line to the 1_1Import Audit Trail script that says: Perform Script ["2_1 Import New Trenches"].