| Components | All | New | MacOS | Windows | Linux | iOS | ||||
| Examples | Mac & Win | Server | Client | Guides | Statistic | FMM | Blog | Deprecated | Old | |
FM.CompareTables
Compare two tables.
| Component | Version | macOS | Windows | Linux | Server | iOS SDK |
| FM FMSQL | 9.3 | ✅ Yes | ✅ Yes | ✅ Yes | ✅ Yes | ✅ Yes |
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
- Dialog.SetButton
- Dialog.SetTop
- ProgressDialog.Hide
- ProgressDialog.SetBottomText
- ProgressDialog.SetButtonCaption
- ProgressDialog.SetProgress
- ProgressDialog.SetTitle
- ProgressDialog.SetTopText
- ProgressDialog.Show
Release notes
- Version 10.1
- Changed FM.InsertRecord, FM.CompareTables, FM.DeleteRecord, FM.DeleteRecords, FM.InsertOrUpdateRecord and FM.UpdateRecord to take table name from first field name if table name is empty.
- Version 9.3
- Added FM.CompareTables function.
Blog Entries
- MBS FileMaker Plugin, version 10.1pr1
- Neues vom MBS Plugin 9.3
- Whats new in MBS FileMaker Plugin version 9.3
- Neues MBS FileMaker Plugin 9.3 - Über 5900 Funktionen in einem Plugin
- MBS FileMaker Plugin 9.3 - More than 5900 Functions In One Plugin
- MBS FileMaker Plugin, version 9.3pr4
- MBS FileMaker Plugin, version 9.3pr3
- Introducing FM.CompareTables function
This function checks for a license.
Created 15th June 2019, last changed 17th January 2020
