«

»

Print this Post

NAV Extensions – Updated Generic Data Migration Options

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

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:

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!

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

2 comments

7 pings

Skip to comment form

  1. davmac1

    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.

    1. waldo

      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. NAV Extensions – Generic Code To Migrate Data When Upgrading Extensions » waldo's blog

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

  2. NAV Extensions – Updated Generic Data Migration Options – Jan 20, waldo’s blog

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

  3. NAV Extensions – Generic Code To Migrate Data When Upgrading Extensions - Microsoft Dynamics NAV Community

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

  4. NAV Extensions – Updated Generic Data Migration Options | Pardaan.com

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

  5. NAV 2016 | My Second Extension - Mark Brummel - Dynamics NAV Users

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

  6. NAV 2016 | My Second Extension - Microsoft Dynamics NAV Community

    […] 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

%d bloggers like this: