As a result of some bad development you might find yourself in the situation to have G/L Entries with a Dimension Set ID that does not contain or contains incorrect Global Dimensions 1 and 2.
If you are new to Dimensions I recommend to first read my Dimensions Overview post.
I had this situation and besides fixing the old code that produced it, I created a repair tool (processing report) for the existing G/L Entries.
For example, on my test Chronus Database I have the 2 Global Dimensions set up like this:
And the following errors:
- G/L Entry 2678 contains wrong Department and Project dimensions and two other dimensions. In this case we need to change the dimension Set ID in order to contain the 2 Global Dimensions from the G/L Entry (Verkoop and Toyota) and the rest of dimensions remain as they are:
- G/L Entry 2679 contains the correct Department Code and two other dimensions but does not contain the Project. In this case we need to change the dimension Set Id so it will contain the existing entries plus the Project Code Toyota:
- G/L Entry 2683 has a dimension Set ID which does not contain Global Dimensions 1 and 2. In this case we should modify this dimension Set Id with one that will have the existing dimensions and the two global dimensions too:
- And finally, G/L Entry 2690 has a Dimension Set Id that contains 2 Global Dimensions, but the G/L Entry has no Global Dimensions. In this case we should “delete the 2 Global Dimensions” from the Dimension Set (use a Dimension Set Id only with the other dimensions):
The Repair Tool checks all G/L Entries (or if the user applied filters in the request page, only the filtered entries) and in case the Dimension Set Id is not in line with the Global Dimensions it calculates a correct dimension Set Id.
I added also the option “Preview Only”. If this boolean is checked, the report only shows the G/L Entries with problem in a layout, but does not modify the Dimension Set Id into the database. This way the user can first see what modifications that need to be done, check the new dimension Set Ids suggested by the Repair Tool and if he wants to modify the G/L Entries he can run the report again without the “Preview Only” check:
If I now run the report for all G/L Entries, I get the following results:
And if I run the report without the “Preview Only” check mark, I can see that the G/L Entries contain now a Dimension Set Id in line with the Global Dimensions 1 and 2. Also, the other existing dimensions are not affected:
- G/L Entry 2678:
- G/L Entry 2679:
- G/L Entry 2683:
- G/L Entry 2690:
The code I wrote for this report is the following (you can also download the sample report at the end of the post):
Documentation()
Report to fix the Dimension set IDs that are Not in line with Global Dim 1 and 2.
OnInitReport()
PreviewOnlyMode := TRUE;
OnPreReport()
TextList := TextList.List;
OnPostReport()
G/L Entry - OnPreDataItem()
GLSetup.GET;
Window.OPEN(ProcessingMsg);
NoOfRecords := COUNT;
G/L Entry - OnAfterGetRecord()
ModifiedRecord := FALSE;
GlobalDim1 := GetDimValueFromDimSetID(GLSetup."Global Dimension 1 Code","Dimension Set ID");
GlobalDim2 := GetDimValueFromDimSetID(GLSetup."Global Dimension 2 Code","Dimension Set ID");
OldDimSetID := "Dimension Set ID";
IF GlobalDim1 <> "G/L Entry"."Global Dimension 1 Code" THEN BEGIN
"Dimension Set ID" := ModifyDimSetID(GLSetup."Global Dimension 1 Code","Global Dimension 1 Code","Dimension Set ID");
IF NOT PreviewOnlyMode THEN
MODIFY;
ModifiedRecord := TRUE;
END;
IF GlobalDim2 <> "G/L Entry"."Global Dimension 2 Code" THEN BEGIN
"Dimension Set ID" := ModifyDimSetID(GLSetup."Global Dimension 2 Code","Global Dimension 2 Code","Dimension Set ID");
IF NOT PreviewOnlyMode THEN
MODIFY;
ModifiedRecord := TRUE;
END;
IF ModifiedRecord THEN
InsertElementsInLogList("Entry No.",OldDimSetID,"Dimension Set ID");
ProcessedRecords += 1;
Window.UPDATE(1,"Entry No.");
IF NoOfRecords > 0 THEN
Window.UPDATE(2, ROUND(ProcessedRecords / NoOfRecords * 10000,1));
G/L Entry - OnPostDataItem()
Integer - OnPreDataItem()
IF TextList.Count = 0 THEN
CurrReport.BREAK;
SETRANGE(Number,1,TextList.Count / 3);
Integer - OnAfterGetRecord()
IF Integer.Number = 1 THEN BEGIN
i := -1;
j := 0;
k := 1;
END ELSE BEGIN
i += 2;
j += 2;
k += 2;
END;
Integer - OnPostDataItem()
LOCAL GetDimValueFromDimSetID(DimCode : Code[20];DimSetID : Integer) : Code[20]
IF DimCode = '' THEN
EXIT;
DimMgt.GetDimensionSet(TempDimSetEntry, DimSetID);
TempDimSetEntry.RESET;
TempDimSetEntry.SETRANGE("Dimension Code", DimCode);
IF TempDimSetEntry.FINDFIRST THEN
EXIT(TempDimSetEntry."Dimension Value Code");
UpdateDimensionSetID(VAR TempDimSetEntry : TEMPORARY Record "Dimension Set Entry";DimSetID : Integer;DimCode : Code[20];DimValue : Code[20])
IF TempDimSetEntry.GET(TempDimSetEntry."Dimension Set ID", DimCode) THEN BEGIN
TempDimSetEntry."Dimension Value Code" := DimValue;
TempDimSetEntry.MODIFY(TRUE);
END ELSE BEGIN
TempDimSetEntry.INIT;
TempDimSetEntry."Dimension Set ID" := DimSetID;
TempDimSetEntry."Dimension Code" := DimCode;
TempDimSetEntry."Dimension Value Code" := DimValue;
TempDimSetEntry.INSERT(TRUE);
END;
LOCAL ModifyDimSetID(GlobalDimCode : Code[20];GlobalDimValue : Code[20];DimSetID : Integer) : Integer
DimMgt.GetDimensionSet(TempDimSetEntry, DimSetID);
UpdateDimensionSetID(TempDimSetEntry,DimSetID,GlobalDimCode,GlobalDimValue);
EXIT(DimMgt.GetDimensionSetID(TempDimSetEntry));
LOCAL InsertElementsInLogList(GLEntryNo : Integer;OldDimSetID : Integer;NewDimSetID : Integer)
TextList.Add(GLEntryNo);
TextList.Add(OldDimSetID);
TextList.Add(NewDimSetID);
For the report layout I used the “trick” with the DotNet list that I presented in this post.
The report only modifies the Dimension Set Id of G/L Entries. If needed, new functionality can be added for the user to modify the dimension Set Id from other ledgers too. We can add more data items in the report and boolean check marks in the request page for the user to select which ledgers he wants to “repair”. But in my case it was not needed, so I didn’t spend more time with this.
You can download the Repair Tool for G/L Entries with Dimension Set Id not in line with the Global Dimensions from THIS LINK if you want to play with it. (it was created in NAV 2016).
