Business Central Musings

Why You Can't Delete That Dimension Value in Business Central (A Look Under the Hood)

11 min read AL Development

Hello Readers!

A few days ago on the Dynamics Community forum somebody asked how to trace Dimension Set ID 3 back to the record that is blocking the delete of a dimension value. Five people answered “block it”. They were right, but nobody explained why the trace is a dead end, and I think for developers the “why” is the interesting part.

I wrote a shorter, finance-friendly version of this for controllers and CPAs on surehooferp.com. This one is the developer version: what the base app actually checks, why the check can never become false again, what Dimension Correction really does in the tables, and a small codeunit if you still want the reverse lookup for an audit.

All references below are from the BaseApp source (version 27).

The error, and the one line behind it

You try to delete a dimension value and you get:

This dimension value has been used in posted or budget entries and is included in a dimension set. You cannot delete it.

The text makes you think BC went through the ledgers and the budgets. It did not. Here is the OnDelete trigger of table 349 Dimension Value:

trigger OnDelete()
var
    DimValuePerAccount: Record "Dim. Value per Account";
begin
    CheckIfDimValueUsedFromOnDelete();

    DimValueComb.SetRange("Dimension 1 Code", "Dimension Code");
    DimValueComb.SetRange("Dimension 1 Value Code", Code);
    DimValueComb.DeleteAll(true);
    ...
    DefaultDim.SetRange("Dimension Code", "Dimension Code");
    DefaultDim.SetRange("Dimension Value Code", Code);
    DefaultDim.DeleteAll(true);
    ...
end;

And the check itself:

procedure CheckIfDimValueUsed() Result: Boolean
begin
    DimSetEntry.SetCurrentKey("Dimension Value ID");
    DimSetEntry.SetRange("Dimension Value ID", "Dimension Value ID");
    Result := not DimSetEntry.IsEmpty();
    OnAfterCheckIfDimValueUsed(Rec, DimSetEntry, Result);
end;

That’s it. One IsEmpty() on Dimension Set Entry (table 480), filtered on the Dimension Value ID. No G/L Entry, no budget, no documents.

Two things to notice:

  • The filter is on Dimension Value ID, not on the code. Dimension Value ID is an AutoIncrement integer on the Dimension Value table, and that is what the dimension set tree is built on.
  • Default Dimensions do not block the delete. The trigger deletes them for you (together with Dimension Value Combinations, Dim. Value per Account, Selected Dimensions and the IC mappings). I have seen more than one checklist telling people to clean Default Dimensions first “so the delete goes through”. It does not change anything for the check.

Why the check never becomes false again

To see why, we need to look at how a dimension set ID gets created.

Every time a user validates dimensions on anything (a sales line, a journal line, a budget entry, a posted entry), the code ends in DimensionManagement.GetDimensionSetID(TempDimSetEntry). That calls into Dimension Set Entry GetDimensionSetID, which walks table 481 Dimension Set Tree Node. The tree is keyed on "Parent Dimension Set ID", "Dimension Value ID". If the path for this combination does not exist yet, new tree nodes are inserted, a new set ID is assigned and the Dimension Set Entry rows are written. Right away, not at posting.

So a dimension set is a hash of a combination, not a pointer to a document. DEPARTMENT 20-200 alone is one set. DEPARTMENT 20-200 + PROJECT A is another set. A sales quote line that somebody coded and then deleted ten minutes later already created both rows in table 480.

Now the important bit: nothing in the base app ever deletes from Dimension Set Entry or Dimension Set Tree Node. I searched BaseApp for deletes on table 480. Everything that comes up is on a temporary record (TempDimSetEntry.DeleteAll() and friends), plus one obsolete method. No garbage collection, no “unused sets” cleanup job.

Put the two together:

  1. The delete check is “is this Dimension Value ID in table 480”.
  2. Table 480 only grows.

So once a value has been in any combination, the answer is true forever. That includes budget lines and unposted documents. G/L Budget Entry has field 480 Dimension Set ID like everybody else, and it calls GetDimensionSetID in its own code. Deleting the budget entries removes the budget, but not the set.

This is also why the “trace Dimension Set ID 3” idea goes nowhere. Even if you find every row in every table pointing at set 3 (and set 17, and set 2041, which also contain your value), and you somehow got rid of all of them, the set rows are still in 480 and the check still says no.

What about reversals, credit memos, undo?

Quickly, because these come up every time:

  • Reverse Transaction posts new G/L entries with the opposite sign. For an exact reversal that is the same combination, so the same set ID. You just added more references.
  • Corrective credit memos, Undo Shipment / Undo Receipt, negative adjustments all post new entries. None of them removes the old ones.

Business Central does not delete ledger entries. So none of these can help, and they add noise to a closed period that somebody will ask about at audit.

What Dimension Correction really changes

Dimension Correction (the feature on General Ledger Entries → Change Dimensions) is the closest thing we have to “rewrite history”, so it’s worth looking at what it actually does in the tables. The code lives in Finance\Dimension\Correction.

DimensionCorrectionMgt builds the new combination for each source set:

NewDimensionSetID := DimensionManagement.GetDimensionSetID(TempNewDimensionSetEntry);

So the target is a new set (or an existing one, if that combination already exists). Then DimCorrectionRun.UpdateGLEntry swaps the ID on the G/L Entry and re-derives the global dimension codes:

GLEntry."Dimension Set ID" := TempDimCorrectionSetBuffer."Target Set ID";
DimensionManagement.UpdateGlobalDimFromDimSetID(
    GLEntry."Dimension Set ID", GLEntry."Global Dimension 1 Code", GLEntry."Global Dimension 2 Code");

Things to note for developers:

  • Only G/L Entry is updated. Customer, vendor, item, value, FA, job and bank ledger entries keep their original Dimension Set ID. That is by design: the subledgers stay as posted, and the G/L shows the corrected analysis. Analysis views get updated too, if you choose that option.
  • The old set is not touched. The source and target IDs are kept in Dim Correction Set Buffer (fields 2 Dimension Set ID and 3 Target Set ID). That is how Undo works, and it is one more reason those 480 rows must stay.
  • Only the new value is checked for Blocked. In Dim Correction Change, validating New Value calls CheckDim and CheckDimValue on the new value. The value you are moving away from can already be blocked. So you can block first and correct afterwards, the order does not matter for the correction.
  • There is a separate Dimensions Blocked for Correction setup (table 2580). That one blocks a whole dimension code from being corrected, not values. Different thing, don’t confuse it with Blocked on the Dimension Value.

So Dimension Correction is a very good tool to fix G/L reporting after a bad dimension design. It will not make the old value deletable, because after the correction the old value is still in table 480, now in even more sets.

“Can’t I just subscribe to the event and skip the check?”

I knew somebody will ask 🙂

Yes, there is an IsHandled publisher:

[IntegrationEvent(false, false)]
local procedure OnBeforeCheckIfDimValueUsedFromOnDelete(DimensionValue: Record "Dimension Value"; xDimensionValue: Record "Dimension Value"; var IsHandled: Boolean)

Technically a subscriber can set IsHandled := true and the delete goes through. Please don’t. Here is what you leave behind:

  • Every Dimension Set Entry row with that Dimension Value ID still exists, but now points to nothing. The Dimension Value Name FlowField on 480 comes back blank for all posted history.
  • Global Dimension 1 Code / Global Dimension 2 Code (and the shortcut dimensions) are denormalized on the ledger entries, so they still carry the old code. Table relations on those fields now point to a value that doesn’t exist, and filters and lookups on them get strange.
  • If someone later re-creates the value with the same code, it gets a new Dimension Value ID (AutoIncrement), so the tree builds new set IDs. New postings with “the same” DEPARTMENT 20-200 won’t share sets with the old ones. Try explaining that in an Analysis by Dimensions view.

The base check is protecting the audit trail. It is a feature.

If you still want the reverse lookup

Sometimes you need the answer anyway. An auditor asks, a data migration needs it, or a client won’t accept “it is in there somewhere”. Analysis by Dimensions works off the G/L, so it will never show you a reference sitting in Value Entry or Job Ledger Entry.

What I need:

  • All set IDs that contain the value, from table 480, by Dimension Value ID (it has a key on it, so this part is fast)
  • Every table that has a Dimension Set ID field, without hardcoding a list (ISV tables included)
  • A count per table, without the whole thing blowing up on the first table I can’t read

Here is the codeunit:

codeunit 50100 "Dim. Value Usage"
{
    Access = Internal;

    procedure GetUsage(DimCode: Code[20]; DimValueCode: Code[20]): Text
    var
        DimensionValue: Record "Dimension Value";
        Field: Record Field;
        SetIDFilters: List of [Text];
        Result: TextBuilder;
        Hits: Integer;
    begin
        DimensionValue.Get(DimCode, DimValueCode);
        if not BuildSetIDFilters(DimensionValue."Dimension Value ID", SetIDFilters) then
            exit('The value is not in any dimension set. It can be deleted.');

        Field.SetRange(FieldName, 'Dimension Set ID');
        Field.SetRange(Type, Field.Type::Integer);
        Field.SetRange(Class, Field.Class::Normal);
        Field.SetFilter(ObsoleteState, '<>%1', Field.ObsoleteState::Removed);
        Field.SetFilter(TableNo, '<>%1&<>%2&<%3',
            Database::"Dimension Set Entry", Database::"Dimension Set Tree Node", 2000000000);
        if Field.FindSet() then
            repeat
                Hits := 0;
                if TryCountUsage(Field.TableNo, Field."No.", SetIDFilters, Hits) then
                    if Hits > 0 then
                        Result.AppendLine(StrSubstNo('%1 (%2): %3', Field.TableName, Field.TableNo, Hits));
            until Field.Next() = 0;

        if Result.Length() = 0 then
            exit('The value is in dimension sets, but no table with a Dimension Set ID uses them now. It still cannot be deleted.');
        exit(Result.ToText());
    end;

    local procedure BuildSetIDFilters(DimensionValueID: Integer; var SetIDFilters: List of [Text]): Boolean
    var
        DimensionSetEntry: Record "Dimension Set Entry";
        SetIDFilter: TextBuilder;
        MaxPerFilter: Integer;
        InFilter: Integer;
    begin
        MaxPerFilter := 200;
        DimensionSetEntry.SetCurrentKey("Dimension Value ID");
        DimensionSetEntry.SetRange("Dimension Value ID", DimensionValueID);
        DimensionSetEntry.SetLoadFields("Dimension Set ID");
        if not DimensionSetEntry.FindSet() then
            exit(false);

        repeat
            if InFilter > 0 then
                SetIDFilter.Append('|');
            SetIDFilter.Append(Format(DimensionSetEntry."Dimension Set ID", 0, 9));
            InFilter += 1;
            if InFilter = MaxPerFilter then begin
                SetIDFilters.Add(SetIDFilter.ToText());
                Clear(SetIDFilter);
                InFilter := 0;
            end;
        until DimensionSetEntry.Next() = 0;

        if InFilter > 0 then
            SetIDFilters.Add(SetIDFilter.ToText());
        exit(true);
    end;

    [TryFunction]
    local procedure TryCountUsage(TableNo: Integer; FieldNo: Integer; SetIDFilters: List of [Text]; var Hits: Integer)
    var
        RecRef: RecordRef;
        FldRef: FieldRef;
        SetIDFilter: Text;
    begin
        RecRef.Open(TableNo);
        if not RecRef.ReadPermission() then
            exit;
        FldRef := RecRef.Field(FieldNo);
        foreach SetIDFilter in SetIDFilters do begin
            FldRef.SetFilter(SetIDFilter);
            Hits += RecRef.Count();
        end;
        RecRef.Close();
    end;
}

A few notes on it, because each line is there for a reason:

The Field virtual table instead of looping AllObj. My first version iterated AllObjWithCaption, opened every table and looped through FieldIndex looking for the name. It works, but it opens a few thousand tables for nothing. Filtering Field on FieldName = 'Dimension Set ID' gives me exactly the tables I need. FieldName is the AL name, not the caption, so it does not depend on the user’s language. And it does not depend on the field number being 480 either. That’s the convention in the base app, but I don’t trust every ISV to follow it.

Tables 480 and 481 are excluded. Both have a field called Dimension Set ID, and both would obviously “match”.

Format(..., 0, 9). A plain Format() on an integer can put a thousands separator in, depending on the user’s region. A filter like 1,234|5,678 is not what you want. Format 9 is the XML format, no separators.

Chunks of 200 set IDs. On a company with years of history, a value can be in thousands of sets. One filter string with all of them gets long and SQL gets unhappy. So I split them and add up the counts.

[TryFunction] plus ReadPermission(). ReadPermission() covers the normal case cleanly. The TryFunction catches the rest: integration tables backed by Dataverse, tables the session can’t open, etc. Without it, the first bad table turns your diagnostic into an error dialog.

It is slow, and that’s expected. The lookup in 480 is on a key. The counts are not. In the base app only G/L Entry has a key on Dimension Set ID (Key13). On every other ledger, filtering on that field is a table scan. On a database with years of Value Entry this is minutes, not seconds. Run it on a sandbox copy, from a test page or with Message(DimValueUsage.GetUsage(...)). Don’t wire it to an action in production that somebody can click twice.

And please notice the last exit message. Even when no table uses the sets any more, the delete still fails. The codeunit answers “where is it used”, not “can I delete it”. The answer to that second question was in the first section of this post.

So what do we do with the value?

We block it. In order:

  1. Find open documents, journal lines and recurring journals that still carry the value, and re-code or finish them. Once the value is blocked, CheckDimValue will stop them at posting.
  2. Remove the Default Dimensions pointing at it, so new documents stop picking it up.
  3. Optional: run Dimension Correction to move G/L history to the new value. As we saw, the old value can already be blocked for this.
  4. Set Blocked on the Dimension Value.

Blocked stops new usage, keeps every historical report resolving the value, and keeps table 480 consistent. If the list looks cluttered, Indentation and Totaling on the Dimension Values page help with the presentation.

Before I go

This question is almost always a symptom of a dimension design that didn’t survive contact with the business. When you are on an implementation, push for the dimension conversation before go-live: how many dimensions, what granularity, which are mandatory. After go-live, all we have left is Blocked, Dimension Correction for the G/L, and a naming convention.

If you want to dig more, open DimensionValue.Table.al, DimensionSetEntry.Table.al and the Correction folder in BaseApp. It’s a nice small piece of the application to read on a Friday afternoon.

Thank you for reading!

Need an AL extension built, or one fixed that does not work right? I write AL for companies and for other partners. AL development →

AL Business Central Dimensions Dimension Set Entry Dimension Correction