Skip to main content

Notifications

Dynamics 365 Community / Blogs / Got C/AL? / How to compare Records in D...

How to compare Records in Dynamics NAV?

Community Member Profile Picture Community Member Microsoft Employee

Last week while being onsite at a customers site, we suddenly needed the ability to compare 2 records to see if any changes were done to the data. We were using the Archive functionality for Purchase Headers, and due to some needed modifications (in the release function) we ended up storing a lot of versions of the same Purchase Header, even though no changes were actually made. With hundreds of Purchase lines and thousands of document dimensions, the Purchase Header/Line/Dimension Archive tables were suddenly almost 50% of the total database size!

So we needed to compare 2 records during runtime, to see if they are identical before actually doing the archiving. This brings us to the subject of this post, were i will go into the details of how we did this in Dynamics NAV.

I remember doing something like this back at my first development training, and was pretty sure we just did a simple evaluation like this:

IF PurchHeader = xPurchHeader THEN...

Of course this does not work, but a small change to it actully (almost) works:

 IF FORMAT(PurchHeader) = FORMAT(xPurchHeader) THEN...

Why does this work? Or almost work? The FORMAT command called with the record variable as parameter outputs each field value seperated by TAB as a Text string, and you can do a simple comparison on this. Unfortunately there is a limit for how long the returned Text string can be in NAV, so if you have a record with more than 1000 bytes of data it will not work. If you have less than that, it works great and is a very simple solution.

What to do with larger records? Here you will actually have to do a true field to field value compare, and return false if something does not match. This can be done with RecordRef. Here is a function that will do it:

CompareRecords(Rec1 : RecordRef;Rec2 : RecordRef) : Boolean
FOR i := 1 TO Rec1.FIELDCOUNT DO BEGIN
  FldRef1 := Rec1.FIELDINDEX(i);
  FldRef2 := Rec2.FIELDINDEX(i);
  IF FldRef1.VALUE <> FldRef2.VALUE THEN
    EXIT(FALSE);
END;
EXIT(TRUE);

In order to use it you just assign you data to RecordRef variables, and call the function that will do the comparison for you. Of course this can easily be extended to only compare certain fields etc.

Enjoy.


This was originally posted here.

Comments

*This post is locked for comments