« MBS FileMaker Plugin,… | Home | JSON Sort »

Introducing FM.CompareTables function

Recently we got a nice idea where the MBS FileMaker Plugin could help. We got a new FM.CompareTables function, where the plugin compares two tables and reports the results. The idea is that we can query data about structure and content via SQL and compare it. Very useful to compare the copy of the table form yesterday with the table from today.

MBS( "FM.CompareTables"; FileName1; TableName1; IDField1; FileName2; TableName2; IDField2 { ; Options } )

You pass the plugin the file names, table names and for each the name of the field for the unique IDs to compare. We first query for both tables the field names and field types. The field names allow us to report which field names are added or removed and find the common set of field names.

Once we have the field names, we can run a query in both tables to query the IDs for the records. This queries the given ID field and not the record numbers given by FileMaker internally. This allows to import a table into another one where the internal row numbers change to still compare the values.

Once we have list of IDs, we can report which IDs are added and which removed. For the common IDs, we load data in blocks of 100 rows and compare values. A value is same if the data type is the same and the content compares as equal. We can then report which fields in which records changed or if you don't need full details just report the changed IDs.

Here a sample query:

Show Custom Dialog [ MBS( "FM.CompareTables"; "Assets Backup.fmp12"; "Assets"; "PrimaryKey"; "Assets.fmp12"; "Assets"; "PrimaryKey"; 1 ) ]

The field named primary key is the field with the UUIDs to identify the records. The result may look like this:

{
    "Messages":     [],
    "TableName1":     "Assets",
    "TableName2":     "Assets",
    "FileName1":     "Assets Backup.fmp12",
    "FileName2":     "Assets.fmp12",
    "FieldsIgnored":     ["gTest"],
    "RemovedFields":     ["Serial Number"],
    "Fields":     ["CreatedBy", "CreationTimestamp", "Description", "Image", "ModificationTimestamp", "ModifiedBy", "Name", "Price", "PrimaryKey", "Purchase Date", "Type", "Vendor"],
    "AddedFields":     ["Weight"],
    "RemovedIDs":     ["880F4BC1-7410-4ED0-A3E6-D9BD1B828A16"],
    "AddedIDs":     ["A8B447FA-74B9-4911-9180-A940CD251DB5"],
    "ChangedRecords":     {
        "B982C9FE-A3A2-49F6-A0A9-8FB9A2CB421B":     [{
                "Field":     "ModificationTimestamp",
                "OldValue":     "2019-06-21 15:54:33",
                "NewValue":     "2019-06-21 15:55:25"
            }, {
                "Field":     "Price",
                "OldValue":     "2",
                "NewValue":     "2.5"
            }]
    }
}

As you see we identified the removed and added fields and the common fields. Calculated and summary fields are ignored and global fields skipped. If someone needs to compare those, we may be able to add an option for them. For changed records, we opt-in for the extended version with an array of objects containing the records changed. The detailed array has the list of field changes with field name, old and new value.

If you like, you can show progress dialog via ProgressDialog.Show and the plugin will update the progress value while comparing data.

We hope you find this useful and if you have further ideas, please contact us.
MBS FileMaker Plugin 9.3pr3 has the function included and for pr4 we changed it to skip globals, summary and calculated fields. Claris FileMaker Plugin
21 06 19 - 16:34