Blog

Once upon a time, and really not such a long time ago, there was no such thing as a codenit type. In all honesty, there is still no such thing as a codeunit type – there is subtype, though. (Why we have subtypes, without having types, beats me, but let’s let the semantics go.)

Nowadays, we have four codeunit types. NAV 2009 SP1 brought along the Test and Test Runner types, which – in my experience, at least – haven’t seen much runtime (which is a pity, if you ask me). And now NAV 2015 brings along another type: the Upgrade codeunit type. The mere sound of it makes my heart thrill. The Upgrade codeunit. Mmm.

Simply put, the upgrade codeunit allows you to simplify the data upgrade process. And when I say “data upgrade” I am not referring merely to that tedious process of upgrading an older NAV to a newer NAV. I am referring to the process of making any change to the database schema that could otherwise cause data loss. That stuff that I talked in my yesterday’s post.

Let me start with that one first. Imagine you want to change a Decimal field to an Integer field. If you don’t feel like creating a table from scratch, in Cronus you can use the Item Unit of Measure and the Length field as the playground for this scenario. I’ll use it, even though I don’t recommend making this particular change in a production environment. So, go to the Item Unit of Measure, and change the type of the Length field from Decimal to Integer, and then try to save it with the default option (Now – with validation). You’ll get this:

A no brainer, really. NAV does not want to lose your data, so it rejects the change outright. I would imagine that you don’t really want to lose any information in there, you want to retain it, or in the worst case to end up having an old value rounded to the nearest integer. The upgrade codeunit can help (and this warning message gets to an inch of actually telling you as much).

In the upgrade codeunit, you can create a TableSyncSetup function, which provides instructions to NAV about how to proceed with schema changes for a specific table.  These instructions can be: copy the old data to a new table (this is good when you just want to change a type of a field), move the old data to a new table (this is good when the old data needs to be restructured, such as one row split into two, or more rows to be combined into one, or something of the sort), or to always just disregard any existing data and simply apply the change (if the old data is really useless).

Since we want to retain the data, just change the type, we will need the copy method. For that, we need a new table. So, take the Item Unit of Measure table, and save it under a new name and ID. I’ll use 50001 Item Unit of Measure (Temp). Since I am only interested in the Length column, I’ll have the following fields remaining:

(I have also deleted the irrelevant secondary keys, deleted all the trigger code, and saved the table using the Force synchronization method). If you are familiar with the data upgrade process of any upgrade project, then you’ll immediately recognize that this table follows the typical pattern of the intermediate tables used to temporarily store the data between steps 1 and 2 of the data upgrade.

So, let’s try this TableSyncSetup gizmo now to see if it walks the talk. Create a codeunit, and set its subtype to Upgrade. Then create a method called TableSyncSetup, of the following signature and body:

You must set its FunctionType property to TableSyncSetup, and declare a local variable of type Codeunit 9900 Data Upgrade Mgt. I’ve saved mine as 50001 Upgrade Item Unit of Measure.

Believe it or not, that’s all. Then only thing that remains now is to go into the Item Unit of Measure table, and change the field type of the Length field from Decimal to Integer, and click File > Save, and then confirm the default (Now – with validation) option. No error occurs. However, two things have happened:

1. NAV has copied the values from the Length field of the Item Unit of Measure table into the Item Unit of Measure (Temp) table.
2. NAV has deleted the values in the Length field of the Item Unit of Measure table.

So, you had a tiny little data upgrade project on the fly, without really having to code too much. The nice thing here, the copying of the data is actually done on the SQL Server Level in an extremely efficient way:

1. Rename the original table into a temporary name
2. Recreate the original table using the new schema
3. Re-populate the original table using the INSERT..SELECT statement
4. Drop the new table
5. Rename the renamed original table into the new table’s name
6. Drop the unnecessary columns from the new table

Nice and quick. It took all of the 13 milliseconds on my machine to complete.

The next step is obviously to put the data back into the old field, just rounded to the nearest integer. Easy peasy lemon squeezy. Go back to your upgrade codeunit, and create a new function (it will automatically be of the Upgrade type) and write this code in it:

Then, perform the data upgrade process. It’s now an integral part of the Development Environment (click Tools > Data Upgrade > Start), and is also available as a cmdlet in PowerShell.  I’ll use PowerShell (Waldo will love me for this):

And, if you go to the Item Unit of Measure table and run it, you can see that the Length column is now of Integer type, and that all of the old data is back (of course, rounded to the nearest integer).

So not only the upgrade process was significantly streamlined with this approach (no need to have long-running cursor-like loopie-loopies to copy data row by row in step 1), but you also get a fantastic tool to help you manage minor upgrades on live systems that have nothing to do with the upgrade project themselves. Essentially, whenever a database schema has to change for whatever reason, you have all the tools in place to help you retain the data that requires retention, or to delete the data that should bite the dust.

And when I say “whenever” I really mean it. In the example above I’ve changed the table directly in the database. However, imagine that I want to deploy a new data change to my customer’s environment by importing the modified original table, and at the same moment creating both the intermediary table with the synchronization and upgrade instructions within a new upgrade codeunit, something like this:

NAV is now smart enough to import the objects, and the ask you this:

If you click OK, it will ask you once again, just in case:

And, if you confirm this question, you get the schema nicely synchronized, with the old data nicely packed in the intermediary upgrade table. The only thing that remains is to run the data upgrade process, which is another click of a mouse.

Clean, efficient, and simply beautiful. Nice touch, Mr. Microsoft!