Track data modifications

Fommo

Hi,
I have an integration that has been reading master data from NAV SQL DB, but now in NAV 2016 introducing hosting in Cloud we can't do it that way any more. So now we need to extract master data via webservices instead. In the database we could use TimeStamp to keep track of what was read, but that is not accessible in a good way from C/AL. And no, we will NOT modify all the master data tables to enable SQL TimeStamp. Our integration should not modify any standard objects. I want an easy deltadata function from within C/AL.
So, what options do we have? I have looked a little at Integration Record Table used for the CRM connector. It seem to keep track of changes, but is it practical or even possible to use that without CRM Connection. I just want to see what was updated since last transfer so we don't send all data every time.
Then I've though about Change Log of course, but it is not reliable as it only keeps track of user modifications, not changes by scheduled jobs and such.

Do you have any ideas? How do we keep track of data changes in C/AL so we only send new data in the webservice response?

PerJuhl

I a standard NAV solution how many jobs modify master data ?

So I would say change log is your best option.

PerJuhl

I a standard NAV solution how many jobs modify master data ?

So I would say change log is your best option.

PConijn

The change log is indeed flawed, as it does not detect e.g. MODIFY(FALSE). I have been pondering the same issue. In an older version of NAV (2009), I solved it by calculating and storing the MD5 hash value of the text value of the records using a .NET automation I had created and stored it in a Delta table. It looked a bit like this (the code below is improvised, so please forgive any missing semi-colons):

REPEAT
  lrrTable.GETRECORD(lrecMyRecord);
  lridRecord := lrrTable.RECORDID;
  ltxtHashValue := lautHash.CalcMD5(FORMAT(lrecMyRecord));
  
  IF NOT lrecHashTable.GET(lridRecord) THEN BEGIN
    lrecHasTable.INIT;
    lrecHashTable."Record ID" := lridRecord;
    lrecHashTable."Table No." := lrrTable.TABLENO;
    lrecHashTable."Record Hash Value" := ltxtHashValue;
    lrecHashTable."Record Modified" := TRUE;
    lrecHashTable.INSERT; 
  END ELSE BEGIN
    IF lrecHashTable."Record Hash Value"  <> ltxtHasValue THEN BEGIN
      lrecHashTable."Record Modified" := TRUE;
      lrecHashTable."Record Hash Value" := ltxtHashValue;
      lrecHashTable.MODIFY;
    END;
  END;
UNTIL lrecMyRecord.NEXT = 0;

The web service then retrieves all the records that have been flagged as modified, stores them in a temporary table and sends those records over; the "Record Modified" flag in the hash table is set to FALSE once successful receipt has been confirmed by the recipient.

The method works well (though not perfectly) in principle, but it takes up a sizeable bit of processing power and, in NAV2009, the risk of running into the maximum length of the string buffer.


I am now looking to build the interface into a NAV2015 environment and, like Fommo, I am wondering if there is perhaps a better and more efficient way to detect all changes in a record, regardless of whether the Modify trigger fired.

Any ideas would be more than welcome.


Fommo, can you enlighten me a bit on your TimeStamp method?

PConijn

I have read and verified that the SQL Timestamp field is easily exposable in NAV2016 (https://msdn.microsoft.com/en-us/library/dn951475(v=nav.90).aspx) - unfortunately, that won't help me in NAV2015