I still haven’t finished getting syncing working with the entire PARP:PS database. The testing and troubleshooting phase of this is taking longer than I thought. But since I have worked out the basics, I decided to upload a demo file that explains the technique.
This file contains only a Trench table, not the full suite of tables used in the field. But by doing so, I was able to cut everything down to the basics to explain the parts of the database that are necessary for creating the audit trail and for syncing. In order to get everything to work there is a new table, changes to two fields, and the addition of a few fields. This file also uses Script Triggers and Conditional formatting to help create the audit trail and resolve sync issues.
There is a readme in the file. That text is reproduced below.
Special thanks to Chris Motz at Tufts for helping me with the scripting.
This database was made to show the techniques behind a multiple file syncing solution for archaeological databases. It 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 Trenches_Parent). The master database is duplicated and copied to the other devices. Those copies are considered the children 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.
This database only includes the Trenches table. The technique used in this file can be scaled to multiple tables, but scripts and layouts will have to be added for each table that syncs.
The outline of the syncing strategy is simple: 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, 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 scripts, create a few records in the Trenches_Parent file. Close the file and duplicate the file. Rename the duplicated file Trenches_Child. Add and/or edit some records in that file. Close the Trenches_Child file. Open the Trenches_Parent file and select Scripts->Sync->1_1 Import Audit Trail. When that is finished, select Scripts->Sync->2_1 Import New Trenches. You will get a dialog box for each step in the sync routine.
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.
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 funtions, 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.
The audit trail will only work when data is manually entered. If data is changed by a replace command or a set field command, that won’t appear in the audit trail.
The last difference between this file and any newly created FileMaker database is the addition of an extended privilege set to allow the file to be reopened on an iOS device without re-entering a password. You can see that in File->Manage->Security and then the Extended Privilege tab.
Extending this technique
The scripts for syncing 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”].