Hi
Every record in the NAV database seems to have a timestamp field which I presume changes per record update.
Is it possible to access this field from C/AL in a code unit ?
This would be especially useful when writing apps that need to know if something has changed since the last time it looked; by properly accessing the NAV data via web services (avoiding direct access to SQL server), .
Thanks
Dave K
Hi David,
You cannot access the timestamp field from NAV.
The timestamp field is just an internal “counter”, not really a field saying when the record was last changed, so you cannot use it to see when a record was last changed.
If you want to do that, then you can setup the Change Log and this way record any changes to a record.
Hi Erik
I understand that I cannot see ‘When’ the change occurred, but what if :
Could this work ?
(Thanks for such a quick reply by the way)
Dave K.
Timestamp is never updated, it’s only set when the record is created. So it wouldn’t do any good in what you’re trying to do.
The timestamp is updated whenever a record is modified. What David describes is a common practice when developing SQL applications. Rather than locking a record, to prevent others from changing. When you first retrieve the record, you save the timestamp value. Then when you go to save the changes, you compare the current timestamp to the value you saved. If they are different, then someone has changed the record and you can react accordingly.
This is basically what NAV is doing when you get the “another user has modified the record…” messages.
But, as you’ve stated this value is not available to the C/AL developer. But I’m not sure what value it would add, as NAV already checks this before updating the database.
Thanks - then I’ve also learned something today! [:D]
My question would then be, can TimeStamp be access from Query in NAV 2013? If yes, then David should actually be able to do this from within NAV.
Well, just tested it. Not possible with the Query object in NAV. Here you still only have the “normal” fields in NAV.
But you could write a more “hard coded” Query directly against the SQL Server using ADO or similar technology from with NAV. And as long as you keep track of the TimeStamp (basically a big int number), then you could do it.
Except you wouldn’t know if any records were deleted. That you would still know with the change log. So if the task not only is to see what has been changed, but also to know what has been deleted, then this is still a better (and easier) option.
No, it’s not accessible in the NAV Query object. I though of that also. But he could access it with a SQL view and linked table.
Timestamp is just a synonym for rowversion. The use of the synonym “timestamp” will go away in a future SQL version. Microsoft recommends not usign this syntax and instead to use the newer “rowversion”.
Cool. We will try this later. The reason we want to do this is because we are writing a Data Capture (Windows RT) app for Production output and we would need to periodically check whether any data has changed (so we only re-fetch the data that has changed). Checking the timestamps would be less expensive than reloading everything every minute or so.
This is a common practice when designing update routines for BI and other reporting databases. By filtering on the timestamps you can limit the update to only data that has been added or modified.
Rather than checking each record, you could build a query that check the highest timestamp in the table. Then run it at different times. If the values are different, then something has changed.
The record would be missing when you went to do the compare.