In several of our projects we log before and after versions of a record for audit purposes. We do this by serializing the record into JSON and then storing the JSON string

We can then Compare the before and after of a record and show the changes, but of course we want to do this in a generic method that will not require a lot of coding

Here is what I came up with this morning. First I fake an example log entry, in the real world this would be coming from a Log table in the database

strLog is Structure
	TableName is string
	BeforeRecord is string
	AfterRecord is string
END

recLog is strLog
recLog.TableName = "Customer"
recLog.BeforeRecord = [
{
	"CustomerID":1,
	"CompanyName":"Beko",
	"Address":"8574 Hickory Lane",
	"City":"Portland",
	"State":"KY",
	"ZipCode":"93887",
	"EmailAddress":"",
	"StripeCustomerID":"cus_GiX5xGdrUlPdph"
}
]

recLog.AfterRecord = [
{
	"CustomerID":1,
	"CompanyName":"Beko-Changed",
	"Address":"8574 Hickory Lane",
	"City":"Portland",
	"State":"KY",
	"ZipCode":"93887",
	"EmailAddress":"",
	"StripeCustomerID":"cus_GiX5xGdrUlPdph"
}
]

And I created a table control populated by programming with 4 columns, Column Name, Before Value, and After Value all strings, and a Changed Column that is checkbox

recBefore is JSON = recLog.BeforeRecord
recAfter is JSON = recLog.AfterRecord

FOR EACH tmpMember OF recBefore..Member
	Changed is boolean 
	IF {"recBefore." + tmpMember..Name} <> {"recafter." + tmpMember..Name} THEN
		Changed = True
	ELSE 
		Changed = False
	END
	TableAddLine(tblTable,tmpMember..Name,{"recBefore." + tmpMember..Name},{"recafter." + tmpMember..Name},Changed)
END

First I move the string data of the before and after variables into JSON Variables.

Then I use a special property of the JSON variable called ..Member to loop through all the members of the JSON data. The ..Member is a complex structure itself with a complex structure containing Name, Type, Value, and Exists.

Since I know both before and after have the same fields I don’t have to do anything special with the after. I use Indirection to compare each before field to its matching after field and set a boolean to true or false, then I add a line in the table again using indirection for the before and after values. The result is a table like this.

Note: When working with the real solution this morning, we ran into an issue with a field containing a password hash. The code storing the data is using the Serialize function with the psdJSON parameter. The resulting JSON has Unicode encoded data in it using the /u3773 style encoding. The StringToJSON function was not correctly handling it, it would quit converting the string to JSON when it hit the Unicode data and we only saw a partial list of fields. We ended up cleaning the offending data before using the StringtoJSON function and that solved the issue.

You might also run into an issue if you have any complex datatypes in your JSON, For example, an Array inside of the JSON structure, you might need to do a nested loop in order to enumerate the values in the array.

• Quick Tips
• WX