Upgrade Codeunits, made easy!

In this video, I show why there are no excuses for not using upgrade codeunits. Check it out:

https://youtu.be/5xAEc8OxbIo

In this video, Erik walks through a practical, real-world scenario that every AL developer will eventually face: you need to change a field’s data type on an existing table, but Business Central won’t let you. The solution? Upgrade codeunits. Erik demonstrates the entire process step-by-step — from hitting the error, to creating a replacement table, writing the upgrade codeunit, using upgrade tags, and marking the old table as obsolete.

The Problem: Destructive Schema Changes

Business Central is happy to let you add things — new fields, new tables, new keys. But certain changes are considered “destructive” and are simply not allowed during a normal upgrade. Changing a field’s data type is one of them.

Erik starts with a simple table called MyData with a Date field called “When.” The customer then comes back and says they need multiple entries on the same date — so the field needs to become a DateTime. Seems easy enough, right?

When Erik changes the field type and tries to deploy, Business Central throws an error:

“The field ‘When’ has changed the data type from NAV Date to NAV DateTime. Changing the data type of fields is not allowed.”

Even incrementing the version number doesn’t help — the schema change is simply rejected. So what do you do?

The Strategy: Create a Replacement Table

The key insight is that the original table is done. You can’t modify it in a breaking way, so instead you create a new table with the corrected schema. Here’s the new table, MyData2 (object ID 50101), with the When field changed to DateTime:

table 50101 MyData2
{
    Caption = 'MyData';
    DataClassification = ToBeClassified;

    fields
    {
        field(1; Customer; Code[20])
        {
            Caption = 'Customer';
        }
        field(2; When; DateTime)
        {
            Caption = 'When';
        }
        field(3; What; Text[100])
        {
            Caption = 'What';
        }
    }
    keys
    {
        key(PK; Customer, WHen)
        {
            Clustered = true;
        }
    }
}

All references throughout the app — pages, codeunits, reports — need to be updated to point to the new table. In this case, the page is straightforward:

page 50100 MyDataView
{
    ApplicationArea = All;
    Caption = 'MyDataView';
    PageType = List;
    SourceTable = MyData2;

    layout
    {
        area(content)
        {
            repeater(General)
            {
                field(Customer; Rec.Customer)
                {
                }
                field(When; Rec.When)
                {
                }
                field(What; Rec.What)
                {
                }
            }
        }
    }
}

After deploying with an incremented version number, the new table exists — but it’s empty. The data is still sitting in the old table. This is where the upgrade codeunit comes in.

Writing the Upgrade Codeunit

An upgrade codeunit is a special codeunit with Subtype = Upgrade. It has several triggers available, but the two most important ones are:

  • OnUpgradePerCompany — runs once for each company in the database. Use this for company-specific (per-company) data tables.
  • OnUpgradePerDatabase — runs once total. Use this for tables shared across all companies.

Since MyData is a per-company table, Erik uses OnUpgradePerCompany. Here’s the complete upgrade codeunit:

codeunit 50104 "Upgrade 1"
{
    Subtype = Upgrade;

    trigger OnUpgradePerCompany()
    var
        Old: Record MyData;
        New: Record MyData2;
        Tag: Codeunit "Upgrade Tag";
        MyTag: Label 'MYDATA_UPGRADE1';
    begin
        if Tag.HasUpgradeTag(MyTag) then
            exit;
        if Old.FindSet() then
            repeat
                New.Init();
                New.Customer := Old.Customer;
                New.When := CreateDateTime(Old.When, 0T);
                New.What := Old.What;
                New.Insert();
            until Old.Next() = 0;
        Tag.SetUpgradeTag(MyTag);
    end;
}

Let’s break down the key parts.

The Data Migration Loop

The pattern is the classic FindSet / repeat...until Next() = 0 loop. For each record in the old table, a new record is created in the new table with the field values mapped across:

New.Init();
New.Customer := Old.Customer;
New.When := CreateDateTime(Old.When, 0T);
New.What := Old.What;
New.Insert();

Note the use of CreateDateTime(Old.When, 0T) to convert the old Date value into a DateTime. The 0T represents midnight (00:00:00). Erik points out that the actual time displayed may differ depending on your timezone — he saw 5:00 PM because his environment was on the US west coast and the value was stored as UTC midnight.

Why Not Use TransferFields?

You might think TransferFields would be a nice shortcut here. Erik considered it, but since field number 2 exists in both tables with different data types (Date vs. DateTime), TransferFields would fail. If you had given the new field a different field number, TransferFields would work for the compatible fields — but you’d still need to handle the type conversion manually.

Upgrade Tags: Preventing Re-runs

Upgrade codeunits run every time the extension is upgraded. Without protection, the data migration code would execute again on every future version bump — potentially duplicating data or causing errors. This is where upgrade tags come in.

The pattern is simple:

  1. Check if the tag already exists: if Tag.HasUpgradeTag(MyTag) then exit;
  2. Do the upgrade work.
  3. Set the tag so it won’t run again: Tag.SetUpgradeTag(MyTag);

The tag itself is just a string label — Erik uses 'MYDATA_UPGRADE1'. Make it descriptive enough that you (and your team) know what it refers to.

Testing Upgrade Codeunits with launch.json

One practical tip Erik shares: when developing and testing upgrade codeunits locally, you can add a setting in your launch.json to force the upgrade to always run:

"forceUpgrade": true

This is invaluable during development because it ensures the upgrade triggers fire every time you deploy, regardless of whether the version number changed. Before this feature existed, it was much harder to tell if your upgrade code actually ran.

Marking the Old Table as Obsolete

Once the migration is in place, you need to prevent anyone (including yourself) from accidentally using the old table. AL provides the ObsoleteState and ObsoleteReason properties for exactly this purpose:

table 50100 MyData
{
    Caption = 'MyData';
    DataClassification = ToBeClassified;
    ObsoleteState = Removed;
    ObsoleteReason = 'Replaced by MyData2';

    fields
    {
        field(1; Customer; Code[20])
        {
            Caption = 'Customer';
        }
        field(2; When; Date)
        {
            Caption = 'When';
        }
        field(3; What; Text[100])
        {
            Caption = 'What';
        }
    }
    keys
    {
        key(PK; Customer, WHen)
        {
            Clustered = true;
        }
    }
}

Erik recommends a two-stage approach:

  • ObsoleteState = Pending — generates compiler warnings. This gives other developers (or dependent apps) time to migrate their code. The table is still accessible.
  • ObsoleteState = Removed — generates compiler errors. No one can reference this table anymore in normal code.

The clever part: even when a table is marked as Removed, upgrade codeunits can still access it. This is by design — the upgrade code needs to read the old data to migrate it. So your upgrade codeunit will compile and run just fine even after the old table is fully obsoleted.

If your extension has dependent apps (other extensions that reference your tables), starting with Pending is especially important. It gives those developers a deprecation warning rather than immediately breaking their builds.

Organizational Tips

Erik mentions that he prefers to create a separate upgrade codeunit for each migration rather than packing multiple upgrades into a single codeunit with multiple conditional blocks. This keeps things clean and easy to reason about — each codeunit has one job and one trigger.

What About Large Data Sets?

Erik acknowledges that this straightforward record-by-record approach works well for small to medium data sets, but if you’re migrating millions of records, there are better approaches — specifically the Data Transfer codeunit (available in newer runtimes), which can move data between tables much more efficiently. That’s a topic for another video.

A Word on Anti-Patterns

Erik calls out a common anti-pattern he’s seen — even from Microsoft — where developers put upgrade logic in page triggers (like OnOpenPage) so the first user who opens a page unknowingly runs migration code. This is fragile, unpredictable, and the wrong approach. Upgrade codeunits are the correct, supported mechanism for data migration during extension upgrades.

Summary

Here’s the complete workflow for handling a breaking table change in AL:

  1. Create a new table with the corrected schema (new object ID).
  2. Update all references (pages, codeunits, reports) to use the new table.
  3. Write an upgrade codeunit (Subtype = Upgrade) that reads from the old table and writes to the new one, handling any data type conversions.
  4. Use upgrade tags to ensure the migration only runs once.
  5. Mark the old table as obsolete — first Pending, then eventually Removed.
  6. Increment your app version and deploy.

It’s not as scary as it might seem, and it’s significantly better than any workaround. For a deeper dive into the full upgrade codeunit lifecycle, Erik recommends checking out Waldo’s detailed blog post on the subject.

Picture of hougaard

hougaard