NAV Extensions – Updated Generic Data Migration Options

OK, it’s official. I feel like an idiot :-).

I was so proud with my previous blogpost as I was able to solve a somewhat complicated problem generically. And now there appears to be a platform solution for what I was trying to solve (or at least for a big part of it).

I remember to have provided feedback to Microsoft on exactly this topic: restore data generically with one statement. Not saying it’s because of me that it’s in the product, only saying it makes a lot of sense that there is a platform statement to just “restore what you can restore“.. .

New NAVAPP Statements

Look at this:

I’m very sure the NAVAPP.DELETEARCHIVEDATA and NAVAPP.RESTOREARCHIVEDATA (we’ll focus on this one) were added after the RTM release. In fact, I took the time to find out the following:

  • NAV2016 CU1 did not contain these statements
  • NAV2016 CU2 DID contain these statements – so from this update, you can benefit from the new statements
  • NAV2016 CU3 .. Duh ..

Though, the description of the Cumulative Update did not mention one single thing about this. This is confusing, Microsoft! :-/. There isn’t even one MSDN page that explains this, nor the help that comes with the DVD is updated. I’m guessing this is pretty new ;-).

Anyway .. as said, there are two very simple new statements:

  • NAVAPP.DELETEARCHIVEDATA(TableID);
  • NAVAPP.RESTOREARCHIVEDATA(TableID);

And there is not much explanation needed, I guess. With one statement, you can restore or delete the data that is in the archive. This is obviously only useful when you know what you’re doing, and you always want to completely restore all Extension-fields from that specific table.

Then again, it might make you wonder how it behaves in certain circumstances.

  1. Well, the RESTOREARCHIVEDATA statement requires that all fields existing in the archive table, also still exist in the new version of the extension destination table. If there is one field missing (if you deleted a field in the new version of your extension), it will end up in an error:

Which means in these cases, you won’t be able to use anything generic, but do it the hard way: write your own upgrade logic. You will get this kind of error message:

  1. Another case is: what if there is a New field in the new version of the extension, that doesn’t exist in the archive table. Well, there is no problem in using the RESTOREARCHIVEDATA statement, as long as the new fields are fine using a default value.
  2. Last but not least: when a record was removed after uninstalling the extension, would mean there is a record in the archive table that can’t be found when restoring the archived data. Well, in that case, it’s just going to skip the record during restore.

I tested these scenario’s and it actually really works very well. I’m sure you can come up with other scenario’s .. please don’t hold back to test them, and report back in the comment section of this blog ;-).

You still need to loop through some tables
Obviously, you being able to use a generic codeunit that will always work, really depends on quite some assumptions. But let’s try to rework the codeunit in my previous blogpost, with the new statements:

OBJECT Codeunit 69611 Rental Extension Upgrade Mgt.
{
  OBJECT-PROPERTIES
  {
    Date=19/01/16;
    Time=23:10:40;
    Modified=Yes;
    Version List=RA1.00;
  }
  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;
    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
                NAVAPP.RESTOREARCHIVEDATA("Object ID");
              END;
          UNTIL NEXT < 1;
      END;
    END;

    LOCAL PROCEDURE RestoreAppTables@1100084006(FromTableID@1100084003 : Integer;ToTableID@1100084004 : Integer);
    VAR
      AllObj@1100084005 : Record 2000000038;
    BEGIN
      WITH AllObj DO BEGIN
        SETRANGE("Object Type", "Object Type"::Table);
        SETRANGE("Object ID", FromTableID,ToTableID);

        IF FINDSET THEN
          REPEAT
            NAVAPP.RESTOREARCHIVEDATA("Object ID");
          UNTIL NEXT < 1;
      END;
    END;

    BEGIN
    END.
  }
}

As you can see, it’s a lot shorter – just because of the fact that there is a lot less code needed:

  • No juggling around with RecRef, FieldRef, Keys, …
  • No looping of fields and transferring data one-by-one

As a matter of fact, there are only the two fuctions that are going to figure out in which default tables you have added fields .. and which new tables you have created – the same way: by providing a object range.

Hope you’ll enjoy!

5.00 avg. rating (98% score) - 2 votes

Permanent link to this article: https://www.waldo.be/2016/01/20/nav-extensions-updated-generic-data-migration-options/

2 comments

7 pings

Skip to comment form

  1. congratulations on your recommendation being accepted! It is of great service to all of us.
    I am a great believer of Microsoft doing something once for all of us, instead of having to do it a thousand times with lots of code.
    Since they hear the MVPs, can you also suggest to them they add an upgrade option that does not require removing all data every time you do an update?
    If you add fields as extensions to large tables like ledgers that could have tens of millions of rows., it could take a while to back the data out and then restore it.
    We could also benefit from the ability to see added fields and tables and use them in custom reports – maybe they could make that possible for single tenant databases.

      • waldo on January 20, 2016 at 9:58 am
        Author

      I doubt that it was my feedback that caused this to be into the product ;-).

      The concern you have is shared with many, and reported many times already.

      as far as I know 😉

  1. […] Continue reading » […]

  2. […] NAV Extensions – Updated Generic Data Migration Options […]

  3. […] Continued on Source: NAV Extensions – Updated Generic Data Migration Options » waldo's blog […]

  4. […] UPDATE: Updated info can be found in this post. […]

  5. […] Bron : Waldo’s Blog Lees meer… […]

  6. […] explains in this blog how to work with upgrade codeunits and how to make them generic. I don’t think it makes sense […]

  7. […] explains in this blog how to work with upgrade codeunits and how to make them generic. I don’t think it makes sense […]

Leave a Reply to davmac1 Cancel reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.