NAV Extensions – Generic Code To Migrate Data When Upgrading Extensions

UPDATE: Updated info can be found in this post.

This is not a post on how Extensions work – I guess there is a lot of material out there that already handles Extensions. And if you don’t find any .. just check YouTube for a few video’s, like:

• How Do I: Create an Extension Package in Microsoft Dynamics NAV 2016
• How Do I Deploy and Manage Extensions
• My “Thinking Outside The Box” session at NAVTechDays for a few best practices 😉

This small post is about migrating data from one version of your extension to another.

When you’re upgrading an extension, you’re actually doing that in a few steps:

1. Publish a new version of your extension
2. Uninstall the current installed version of the extension in Tenant X
3. Install the new published version of the extension in Tenant X

Now, uninstalling an extension means it’s going to kill any change you did on the application, which also means it’s going to remove fields, and even entire tables.

What happens with the data then?

Well, to not go too deep into details: your data is moved to a new table in the SQL Server Database, like you see here:

The $AppData$… table is holding all the data with the fields and values that you would have lost when uninstalling the Extension.

So how do I get the data in?

Well, it’s all quite well explained on MSDN: Extending Microsoft Dynamics NAV Using Extension Packages. In short, you have to create a codeunit that holds at least one of these global function names:

• OnNavAppUpgradePerDatabase()
• OnNavAppUpgradePerCompany()

Indeed: they need to exist in your extension (part of your delta’s) as global functions. Weird approach, isn’t it? But it works – and you can even guess how it works: it’s going to call the “..PerDatabase” only once per database (so, only once), and the other once in each company. That’s where you will put your upgrade code.

How do I get to the data in code?

To get to your saved data, you have the statement “NAVAPP.GETARCHIVERECORDREF”. You need to provide a tableID, and a RecRef variable. The latter will contain the archived data of the table you mentioned. So this needs to be done for all the tables you have touched in your Extension .. new and modified!

Now, one point of concern: This recref is NOT based on the original table, which you might have expected. So you will not be able to do something like DestinationRecRef := SourceRecRef , or DestinationRecRef.GET(SourceRecRef) … or anything in that sort. There is only the long way (as far as I know) .. the only thing you can do, is looping the fields, and transfer the data field-by-field back to where it belonged. You have to manage lost data, lost fields, conversions, … everything in code.

So, imagine, if you need to foresee upgrade code for every field .. you might want to do this as generic as possible – else, at some point in the future, you WILL forget to add upgrade code for a new field you added in a new version of the Extension .. .

Let me show you one way to do this generically .. not claiming it’s the only way, or the best way .. It’s just ‘a’ way 😉

I will share the codeunit at the end of this blogpost. But let’s first look at the main function:

And this explains it all already :-). I have two functions and a few assumptions (I know .. they probably are mother of all f***ps.. but still ..).

When you design tables in an extension, you will either change a default table, of you will add new tables. So it’s these situations that are handled in the two functions. On top of that, I need to do a few assumptions:

• The new fields in the modified tables, are in a specified range. In this case: 50000..99999. In this case, other fields will be skipped. Beware! The code is looping all the fields in default tables
• For the new table, I need to loop ALL fields. In this case, I assume the new tables are in a specific number range. In this case, once again, 50000..99999.

Makes sense, but still, it’s assumptions, and you should be aware!

But taking this in account, it’s possible to just loop the Fields-table / Object table, and you’re good to go. With only one remark: don’t loop the Object table (that table isn’t aware of the Extension-objects), but loop the AllObj-table, like you see in the code below.

That’s about it. The rest is quite straight forward. Here is the codeunit – enjoy!

OBJECT Codeunit 69611 Rental Extension Upgrade Mgt.
{
  OBJECT-PROPERTIES
  {
    Date=07/01/16;
    Time=23:31:55;
    Modified=Yes;
    Version List=CloudReadySoftware;
  }
  PROPERTIES
  {
    OnRun=BEGIN
          END;

  }
  CODE
  {

    PROCEDURE OnNavAppUpgradePerDatabase@1100084000();
    BEGIN
    END;

    PROCEDURE OnNavAppUpgradePerCompany@1100084001();
    BEGIN
      RestoreFieldsInModifiedTables(50000,99999);
      RestoreAppTables(50000,99999);
    END;

    LOCAL PROCEDURE RestoreFieldsInModifiedTables@1100084005(FromField@1100084004 : Integer;ToField@1100084005 : Integer);
    VAR
      Field@1100084001 : Record 2000000041;
      AllObj@1100084000 : Record 2000000038;
      SourceRecRef@1100084002 : RecordRef;
      DestinationRecRef@1100084003 : RecordRef;
      KeyRef@1100084006 : KeyRef;
    BEGIN
      WITH AllObj DO BEGIN
        SETRANGE("Object Type","Object Type"::Table);

        IF FINDSET THEN
          REPEAT
              Field.SETRANGE(TableNo, "Object ID");
              Field.SETRANGE("No.", FromField, ToField);
              IF NOT Field.ISEMPTY THEN BEGIN
                IF NAVAPP.GETARCHIVERECORDREF("Object ID", SourceRecRef) THEN BEGIN
                  IF SourceRecRef.FINDSET THEN
                    REPEAT
                      DestinationRecRef.OPEN("Object ID",FALSE);
                      IF GetRecRefFromRecRef(SourceRecRef,DestinationRecRef) THEN BEGIN
                        TransferCustomFieldRefs(SourceRecRef,DestinationRecRef,FromField,ToField);
                        DestinationRecRef.MODIFY;
                      END ELSE BEGIN
                        ERROR('Destination record not found.  Data would be lost.  RecordID: %1',FORMAT(SourceRecRef.RECORDID));
                      END;
                      DestinationRecRef.CLOSE;
                    UNTIL SourceRecRef.NEXT < 1;
                 END;
              END;
          UNTIL NEXT < 1;
      END;
    END;

    LOCAL PROCEDURE RestoreAppTables@1100084006(FromTableID@1100084003 : Integer;ToTableID@1100084004 : Integer);
    VAR
      SourceRecRef@1100084002 : RecordRef;
      DestinationRecRef@1100084001 : RecordRef;
      Field@1100084000 : Record 2000000041;
      AllObj@1100084005 : Record 2000000038;
    BEGIN
      WITH AllObj DO BEGIN
        SETRANGE("Object Type", "Object Type"::Table);
        SETRANGE("Object ID", FromTableID,ToTableID);

        IF FINDSET THEN
          REPEAT
            IF NAVAPP.GETARCHIVERECORDREF("Object ID", SourceRecRef) THEN BEGIN
              IF SourceRecRef.FINDSET THEN
                REPEAT
                  DestinationRecRef.OPEN("Object ID",FALSE);

                  TransferFieldRefs(SourceRecRef,DestinationRecRef);
                  DestinationRecRef.INSERT;

                  DestinationRecRef.CLOSE;
                UNTIL SourceRecRef.NEXT = 0;
            END;
          UNTIL NEXT < 1;
      END;
    END;

    LOCAL PROCEDURE TransferFieldRefs@1100084004(VAR SourceRecRef@1100084000 : RecordRef;VAR DestinationRecRef@1100084001 : RecordRef);
    VAR
      Field@1100084002 : Record 2000000041;
      SourceFieldRef@1100084003 : FieldRef;
      DestinationFieldRef@1100084004 : FieldRef;
    BEGIN
      WITH Field DO BEGIN
        SETRANGE(TableNo, DestinationRecRef.NUMBER);
        IF FINDSET THEN
          REPEAT
            IF SourceRecRef.FIELDEXIST(Field."No.") THEN BEGIN
              SourceFieldRef := SourceRecRef.FIELD(Field."No.");
              DestinationFieldRef := DestinationRecRef.FIELD(Field."No.");
              DestinationFieldRef.VALUE := SourceFieldRef.VALUE;
            END;
          UNTIL NEXT = 0;
      END;
    END;

    LOCAL PROCEDURE TransferCustomFieldRefs@1100084015(VAR SourceRecRef@1100084000 : RecordRef;VAR DestinationRecRef@1100084001 : RecordRef;FromField@1100084005 : Integer;ToField@1100084006 : Integer);
    VAR
      Field@1100084002 : Record 2000000041;
      SourceFieldRef@1100084003 : FieldRef;
      DestinationFieldRef@1100084004 : FieldRef;
    BEGIN
      WITH Field DO BEGIN
        SETRANGE(TableNo, DestinationRecRef.NUMBER);
        SETRANGE("No.",FromField,ToField);
        IF FINDSET THEN
          REPEAT
            IF SourceRecRef.FIELDEXIST(Field."No.") THEN BEGIN
              SourceFieldRef := SourceRecRef.FIELD(Field."No.");
              DestinationFieldRef := DestinationRecRef.FIELD(Field."No.");
              DestinationFieldRef.VALUE := SourceFieldRef.VALUE;
            END;
          UNTIL NEXT = 0;
      END;
    END;

    LOCAL PROCEDURE GetRecRefFromRecRef@1100084007(VAR SourceRecRef@1100084001 : RecordRef;VAR DestinationRecRef@1100084000 : RecordRef) : Boolean;
    VAR
      KeyRef@1100084002 : KeyRef;
      i@1100084003 : Integer;
      FieldRef@1100084004 : FieldRef;
      SourceFieldRef@1100084005 : FieldRef;
      DestinationFieldRef@1100084006 : FieldRef;
    BEGIN
      KeyRef := DestinationRecRef.KEYINDEX(1);
      FOR i := 1 TO KeyRef.FIELDCOUNT DO BEGIN
        FieldRef := KeyRef.FIELDINDEX(i);

        SourceFieldRef := SourceRecRef.FIELD(FieldRef.NUMBER);
        DestinationFieldRef := DestinationRecRef.FIELD(FieldRef.NUMBER);

        DestinationFieldRef.VALUE := SourceFieldRef.VALUE;
      END;

      IF NOT DestinationRecRef.FIND('=') THEN
        EXIT(FALSE)
      ELSE
        EXIT(TRUE);
    END;

    BEGIN
    END.
  }
}

One big disclaimer though .. I did NOT test this code thoroughly. But I did test it somewhat ;-). If there are issues, you’re always welcome to comment, and I’ll try to look at it!