NAV Upgrade Notes – First essential step for a successful NAV upgrade

Standard

The first step in my last NAV upgrade was to back up customized tables. Although having database backups throughout your upgrade process is a no brainer, my focus in this post is backing up locally tables that contain non-standard fields. Having the heavy customized tables readily available in the same database, somewhere in the range 50000-99999 it can prove useful.

For example, let’s say the table 21 Customer Ledger Entry in my customer NAV 2009 database has a few non-standard fields in the range 50k or some other range. My plan is to have an exact copy of this table with ID 50000, table that I can carry forward until the upgrade process reaches NAV 2017.

Having the original data allows me to:

  •  re-construct any field that might have gotten lost or overriden during the upgrade (through a Forced sync without an upgrade codeunit or some other faulty process) or
  • perform conversion validation like comparing the balance for each customer in the original table(now saved in the 50k range) with the value from the upgraded table.

To backup the table I start with opening the original table (21), copy all fields, create a new table in the 50k range (60007) and paste all the fields. In the new table we don’t need any business rules, therefore we can select all code lines and delete them.

I would do the same for all tables whose data I want to back up.

If your backup set includes like mine, hundreds of tables, I would create a new table(50000) where I am planning to keep all original-backed up table pairs. For example, I can have pairs like table 21 – table 60007 in the new table:

List Of Tables

Next we need to populate the new table 60007 with original data.

Create a new codeunit and traverse table 50000. For each row execute the following function:

CopyData

This method copies the value of “Normal” fields from the source table(21) into each correspondent field from target table(60007).

How to use CAPTIONCLASSTRANSLATE in Dynamics NAV

Standard

Not long ago I stumbled upon a question on the popular NAV forum dynamicsuser.net. The question was “What is the use of CAPTIONCLASSTRANSLATE command in NAV?”.
There was only one answer coming from one of the most influential experts in the NAV world, Luc Van Vugt, but his answer intrigued me even more. As msdn was not very helpful, I started digging into this function use.

First, I wanted to see where is used in standard NAV 2017 in the Cronus database. To find all occurrences of CAPTIONCLASSTRANSLATE in standard NAV I used the Object Manager produced by idyn , a NAV AddOn for managing C/AL code. For my readers that do not have access to this development tool, export all objects to a text file and search in the file for “CAPTIONCLASSTRANSLATE”.

std-nav-use

First occurrence was in report 13, VAT register:

rep13

The report is using the command CAPTIONCLASSTRANSLATE to populate the value of a control(VATEntryClosedCaption) with the caption of “VAT Entry”.Closed field.

Similarly, CAPTIONCLASSTRANSLATE is used in report 22 “No. Series Check”, report 35 “Document Entries”, report 122 “Reminder – Test”, report 123 “Finance Charge Memo – Test”, report 1403 “Bank Account Register”, report 5900 “Service Order”.

A more intriguing way of using the command CAPTIONCLASSTRANSLATE is found in the other objects  as following:

employeelistreport

As we can see above, CAPTIONCLASSTRANSLATE receives a string parameter consisting of three sub-strings separated by two commas.

CAPTIONCLASSTRANSLATE(‘i,j,k’)

By investigating the code in Codeunit 42 “CaptionManagement” and Codeunit 57 “Document Totals” I could draw the following conclusions:

If i = 1 then CAPTIONCLASSTRANSLATE will output Dimension codes.

If j = 1 then CAPTIONCLASSTRANSLATE will output Global Dimensions code.

If k = 1 (i=1,j=1) then CAPTIONCLASSTRANSLATE will output “Global Dimension 1 Code”

If k = 2 (i=1,j=1) then CAPTIONCLASSTRANSLATE will output “Global Dimension 2 Code”

If i = 1 and j = 2 then CAPTIONCLASSTRANSLATE will output Shortcut Dimension Codes(for k=1..8).

dimensioncodes

For printing field captions that have (LCY) in caption use i = 101.

For example to display Amount(LCY) use CAPTIONCLASSTRANSLATE like below:

lcy

To display captions of amount fields that contain the text “Incl. VAT” or “Excl. VAT” use CAPTIONCLASSTRANSLATE with i =2 and j = 0 (to display Excl. VAT) or j = 1 (to display Incl. VAT) as in the examples below:

vat

All examples of CAPTIONCLASSTRANSLATE found in NAV standard were in reports and used to display captions in the table headers or group totals in report layouts.

 

 

 

Day-to-day NAV: Upgrade codeunits

Standard

Starting with NAV 2015 there is an easier way to migrate data between old and new structures of NAV tables.

From msdn excerpt, the upgrade codeunits role is to provide “the logic for migrating existing data in the business data table from the old format to the new format after schema synchronization”.

When we operate a change to the structure of a table the system will detect there are discrepancies between what we just saved through the development environment and what is current in SQL Server.

When we save the object we can switch between three options available for “Synchronize schema”. By default the value is “Now – with validation”.  If we choose “Later” the object is saved but no schema synchronization is performed. If we choose “Force”, the object is saved and the new structure is operated at the SQL Server  level with potential data loss.

If we choose “Now – with validation”, the system will attempt to synchronize schema and because there are structural changes it will check the existence of an upgrade codeunit that can perform the changes without data loss. If such an upgrade unit does not exist we get the error above.

To see the upgrade codeunit in action, I will:

  1. Create a table
  2. Populate it
  3. Change its structure:
    • change the ID# of a field
    • change the data type of a field
    • insert new field in the freed ID#
  4. Confirm schema sync error
  5. Create upgrade codeunit
  6. Attempt to save the object with Schema Synchronization “Now – with validation”
  7. Start the upgrade process
  8. Check upgrade output

Let’s create a table, populate it with some data and attempt to change its structure.

1. Create a table similar to the one below:

1-table

2. Create a codeunit to populate the newly created table:

codeunit-populate

   3. Let’s change the structure of the table as following:

  • ID 6 Status (Option) -> ID 20 Status (Option) [Update1]
  • ID 4 Field_3 (Integer) -> ID 4 Field_3(Code10) [Update2]
  • ID 6 Description (Text30) (new field) [Update3]

Check the current status of the table pre-upgrade in Management Studio

struct-pre-upgrade

4. If trying to save the object with Schema synchronization “Now – with validation” after [Update1] and [Update2] we get this error:

error1

If we try to save the object after [Update1],[Update2] and [Update3]

we get this error:

error2

Notice how the system reports only the last update for field 6, I assume because the Update1 is not committed.

5. Let’s now create the upgrade codeunit:

Start by setting up the Subtype property of the codeunit to “Upgrade”.

The codeunit consists of two functions:

  • A function that will back up the data from our table into a temporary table. [My temporary table is pretty much a copy of the original table. But if you’re not changing a lot of fields in your process, I suggest having in your temp table just the fields in the primary key and the fields that are affected.]. Set the FunctionType property of this function to TableSyncSetup.

tablesyncsetupfnA function that will populate the new structure with the data from the temporary backup table … something like a “forced” schema synchronization with data restore from a backup. Set the FunctionType property of this function to UpgradePerCompany

restoredata

6. Save the object (compile option for schema synchronization Now – with validation). No errors should be encountered at this point if the upgrade codeunit was created.

7. Start the upgrade from the development environment (Tools-Data Upgrade – Start …) or if you prefer powershell, run in Administration Shell: Start-NAVDataUpgrade [InstanceName]

8. Check the content of the table:

afterupgrade

I used upgrade codeunits in day-to-day tasks(recently changing the data type of a field from integer to code), and in upgrade projects when existent fields have been relocated to different IDs.

When the Data Upgrade processes is suspended you can identify potential errors in your upgrade codeunit by running a powershell command: Get-NAVDataUpgrade [InstanceName] -ErrorOnly

But it’s not possible to step through the codeunit with the debugger … or, to be more precise, I couldn’t find how.

Sample code here.