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
