New TimeStamp fields in Microsoft Dynamics NAV 2016


In Dynamics NAV 2016, you may have noticed that a new field has shown up in the development environment: Timestamp. We mentioned this in the What’s New topic in the MSDN Library, but here is a bit of background.

Since the first version of the SQL Server option for Dynamics NAV, every Dynamics NAV table has automatically had the field “timestamp” added and maintained on the SQL Server table as an internal field. In Dynamics NAV 2016, as per popular request, this field is now available in the development environment, offering new ways of data integration and synchronization. And it is also used for the redesigned CRM integration in Dynamics NAV 2016.

Example

Add a new field to table 17 “G/L Entry”:

“Field No.” = 50000
“Field Name” = Time Stamp
“Data Type” = BigInteger
“SQL Timestamp” = Yes

The name of the field is unimportant. The important thing here, is the Data Type and the property “SQL Timestamp”. The property name refers to timestamp only because this concept is already known, but in reality the field stores row version in the format of BigInteger, automatically incremented for every update. It does not actually store a date or time.

 

Adding a timestamp field to the table is all we need to be able to find modified and new records since last time you checked:

GLEntry.SETCURRENTKEY(“Time Stamp”);

IF ReferenceTimeStamp <> 0 THEN

GLEntry.SETFILTER(“Time Stamp”,STRSUBSTNO(‘>%1’,ReferenceTimeStamp));

IF GLEntry.FINDSET THEN

REPEAT

// This record is updated since last check

UNTIL GLEntry.NEXT = 0;

ReferenceTimeStamp := GLEntry.“Time Stamp”;

// Now store ReferenceTimeStamp somewhere for next time we check for changes

 

Note: We cannot add the field as a key in the table, but that does not prevent us from sorting on it. So SETCURRENTKEY will still add ORDER BY to the SQL query.

Note 2: This method will find not only new records, but also existing records that were modified since last check. To locate deleted records however would require some additional logic.

 

Hopefully this new small  but big feature is useful. As it requested by many partners for many years, if you have comments to the way it is implemented or how to use it, please feel free to add comments below. You can read

Comments (5)

  1. Roger Larsson says:

    Thank you! 🙂

  2. Raokman says:

    would be nice to know who/what changed the record.

  3. Hello Lars!
    Thank you very much for this blog post. I happened to have a project running which includes web service integrations to external system where the customer enrichenes the NAV item data for product dictionary and pricelist purposes.
    I immediately adapted this technique following way:
    Created new “SQL Timestamp” field to item table (as instructed “timestamp” was a reserved word and could not be used).
    I also created a new settings table with one bigint field “last exported timestamp”.
    Then I created a codeunit and xmlport according to Kauffmann’s excellen blog series:
    http://kauffmann.nl/index.php/2011/01/15/how-to-use-xmlports-in-web-services-1/

    Xmlport filters outgoing items according to setup table saved last timestamp, and voilá everything works nicely.
    best regards, Urpo

  4. Per Pedersen says:

    But we still keep the internal timestamp added to all NAV tables – right?

  5. Gintautas says:

    Hey Guys,
    Thanks for the update. Have you considered performance implications? If I set SETCURRENTKEY on the timestamp field, my query will run full scan (not very fast). Creating index on timestemp field is not good idea either.
    What would be your suggest for using timestamp filed for the big tables?

Skip to main content