fmSQL Synch (v2.0)

Database Integration (This page has not been updated for v2 yet. See the README file for current details.)

The fmSQL Synch software has been designed on top of the FileMaker templating system developed by Garrison Computer Services. It can also be integrated with existing FileMaker solutions. It is fairly easy to integrate existing FileMaker databases with fmSQL Synch. You will need to add a few fields, relationships, layouts and scripts. Each is outlined below. See Integration for more general implementation details.

Fields to add to FileMaker files

Primary key (ID) in FileMaker must not have 'prohibit modification' ticked, otherwise it won't be possible to assign key values to new records. All references to the 'ID' field above should be changed to the name of your primary key field. The calculation for the cFileNameShort field should be changed to an appropriate value for your file; it should match the value entered in the Abbr config option in Synching.fp5.

The cDateModified field should be changed to the following calculation if using MS SQL rather than mySQL. Use the following calculation for any SQL servers that expect date/times to be formatted as "yyyy-mm-dd hh:mm:ss" instead of the mySQL format of "yyyymmddhhmmss".

Relationships

* HasMergeConfict is a self-join relationship.

Layouts

The first two layouts are very important to get right. They are used for getting data into and out of FileMaker databases. They should have all the fields that need to be synchronized, and there should be no extra fields. Look at the supplied Example.fp5 database for examples of the layouts. The date and time fields need to have their formatting set correctly for data to be synchronized successfully. The tab order should be set to default so that field ordering on the layouts matches the order of fields set in the FM Fields option.

It is also important that none of the fields have validation checks which may fail when updating FileMaker records. The demo/template files use a global field from the Synching file (gFldValidationOff) to turn off validation checks while synching is in progress. The global field is set to 1 during the synching process and reset to it's original value when finished.

SQL->FM (update)

Date fields to be formatted as dd/mm/yyyy (or whatever format is returned by SQL select query)

FM->SQL (select)

Date fields to be formatted as yyyy-mm-dd (or whatever the format is for the date fields for your database)
Time fields to be formatted as hh:mm:ss (24 hr time) (or whatever the format is for the time fields for your database)

MergeConflicts

Make sure MergeConflicts portal is 'in front' on layout

Scripts

You may want to import scripts from the Example.fp5 database rather than re-entering from scratch. Be sure to fix any errors after importing.

Keep FM Value
If [ cIsMergeConflict = ID ]
    Send Apple Event [ Event Specification: Application: fmSQL Synch, Event Class: sync, Event ID: KFMV, Filename: ]
Else
    Show Message [ Buttons: "OK", "", ""; Data: "There are no merge conflicts to resolve." ]
End If

Keep SQL Value
If [ cIsMergeConflict = ID ]
    Send Apple Event [ Event Specification: Application: fmSQL Synch, Event Class: sync, Event ID: KSQL, Filename: ]
Else
    Show Message [ Buttons: "OK", "", ""; Data: "There are no merge conflicts to resolve." ]
End If

Find MergeConflicts
Enter Browse Mode
Perform Find [ Request 1: cIsMergeConflict >0 ]
    [ Restore find requests ]
Perform Script [ MergeConflicts Layout ]
    [ Sub-scripts ]

Delete Record
Enter Browse Mode
Allow User Abort [ Off ]
Show Message [ Buttons: "Cancel", "Delete", ""; Data: "Do you want to delete the ENTIRE record?" ]
If [ Status(CurrentMessageChoice) = 2 ]
    Set Field [ DeleteLogCreate::IDParent, ID ]
    Set Field [ DeleteLogCreate::File, cFileNameShort ]
    Delete Record/Request
        [ No dialog ]
End If
Allow User Abort [ On ]