Topics   All   Mac OS X (Only)   Windows (Only)   Linux (Only, Not)   iOS (Only, Not)  
Components   Crossplatform Mac & Win   Server   Client   Old   Guides   Examples
New in version: 8.2   8.3   8.4   8.5   9.0   9.1   9.2   9.3   9.4   9.5    Statistic  

FM.CompareTables

Compare two tables.

Component Version macOS Windows Server FileMaker Cloud FileMaker iOS SDK
FM FMSQL 9.3 Yes Yes Yes Yes Yes
MBS( "FM.CompareTables"; FileName1; TableName1; IDField1; FileName2; TableName2; IDField2 { ; Options } )   More

Parameters

Parameter Description Example value
FileName1 The file name of where the table is inside.
Can be empty to look for the table in all files.
File extension is optional for file name.
Get(FileName)
TableName1 The name of the first table (newer). "Assets"
IDField1 The name of the ID field in first table.
Can be a text or numeric field.
"Identifier"
FileName2 The file name of where the table is inside.
Can be empty to look for the table in all files.
"other.fmp12"
TableName2 The name of the second table (older). "Assets"
IDField2 The name of the ID field in second table.
Can be a text or numeric field.
"Identifier"
Options Optional
Pass options.
Add 1 to include details.
Add 2 to include all the IDs found in both.
1+2

Result

Returns JSON or error.

Description

Compare two tables.
We compare the field names for both tables and report added, removed and common field names.
Then we check the record identifiers to report added, changed and common IDs.
If IDField1 and IDField2 names are the same in both tables, we compare the common records to report the changed record identifiers.
Does not compare the internal FileMaker row IDs.

Records are compared in 100 records block to limit memory usage.

Examples

Compares two tables in two files:

MBS( "FM.CompareTables"; "Anlagen1.fmp12"; "Anlagen"; "Primärschlüssel"; "Anlagen2.fmp12"; "Anlagen"; "Primärschlüssel" )

Compare tables:

Show Custom Dialog [ MBS( "FM.CompareTables"; "Anlagen1.fmp12"; "Anlagen"; "Primärschlüssel"; "Anlagen2.fmp12"; "Anlagen"; "Primärschlüssel"; 1 ) ]

Example result:
{
  "Messages":   [],
  "TableName1":   "Anlagen",
  "TableName2":   "Anlagen",
  "FileName1":   "Anlagen1.fmp12",
  "FileName2":   "Anlagen2.fmp12",
  "RemovedFields":   ["Anlage1feld"],
  "Fields":   ["Anbieter", "Beschreibung", "Bild", "ErstelltVon", "Erstellungszeitstempel", "Gesamt", "GeändertVon", "Kaufdatum", "Name", "Preis", "Primärschlüssel", "Seriennummer", "Typ", "Änderungszeitstempel"],
  "AddedFields":   ["Anlage2feld"],
  "RemovedIDs":   ["00E5BB4E-3BB1-43D8-95DD-60E5C7E6153E", "149E3A95-89CC-4633-A576-C977831ED15E"],
  "IDs":   ["CFBA0CA0-3480-4560-B138-4FE605F1A666", "5AED71F4-D78F-490D-A842-CD623E25C118"],
  "AddedIDs":   ["04430FBE-C3AA-4D1B-B451-1B41AA703A96", "37E7E4AA-F5D0-4E96-BB24-BB720E808357"],
  "ChangedRecords":   {
    "CFBA0CA0-3480-4560-B138-4FE605F1A666":   [{
        "Field":   "Seriennummer",
        "OldValue":   "123a",
        "NewValue":   "123"
      }, {
        "Field":   "Änderungszeitstempel",
        "OldValue":   "2019-06-15 20:13:21",
        "NewValue":   "2019-06-15 17:54:25"
      }],
    "5AED71F4-D78F-490D-A842-CD623E25C118":   [{
        "Field":   "Kaufdatum",
        "OldValue":   "",
        "NewValue":   "2019-06-18"
      }, {
        "Field":   "Änderungszeitstempel",
        "OldValue":   "2019-06-15 17:54:28",
        "NewValue":   "2019-06-16 05:47:24"
      }]
  }
}

Compare and show results:

Set Variable [ $r ; Value: MBS("ProgressDialog.SetBottomText"; "") ]
Set Variable [ $r ; Value: MBS("ProgressDialog.SetTopText"; "Compare Table") ]
Set Variable [ $r ; Value: MBS("ProgressDialog.SetTitle"; "Compare...") ]
Set Variable [ $r ; Value: MBS("ProgressDialog.SetButtonCaption"; "Cancel") ]
Set Variable [ $r ; Value: MBS("ProgressDialog.SetProgress"; -1) ]
Set Variable [ $r ; Value: MBS("ProgressDialog.Show") ]
Pause/Resume Script [ Duration (seconds): ,1 ]
Set Variable [ $$r ; Value: MBS( "FM.CompareTables"; "test1.fmp12"; "Import2PropertyVal"; "_RecordUUID"; "test2.fmp12"; "Import2PropertyVal"; "_RecordUUID"; 1 ) ]
Pause/Resume Script [ Duration (seconds): ,1 ]
# Cleanup prorgress bar
Set Variable [ $r ; Value: MBS("ProgressDialog.Hide") ]
Show Custom Dialog [ $$r ]

Example result:
{
  "Messages":   [],
  "TableName1":   "Import2PropertyVal",
  "TableName2":   "Import2PropertyVal",
  "FileName1":   "test1.fmp12",
  "FileName2":   "test2.fmp12",
  "RemovedFields":   [],
  "Fields":   ["Hex", "Hex.bytes", "PropertyVal.Name", "_CreationTimeStamp", "_ParentRecordUUID", "_RecordUUID", "test"],
  "AddedFields":   [],
  "RemovedIDs":   [],
  "AddedIDs":   [],
  "ChangedRecords":   {
    "B92DB7FE-2506-4070-A384-BE99AE829EB8":   [{
        "Field":   "Hex.bytes",
        "OldValue":   "",
        "NewValue":   "3"
      }, {
        "Field":   "test",
        "OldValue":   "Hello",
        "NewValue":   "World"
      }],
    "8D9BD00C-C31B-401B-B18C-0BCB0CC9EC73":   [{
        "Field":   "Hex.bytes",
        "OldValue":   "1",
        "NewValue":   "2"
      }],
    "2FED79CC-1BBF-4C5D-96E4-5E42C8454C3A":   [{
        "Field":   "Hex.bytes",
        "OldValue":   "1",
        "NewValue":   "2"
      }]
  }
}

See also

Blog Entries

Release notes

Created 15th June 2019, last changed 23th June 2019


FM.ChooseDictionary   -   FM.CurrentScriptID

Feedback: Report problem or ask question.




Links
MBS FileMaker Plugins