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.

Advertisements

NAV 2017: Wizard pages now supported in Web Client

Standard

As of October 2016, NAV 2017 is the latest version of the popular ERP system. To complement the brand new features, NAV team worked on enhancing current features.

A list of the features not supported in NAV 2016 is located here.  Among them, NavigatePage was not supported in NAV 2016 Web Client, but it is now supported in NAV 2017 Web Client.

Let’s delve into creating a simple (yet fit for starting your own) NavigatePage and check it in the Web Client. Additionally, I will suggest a few cases when a NavigatePage is a good candidate to collect data from users and perform some processing in one screen.

The NavigatePage assumes the existence of the following objects (included in the demo):

  • Table 90001 “Generic Entity” with the following fields:
    • field_1 (Code10)
    • field_2(Code10)
    • field_3(Code10)

PK: field_1,field_2,field_3

  • Table 90002 “Generic Entity Comment” with the following fields:
    • field_1 (Code10)
    • field_2(Code10)
    • field_3(Code10)
    • Entry No.(integer)
    • Comment(Text80)

PK: field_1,field_2,field_3,Entry No.

  • Page 90001 “Generic Entity Card”
    • An action to launch the NavigatePage will be located on this page

NavigatePage details:

navigatepage

And the actions:

navigatepageactions

Note: To make the actions appear as buttons on the toolbar and the groups as tabs set each action’s InFooterBar property to Yes.

This simple NavigatePage will collect data(the comment) from the user in Step1 and when the user clicks Next will insert a comment and make visible Step2.

When user clicks Next, the system will:

  • validate the fields in Step1
  • Hide the fields in Step1 group
  • Perform Wizard action (generate the comment)
  • Show Step2 group

When the user clicks on Finish the page closes.

A few snapshots of the NavigatePage as it appears on the Web Client:

genericentitycardpage

Launching Comment – Wizard action:

genericentitycommentwizzard

Click on Next will move the wizard into Step 2:

wizzardsummarystep

Standard NAV has a few NavigatePage pages. Check them out:

  • 5077 -> Create Interact
  • 5097 -> Create To-do
  • 5126 -> Create opportunity

If you have processes in which the users need to go through a few pages to enter data and create different entities, then NavigatePages are a great candidate for improving user efficiency and experience.

I used recently NavigatePages in two instances. Once, when I needed an unified screen to allow users to attach files (word docs, excel files, pdfs) in a Property Management AddOn. The documents were collected and attached to an email sent to the owner at month-end.

Another example was more recent when, with one NavigatePage, I was able to create records similar to a fixed asset, collect mandatory data for a purchase invoice and generate the purchase invoice for that record. Of course you need methods that will take care of generating the purchase invoice, its lines, general ledgers, vendor ledgers, dimensions. Moreover, assertiveness is needed to not end up with half-performed processes … But it’s possible to run your processes through a wizard page with an overall improved user experience and efficiency.

Sample code available here.