Timestamp fields in Dynamics NAV 2016

Have you ever needed to keep track of when records changed, or have you ever had to do an integration where you want to make sure you keep records in synch across multiple systems?

For master records, this was not a huge chore, as you typically were able to use the Last Date Modified field (if there is one of course), but of course you better hope that all updates to the record call the onModify() trigger. For transactional records though, this became more of a chore, as you would need to either keep track of the last entry you synchronized, or figure out some other way of keeping track what you’ve synched and what you haven’t.

I remember a particular integration that I had to do in which involved synchronizing the Item Ledger Entry table. Sure, I could keep track of the last entry no. so I could easily find all new records since the last synch session, however what became difficult was finding all of the older ledger entries that were updated as inventory transactions were applied to them. Remember that the Item Ledger Entry table is unique in that it gets updated even long after they get created.

Enter the Timestamp field. New to Dynamics NAV 2016, it will simply these types of integration and synchronization scenarios. As far as I can remember, all versions of Dynamics NAV running on SQL Server have had an internal system field name that holds the record timestamp. You could however, only access this field by querying the SQL table directly. Anything done via NAV would never even see the field.

Now with Dynamics NAV 2016, you have the ability for all records to determine which records have not only been inserted, but also modified. You still need to track your date/time reference (e.g. the last time the synch was run), and you are still left to determine what to do if records are deleted, but overall, this is a great new feature to the product!

Here’s how to use them…

  1. In any given table, create yourself a custom field, and assign it a Data Type of BigInteger. The name of the field can be anything you want.

  2. View the Properties of your new field, and set the SQL Timestamp property to Yes. It’s probably a good idea to make it a non-editable field as well, just for logic sake.

  3. Close, save, and compile your table.
  4. Voila, when you run the table, you now how your timestamp!

Umm, ok Mr. Blogger, I get all of this, but that field definitely does not contain either a date or a time!! What’s up with that??

Yes…this is true; the field does not actually hold date or time information. What it does hold though is a “version numbering” that is assigned to each row in a table, across all tables in the database, so every row in the database will have a unique timestamp value. This means that no matter what table you are dealing with, you can use a single reference timestamp and obtain all records that have been added or changed. AWESOME!
(as I said above though, finding deleted records is still a chore that requires coding)

One more note on Timestamp fields. You cannot add them to a table key, however you can still perform a SETCURRENTKEY using the timestamp, since NAV now allows us to sort on any field regardless of it being in a key or not.

So now you know, hopefully you can use this new feature to your advantage.

Happy coding….

MG

Advertisements

Hello Project “Madeira”

Today, Microsoft unveiled a major effort they’ve been working on, codenamed Project “Madeira”.

ProjectMadeira

What is it? Simply put…..this is a preview of Microsoft’s offering of a fully cloud-based ERP system. Geared towards small and mid-sized companies, Project “Madeira” is a simple to sign-up, no footprint browser-based software package that provides all of the tools necessary to manage a business……and it’s available on your mobile device too.

Even simpler put……….it’s FREAKIN’ AWESOME!

Oh, and it is DEEPLY integrated into Office365 as well, so managing emails, or exporting data to Excel is a snap on any device!

If that’s not enough, it’s incredibly easy to connect Power BI to your Project “Madeira” system for incredible insights into your business data.

Currently, the preview is only available in the US, but I imagine it’s only a matter of time for that to change. If you just can’t wait, sign up for a free 1 month trial US O365 account to get access now.

A goal of Project “Madeira” is to make it easy for companies to move to this new platform, and this is evident in the sign up process. You can choose to create an empty company, and then simply upload your existing data into it. For those that are using QuickBooks, there is an extension that once enabled, lets you upload data directly from an Intuit Interchange Format (IFF) file. If that doesn’t float your boat, or if you need to ‘clean’ your data first, you can also use Excel files.

Extensions……what are those you ask? Think of these as pieces of functionality that you can turn on and off. This not only means that you can run your ERP in the cloud, but you can tailor your system with the functionality that you need!

Currently, there are 3 extensions available in the preview, but you can rest assured that many more are on the way:

Ok, enough reading about it, go try out the preview!!.