Oct 25

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.

One Response to “How to compare Records in Dynamics NAV?”

  1. Ricardo Ventura says:

    HI, congratulations for your page, it’s fantastic.

    sorry for my English:

    Today I have tried to compare two records with your solutions and none has worked. I have Attain 5.1 over SQL Server 2005.

    The first compile but it returns false with two records with the same information (I have write to a log FORMAT(rec1) and FORMAT(rec2) for check it and both are equals. The rec2 is a temporary table, so I don’t know if the SQL timestamp is the same to the original and Navision include it in the comparative.

    The second option doesn’t compile because FIELDINDEX doesnt exists.

Leave a Reply

preload preload preload
pornpants.com pornofri.com kilporn.com