Door 17 Compare tables

Welcome to today's door. Today, I would like to introduce a function that allows you to compare changes in tables. The FM.CompareTables function is available for this purpose. It compares two tables for their fields and records and checks whether data has been changed, added, or deleted. This function has several parameters, so let's take a look at them together.

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

First, we have the file name of the database that contains the first table to be compared, followed directly by the table name and then the ID field from the table. This must be unique so that a comparison is possible. Then we do the same for the second table: file name, table name, and ID field. The ID field from the first table and the ID field from the second table should contain values that can be compared with each other. Optionally, we can then specify options to influence the output we receive. But more on that later.

We have copied the file we have been working with and made a few changes to the Movies table, which I would now like to explore with you.

We fill in the parameters of our function:

Set Variable [ $File ; Value: Get(FileName) ]
Set Variable [ $JSON ; Value: MBS( "FM.CompareTables"; $File; "Door7"; "PrimaryKey"; 
   "Christmas2025_Copy"; "Door7"; "PrimaryKey"  ) ]

We will now run this in our source file. However, we must also note that if the fmplugin privilege exists in your database in the security settings, this privilege must also be activated in both files for the script to function properly. Furthermore, if we have to perform the comparison on two databases, both must be opened with the same FileMaker. If everything is correct, we will receive a JSON like this.

{
	"Messages":	[],
	"TableName1":	"Door7",
	"TableName2":	"Door7",
	"FileName1":	"Christmas2025",
	"FileName2":	"Christmas2025_Copy",
	"FieldsIgnored":	[],
	"RemovedFields":	[],
	"Fields":	[
		"CreatedBy", 
		"CreationTimestamp", 
		"Generated_Sales", 
		"Launch", 
		"ModificationTimestamp", 
		"ModifiedBy", 
		"Name", 
		"PrimaryKey"
	],
	"AddedFields":	[
		"Ranking"
	],
	"RemovedIDs":	[
		"1D047820-6864-4CB9-88A0-0F15FA3D82D5"
	],
	"AddedIDs":	[
		"D45E3ABC-68F0-408D-8B7F-A2B777489DD8"
	],
	"ChangedRecords":	[
		"3E5C7469-5322-43D6-8404-F48735DD8A0C", 
		"027E9C32-01FA-4296-B1E4-8E38371BC540", 
		"C4FFF754-1D79-4C4E-8E3F-B49B6BD446A2", 
		"BF6DA185-CD55-4DFB-B414-CD6A897CC21A"
	]
}

Let's take a look at this together. First, we can see in the upper keys exactly which tables from which files are being compared here. Then we see that no fields have been removed from the table and all the fields that are present in the original table are listed again. A field called “Ranking” has been added, one record has been removed, and a new one has been added. Three additional records have been changed. The corresponding IDs are displayed so that we can identify the corresponding records. However, this does not provide us with much information about the changes in the records, and this is where the optional options mentioned above come into play. If we enter a 1 here, we will receive more detailed information about the changes:

{
	"Messages":	[],
	"TableName1":	"Door7",
	"TableName2":	"Door7",
	"FileName1":	"Christmas2025",
	"FileName2":	"Christmas2025_Copy",
	"FieldsIgnored":	[],
	"RemovedFields":	[],
	"Fields":	[
		"CreatedBy", 
		"CreationTimestamp", 
		"Generated_Sales", 
		"Launch", 
		"ModificationTimestamp", 
		"ModifiedBy", 
		"Name", 
		"PrimaryKey"
	],
	"AddedFields":	[
		"Ranking"
	],
	"RemovedIDs":	[
		"1D047820-6864-4CB9-88A0-0F15FA3D82D5"
	],
	"AddedIDs":	[
		"D45E3ABC-68F0-408D-8B7F-A2B777489DD8"
	],
	"ChangedRecords":	{
		"3E5C7469-5322-43D6-8404-F48735DD8A0C":	[
			{
				"Field":	"Launch",
				"OldValue":	"1993",
				"NewValue":	"1994"
			}, 
			{
				"Field":	"ModificationTimestamp",
				"OldValue":	"2025-12-06 01:19:07",
				"NewValue":	"2025-12-10 11:08:36"
			}
		],
		"027E9C32-01FA-4296-B1E4-8E38371BC540":	[
			{
				"Field":	"ModificationTimestamp",
				"OldValue":	"2025-12-06 01:19:07",
				"NewValue":	"2025-12-10 11:08:55"
			}, 
			{
				"Field":	"Name",
				"OldValue":	"Gremlins",
				"NewValue":	"Gremlin"
			}
		],
		"C4FFF754-1D79-4C4E-8E3F-B49B6BD446A2":	[
			{
				"Field":	"ModificationTimestamp",
				"OldValue":	"2025-12-06 01:19:07",
				"NewValue":	"2025-12-11 15:54:06"
			}
		],
		"BF6DA185-CD55-4DFB-B414-CD6A897CC21A":	[
			{
				"Field":	"ModificationTimestamp",
				"OldValue":	"2025-12-08 12:52:00",
				"NewValue":	"2025-12-10 11:09:35"
			}, 
			{
				"Field":	"Name",
				"OldValue":	"I'll Be Home for Christmas",
				"NewValue":	"I will Be Home for Christmas"
			}
		]
	}
}

We can now see every single change in the JSON and compare the old and new values at a glance.

We can also set a 2 in the options, which will give us a list of all IDs in addition to the basic information in the JSON.

{
	"Messages":	[],
	"TableName1":	"Door7",
	"TableName2":	"Door7",
	"FileName1":	"Christmas2025",
	"FileName2":	"Christmas2025_Copy",
	"FieldsIgnored":	[],
	"RemovedFields":	[],
	"Fields":	[
		"CreatedBy", 
		"CreationTimestamp", 
		"Generated_Sales", 
		"Launch", 
		"ModificationTimestamp", 
		"ModifiedBy", 
		"Name", 
		"PrimaryKey"
	],
	"AddedFields":	[
		"Ranking"
	],
	"RemovedIDs":	[
		"1D047820-6864-4CB9-88A0-0F15FA3D82D5"
	],
	"IDs":	[
		"C771BFDA-C49F-4E1D-A489-C071FBF06F84", 
		"2C1D5302-1A63-49AA-A6DD-1361F675D096", 
		"A090D14E-31AA-4684-99C9-9639D5194871", 
		"C44FD2D1-75CA-4C4E-967A-9632C1BA64B9", 
		"9951D64D-BCF9-41BA-BA50-03CE17D061E1", 
		"94ADC72B-F80E-48E2-AC9B-9E2E10576C55", 
		"82E92D98-7750-4A7C-95CD-78B1C386F3BA", 
		"7CB7D5DC-FB55-4A54-8765-9D8E6B120838", 
		"0DC9DFA0-7152-4071-913A-BAB4D51D80E8", 
		"DC6C070F-1C0D-408F-B425-195121E3F2B6", 
		"176B2740-3AE0-4846-A21D-CF5FAE3F5C0C", 
		"788B5BF3-D55D-4698-BFE7-98D16B36D652", 
		"3E5C7469-5322-43D6-8404-F48735DD8A0C", 
		"E6D966F4-8B06-422A-A3AF-4B4305447D1A", 
		"8DB8549F-A503-4E7A-B4E7-AEAC22E08700", 
		"027E9C32-01FA-4296-B1E4-8E38371BC540", 
		"AA9121AE-A49E-4D8C-8CC3-8AF5ECDF8AD1", 
		"15410990-349B-45EF-B80F-C9315058F335", 
		"C4FFF754-1D79-4C4E-8E3F-B49B6BD446A2", 
		"B3BA53FD-FBE9-457B-96D2-46103D0167E7", 
		"C4F29C78-6286-450C-9564-BE20CBAAEA19", 
		"BF6DA185-CD55-4DFB-B414-CD6A897CC21A"
	],
	"AddedIDs":	[
		"D45E3ABC-68F0-408D-8B7F-A2B777489DD8"
	],
	"ChangedRecords":	[
		"3E5C7469-5322-43D6-8404-F48735DD8A0C", 
		"027E9C32-01FA-4296-B1E4-8E38371BC540", 
		"C4FFF754-1D79-4C4E-8E3F-B49B6BD446A2", 
		"BF6DA185-CD55-4DFB-B414-CD6A897CC21A"
	]
}

Of course, if necessary, the two options can be combined by adding the numbers together. This will give us a JSON with the maximum information.

I hope this function helps you when you want to compare two tables.