Record Timestamp Dynamics NAV 2016

catitienei · 2016-01-08

Hello,

In older NAV versions I've used a datetime timestamp field and a secondary key on the field in order to know when a record has been modified and to filter records for sending them to an interface.

I've tried the same scenario in Dynamics NAV 2016 with a field type BigInteger and SQL timestamp property set to yes.

If I add a secondary key on that field I receive a message when I am trying to compile the object:
"The server "DynamicsNAV90" was unable to process the request. The application will close."

Can I use the new timestamp as a key? I am wondering about performance if I do not have a key on the timestamp.

Is there any possibility to make a conversion from BigInteger Timestamp to DateTime? I would like to know when a record has been actually modified without adding a new field of type datetime.

Thank You!

kriki · 2016-01-10

In SQL you have the timestamp field. Each table in NAV has it, but it is not shown in NAV. In NAV2016 you have the possibility to show it as you found out.

Important to know is that the timestamp field does NOT contain a time in it (talking about a bad name for a field...). It is just some versioning ID. Each time a record is inserted/updated, it the value is increased by 1. But not necessary for the same record. I have noticed that a value "x" is used only once for a record in the whole database! This means that there is max 1 record in the whole database with a certain timestamp value (if you update the record, it will get a new timestamp value and the old timestamp value will disappear from the database).

So when you need to know the records that where changed, get the records with a timestamp larger than the largest one that you found last time (you will need to save that value somewhere).

I did a small test:
I created a key on the timestamp-field (I gave it a different name (not "timestamp")) and that was not a problem.

Not having a key on the field will mean that SQL has to scan the whole table to find the records you are searching for.

kriki · 2016-01-10

In SQL you have the timestamp field. Each table in NAV has it, but it is not shown in NAV. In NAV2016 you have the possibility to show it as you found out.

Important to know is that the timestamp field does NOT contain a time in it (talking about a bad name for a field...). It is just some versioning ID. Each time a record is inserted/updated, it the value is increased by 1. But not necessary for the same record. I have noticed that a value "x" is used only once for a record in the whole database! This means that there is max 1 record in the whole database with a certain timestamp value (if you update the record, it will get a new timestamp value and the old timestamp value will disappear from the database).

So when you need to know the records that where changed, get the records with a timestamp larger than the largest one that you found last time (you will need to save that value somewhere).

I did a small test:
I created a key on the timestamp-field (I gave it a different name (not "timestamp")) and that was not a problem.

Not having a key on the field will mean that SQL has to scan the whole table to find the records you are searching for.

David_Singleton · 2018-05-13

kriki wrote: »

I did a small test:
I created a key on the timestamp-field (I gave it a different name (not "timestamp")) and that was not a problem.

Not having a key on the field will mean that SQL has to scan the whole table to find the records you are searching for.

HI Alain,

How did you add the "KEY" do you mean you added an index to the timestamp field directly in SQL? Because my testing show that this wont help, since the Navision "timestamp" is actually [cast([timestamp] as BigInt)] so if you do a setrange or setfilter in Navision then it still needs to do a table scan.

kriki · 2018-05-31

I added a key in NAV.

I have to admit I didn't do any testing on filtering and what was send to SQL and what SQL was doing. I just checked if I could create a key on it.

Record Timestamp Dynamics NAV 2016

Best Answer

Answers

Categories