Recently, in our product, we enabled support for the new “Item References” in Business Central. Basically meaning: when upgrading to the new version of our product, we wanted to:
- Make sure our code supported the new “Item Reference” table in stead of the old “Item Cross Reference” table
- Automatically enable the “Item Reference” feature” (necessary, because when your code depends on this new feature, it has to be enabled ;-)).
- Automatically trigger the data upgrade (which basically transfers all data from one table to the other)
Obviously, this is all done using an upgrade codeunit. And this made me realize that there are some things that might not be too obvious to take into account when doing things like this. So .. blogpost on upgrade codeunits 😉.
The base mechanics
As such, an upgrade codeunit is quite simple. It’s a codeunit that is run when you’re upgrading an app from version x to version y (which is higher, obviously). In this codeunit, you will typically move data from one place (field or table) to another.
It’s a codeunit with the subtype “Upgrade” which basically enables six available triggers.
You can use the “precondition” trigger to test whether an upgrade is possible or not. If not: raise an error, and the upgrade process will be rolled back, and the previous app will still be available (if you’re running the right upgrade procedure ..).
OnUpgradePerCompany / OnUpgradePerDatabase
In the “OnUpgrade” trigger, you’ll typically put the upgrade code
OnValidateUpgradePerCompany / OnValidateUpgradePerDatabase
And in the end, you’d be able to validate your upgrade. Again: if not valid (let’s say, if the field still has content), you can again raise an error to rollback the upgrade process.
Avoid running the upgrade multiple times
To track whether an upgrade has run or not, Microsoft created a system that they call “Upgrade Tags”.
Upgrade tags provide a more robust and resilient way of controlling the upgrade process. They provide a way to track upgrade methods have been run to prevent executing the same upgrade code twice. Tags can also be used to skip the upgrade methods for a specific company or to fix an upgrade that went wrong.Microsoft Docs
Son, you’ll have to take the upgrade tags into consideration, which means:
- Create/manage a unique tag for every single upgrade method (usually including a companyname, a reason and a date)
- Do NOT run the upgrade if the tag is already in the database
- Add the tag in the database when you ran the upgrade
- Add the tag in the database when you create a new company
The latter, if often forgotten, by the way.. . But obviously very important.
This, my friends, calls for a template pattern (if I may call it that – people have been quite sensitive about that word 🙄) for an upgrade codeunit. But let’s leave that for later, when I talk about – yes AJ – SNIPPETS! 🤪
Update – AJ and Stefan Maron made some comments down below that are worth mentioning:
When you install (not upgrade!) an app, you have to make sure that all upgrade-tags are pre-registered. You can do that by simply call the
UpgradeTag.SetAllUpgradeTags(); in an install codeunit (You’ll find a snippet for an install-codeunit in “waldo’s CRS AL Language Extension” under “tinstallcodeunitwaldo”).
Up until now, you’d be able to find the info in the default Microsoft Docs documentation , which is already quite elaborate .. but not elaborate enough.
Don’t remove data!
- The base app upgrades data you depend on
- Or an ISV product upgrades data you depend on
- Let’s say that dependency is a field that you added in that (now obsolete) table, and has to be moved to your new field.
- Or it could also be some business logic, where you depend on a value of a field which is not completely obsolete, and handled differently
In any case – it might very well be that the depending app will need this data to do whatever: move its own fields out, or move default data to new extension data, or .. Whatever.
In other words:
You (as Microsoft or as ISV) simply can’t assume nobody needs the data anymore. You really can not.
In other words:
Don’t delete the data. Move it to another field/table, make the original field/table obsolete. But that’s it! Keep the data and don’t kid yourself you need to delete it from the obsolete table, just because you think that’s cleaner. I can’t stress that enough! Just assume that in the upgrade process, there will be a dependent app that will be installed after your app, so which will run the upgrade after you as well. It will need the data. Keep it. Just keep it.
Don’t delete it like we did 😉. Yep, we didn’t think about this, and we messed up. And we had to redo the upgrade. No harm done – we noticed quite fast – but still. I hope I prevented at least one (possibly major) problem with this blogpost ;-).
Preserve the SystemId!
Another not-so-obvious thing but in my book VERY important, is the SystemId. When you’re transferring a complete table (which in my case, was the “Item Cross Reference” to the “Item Reference” table, think about transferring the SystemId as well!
I mean, the SystemId might be the primary key for anything Power-stuff, or any third-party application that is interfacing with your system through APIs. When you would simply transfer like this:
It would create a new SystemId for every record in the new table. Basically meaning all these records would be “new” for any interfacing system.
And guess what – this was the version of Microsoft which I was lucky enough to catch in time. In the meantime, Microsoft has fixed it – but I do hope they’ll remember for any other table in the future that will be transferred to a new table.
The fix is quite simple:
ItemReference.Init(); ItemReference.TransferFields(ItemCrossReference, true); ItemReference.SystemId := ItemCrossReference.SystemId; ItemReference.Insert(false, true);
Just set the SytemId, and insert with the second “true”. Didn’t know this second boolean existed? Well, here is the official docs: Record.Insert Method (it turns out there are different pages about the insert method – it took me a while to find the right one.. ).
As promised, I have some snippets for you, and they are already in “waldo’s CRS AL Language Extension“. The three snippets concerning upgrades are:
This snippet has the base mechanics of what I was talking about above. You see the upgrade tag, the event to subscribe to to manage upgrade tags in new companies. This snippet will also create a default way to create a tag, including the current date. How cool is that ;-).
The script deviates a bit from what was described on Microsoft Docs. I just like this much better as everything about this upgrade is nicely encapsulated in one codeunit, including the event.
This snippet is meant to be used in an upgrade codeunit, and will generate code to move all data from one table to the other. And you see already that it handles the SystemId correctly as well. Again – very important!
As the last line, you’ll find the procedurecall, which is meant to be moved in the right “OnUpgrade…”-trigger.
Quite similar to above, but this snippet is intended to move one field to another field. And again, the procedurecall at the end is to be moved in the right trigger.
That was it! If there is anything I left out, I’d be happy to know! You can leave a comment, you can send me a message, .. . I’m always happy to hear!