Business Central and Related Dimension Values

I had recently a request from a customer that they want to relate dimensions in Business Central. I.e you have a Sales Person dimension and you want to assign these dimensions values to a Sales Department dimensions.

Thats not possible to do out of the box in Business Central and needs some customization. In standard Business Central you need to add all the dimensions one by one and that is very time consuming and error prone.

Everybody that has worked with dimensions via AL code know that they are quite complicated and easy to mess up so I wanted to share some tips of how to make this related dimension work.

First step is I created a Related Dimensions table in Business Central:

Here on this page I can say which dimension and dimension value is related to a dimension value.

In my case JO has related dimensions:

• BUSINESSGROUP / HOME
• CUSTOMERGROUP / SMALL
• DEPARTMENT / ADM

Now the code part. Since the dimensions can be added via UI, via API calls, from default dimensions, from shortcut dimensions etc and each table record in Business Central has “Dimension Set Entry” id we need to go lowest place possible in code to make this solution universal.

I used event OnBeforeGetDimensionSetID from "Dimension Set Entry" table. That event sends a temp record of “Dimension Set Entry” and we can add to it.

This is the full code to add all the related dimensions:

    procedure CheckAndAddRelatedDimensions(var DimensionSetEntry: Record "Dimension Set Entry")
    var
        RelatedDimension: Record "RLD Related Dimension";
        TempDimSetEntry: Record "Dimension Set Entry" temporary;
        ExistingDimensions: List of [Code[20]];
    begin
        if DimensionSetEntry.FindSet(false) then
            repeat
                TempDimSetEntry.Copy(DimensionSetEntry);
                TempDimSetEntry.Insert();
                DimensionSetEntry.Delete();
            until DimensionSetEntry.Next() = 0;

        Clear(DimensionSetEntry);
        DimensionSetEntry.Reset();

        if TempDimSetEntry.FindSet(false) then
            repeat

                DimensionSetEntry.Init();
                DimensionSetEntry.Validate("Dimension Set ID", TempDimSetEntry."Dimension Set ID");
                DimensionSetEntry.Validate("Dimension Code", TempDimSetEntry."Dimension Code");
                DimensionSetEntry.Validate("Dimension Value Code", TempDimSetEntry."Dimension Value Code");
                if DimensionSetEntry.Insert(true) then
                    ExistingDimensions.Add(TempDimSetEntry."Dimension Code");

                RelatedDimension.SetRange("Dimension Code", TempDimSetEntry."Dimension Code");
                RelatedDimension.SetRange("Dimension Value Code", TempDimSetEntry."Dimension Value Code");
                if RelatedDimension.FindSet() then
                    repeat
                        if not ExistingDimensions.Contains(RelatedDimension."Related Dimension Code") then begin
                            DimensionSetEntry.Init();
                            DimensionSetEntry.Validate("Dimension Set ID", TempDimSetEntry."Dimension Set ID");
                            DimensionSetEntry.Validate("Dimension Code", RelatedDimension."Related Dimension Code");
                            DimensionSetEntry.Validate("Dimension Value Code", RelatedDimension."Related Dimension Value Code");
                            DimensionSetEntry.Insert(true);
                        end;
                    until RelatedDimension.Next() = 0;
            until TempDimSetEntry.Next() = 0;
    end;

For the UX part I added this Page Extension for the Edit Dimension Set Entries page:

pageextension 50101 "RLD Edit Dimension Set Entries" extends "Edit Dimension Set Entries"
{
    layout
    {
        modify(DimensionValueCode)
        {
            trigger OnAfterValidate()
            var
                RelatedDimMgmt: Codeunit "RLD Related Dimensions Mgmt.";
            begin
                CurrPage.Update(true);
                RelatedDimMgmt.CheckAndAddRelatedDimensions(Rec);
                CurrPage.Update(false);
            end;
        }
    }
}

And this is how it looks like when finished:

If you also need this solution then please e-mail me villem@integrated.ee