Topics   All   MacOS (Only)   Windows (Only)   Linux (Only, Not)   iOS (Only, Not)  
Components   Crossplatform Mac & Win   Server   Client   Old   Deprecated   Guides   Examples   Videos
New in version: 9.2   9.3   9.4   9.5   10.0   10.1   10.2   10.3   10.4   10.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 Flags
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).
Can be ID of table, so we lookup name by ID.
Can be result of GetFieldName() function as we remove field name automatically.
"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).
Can be ID of table, so we lookup name by ID.
Can be result of GetFieldName() function as we remove field name automatically.
"Assets"
IDField2 The name of the ID field in second table.
Can be a text or numeric field.
"Identifier"
Options Pass options.
Add 1 to include details.
Add 2 to include all the IDs found in both.
1+2 Optional

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 17th January 2020


FM.ChooseDictionary   -   FM.CurrentLayoutID

Feedback: Report problem or ask question.




Links
MBS Xojo Plugins