Dimensions not inserted correctly NAV 2013

zeon · 2014-04-23

Hi,

We have a problem at a customer as no dimension set is inserted when create a Gen. Journal Line. The Gen. Jnl Line with account type vendor.

Upon validating the Vendor No. on the Gen. Jnl Line a Dimension Set Id=7 is inserted on the Gen. Jnl Line. When opening the dimensions Ctrl+Shft+d no dimension values exist.

We have tried debugging and here I found that in T480 Dimension Set Entry the first rec has dimension set id=8. When debugging I can see that NAV uses T481 Dimension Set Tree Node to calculate/find the Dimension Set Id. In this case it finds an entry with dimension set id = 7 although this set id does not exist in T480 [-( Further it is marked "In Use" in T481.

I cannot grasp why we can have entries in Dimension Set Tree Node (T481) with set id's that relate to set id's that do not exist in Dimension Set Entry (T480) AND is marked "in use"?
So, in this case it return set id = 7 (which does not exist) meaning that when the the customer opens the dimensions page no values are shown.

geordie · 2014-04-23

Just wondering...do you have some deleted transaction/data loaded with RapidStart (validating the Dimension Set ID) but not posted, which triggered the creation of Dimension set id = 7?

zeon · 2014-04-23

No, that is not the case...

geordie · 2014-04-24

If it's a doable solution, you can give a try manually inserting a record in Dimension Set Entry table with Set Id=7 to fill the gap I assume there is at the moment, and see if validating the Vendor No. dimensions are correctly shown.

zeon · 2014-04-24

Thanks for the input guys, but I found another solution to solve the problem.

Looking into the records in both T480 "Dimension Set Entry" and T481 "Dimension Set Tree Node" in the Cronus company it seems like the following relation exists.

If a rec with e.g Dimension Set Id = 7 does not exist in T480, but it exists in T481, then the field "In Use" in T481 is FALSE. And vice verse if an entry with Dimension Set Id = 6 exists in both T480 and T481, then "In Use" is TRUE.

I'm not 100% sure this is always the case, but in this particular case I marked all entries in T481 with "In Use"=FALSE if the Dimension Set Id did not exist in T480. If "In Use" is FALSE the code will insert a new entry in T480 with the same Dimension Set Id.

jrenz · 2014-04-24

Hi,
just one question: have you imported dimension values (table 349) and/or have you manually filled field 12 "Dimension Value ID"?

zeon · 2014-04-24

I'm not sure, but I don't think the customer have done that! I have just been asked to help with this specific problem - I do not work on that project normally.

awarn · 2014-10-08

I,

I'm having a similar issue and I think it IS due to data being imported via rapidstart and the dimension ID not being set properly.

I think what needs to be done is to somehow rebuild the dimension set and dimension tree table. I suspect the way to do this would be something like:

-Delete all records in the dimension set entry / dimension tree tables
-Scan all master files (they have 12,000 customers which was the source of this issue), and hit through code the validation of both global dimensions.
-The above action *should* rebuild the dimension set entry and dimension tree tables.

The above doesn't sound hard - except I'd have to make sure that the 'scan master files' step also scanned all open documents, all posted records - ugg that doesn't sound fun!

Has anyone seen this before?

BardurKnudsen · 2014-10-09

I think it sounds a bit drastic to delete tables 480 and 481, especially because you thereby loose all information about posted data, except for the two global dimensions.
First step is to inspect the two tables to establish which one is wrong. They have apparently gone out of sync, most likely due to manual update of table 480.

Assuming one of the tables is correct (which most likely will be 480), you can create a codeunit to rebuild table 481 from table 480. Remember that table 481 is just an index for table 480.

For inspiration, I have added the printout with two codeunits; 50000 and 50001, and a query; 50000 that is used by the codeunits.
Codeunit 50000 “Recreate Dim Set Entry Nodes” will delete all entries in table 481 and recreate a tree. The tree will not be exactly equal to the old, as some tree nodes are not ‘in use’ and hence there is no corresponding dimension set entry set – and hence it doesn’t matter.
Codeunit 50001 “Verify Dim Set Entries” will just check the consistence of the dimension entries.

IF you decide to try these out on the customer database, PLEASE do take a backup first!!! and PLEASE do test/check the results and see if works as expected!

Unfortunately it is not possible to upload files to this forum, so here's the code in clear text:

OBJECT Codeunit 50000 Recreate Dim Set Entry Nodes
{
OBJECT-PROPERTIES
{
Date=;
Time=;
Modified=Yes;
Version List=;
}
PROPERTIES
{
OnRun=VAR
DimensionSetCount@1007 : Query 50000;
DimTreeNode@1000 : Record 481;
BEGIN
IF NOT CONFIRM('Are you sure you want to delete and recreate %1?',FALSE,DimTreeNode.TABLECAPTION) THEN
EXIT;
IF NOT CONFIRM('Really?') THEN
EXIT;
DimTreeNode.DELETEALL;
DimensionSetCount.OPEN;
WHILE DimensionSetCount.READ DO
RecreateDimNode(DimensionSetCount.DimensionSetID,DimensionSetCount.DimCount);
END;

}
CODE
{

LOCAL PROCEDURE RecreateDimNode@3(DimSetID@1000 : Integer;DimSetCount@1007 : Integer);
VAR
DimSetEntry@1005 : Record 480;
DimTreeNode@1004 : Record 481;
ParentID@1006 : Integer;
i@1008 : Integer;
NodeExists@1009 : Boolean;
BEGIN
DimSetEntry.SETRANGE("Dimension Set ID",DimSetID);
DimSetEntry.SETCURRENTKEY("Dimension Value ID");
IF DimSetEntry.FINDSET THEN
REPEAT
i += 1;
NodeExists := DimTreeNode.GET(ParentID,DimSetEntry."Dimension Value ID");
IF NodeExists THEN BEGIN
IF NOT DimTreeNode."In Use" AND (i = DimSetCount) THEN BEGIN
DimTreeNode."In Use" := TRUE;
DimTreeNode.MODIFY;
END;
END ELSE BEGIN
DimTreeNode.INIT;
DimTreeNode."Parent Dimension Set ID" := ParentID;
DimTreeNode."Dimension Value ID" := DimSetEntry."Dimension Value ID";
IF i = DimSetCount THEN BEGIN
DimTreeNode."In Use" := TRUE;
DimTreeNode."Dimension Set ID" := DimSetEntry."Dimension Set ID";
END;
DimTreeNode.INSERT;
END;
ParentID := DimTreeNode."Dimension Set ID";
UNTIL DimSetEntry.NEXT = 0;
END;

BEGIN
END.
}
}

OBJECT Codeunit 50001 Verify Dim Set Entries
{
OBJECT-PROPERTIES
{
Date=;
Time=;
Modified=Yes;
Version List=;
}
PROPERTIES
{
OnRun=VAR
TempDimSetEntry@1003 : TEMPORARY Record 480;
DimSetEntry@1001 : Record 480;
DimMgt@1000 : Codeunit 408;
i@1002 : Integer;
PrevDimSetID@1004 : Integer;
OldCount@1005 : Integer;
BEGIN
IF DimSetEntry.FINDSET THEN
REPEAT
IF (PrevDimSetID <> 0) AND (DimSetEntry."Dimension Set ID" <> PrevDimSetID) THEN BEGIN
OldCount := DimSetEntry.COUNT;
i := DimMgt.GetDimensionSetID(TempDimSetEntry);
IF OldCount <> DimSetEntry.COUNT THEN
ERROR('Dimension set %1 was added.',i);
CompareDimSets(i,TempDimSetEntry);
TempDimSetEntry.DELETEALL;
END;
PrevDimSetID := DimSetEntry."Dimension Set ID";
TempDimSetEntry.INIT;
TempDimSetEntry.TRANSFERFIELDS(DimSetEntry);
TempDimSetEntry.INSERT;
UNTIL DimSetEntry.NEXT = 0;
i := DimMgt.GetDimensionSetID(TempDimSetEntry);
CompareDimSets(i,TempDimSetEntry);
END;

}
CODE
{

LOCAL PROCEDURE CompareDimSets@2(DimSetID@1000 : Integer;VAR TempDimSetEntry@1001 : Record 480);
VAR
DimSetEntry@1002 : Record 480;
BEGIN
DimSetEntry.SETRANGE("Dimension Set ID",DimSetID);
IF DimSetEntry.COUNT <> TempDimSetEntry.COUNT THEN
ERROR('different counts in sets %1 and %2.',DimSetID,TempDimSetEntry."Dimension Set ID");

DimSetEntry.FINDSET;
TempDimSetEntry.FINDSET;
REPEAT
IF DimSetEntry."Dimension Code" <> TempDimSetEntry."Dimension Code" THEN
ERROR('different dim code in sets %1 and %2.',DimSetID,TempDimSetEntry."Dimension Set ID");
IF DimSetEntry."Dimension Value Code" <> TempDimSetEntry."Dimension Value Code" THEN
ERROR('different dim value code in sets %1 and %2.',DimSetID,TempDimSetEntry."Dimension Set ID");
IF DimSetEntry."Dimension Value ID" <> TempDimSetEntry."Dimension Value ID" THEN
ERROR('different dim value id in sets %1 and %2.',DimSetID,TempDimSetEntry."Dimension Set ID");
UNTIL (DimSetEntry.NEXT = 0) AND (TempDimSetEntry.NEXT = 0)
END;

BEGIN
END.
}
}

OBJECT Query 50000 Dimension Set Count
{
OBJECT-PROPERTIES
{
Date=;
Time=;
Modified=Yes;
Version List=;
}
PROPERTIES
{
OrderBy=DimCount=Ascending,
DimensionSetID=Ascending;
}
ELEMENTS
{
{ 1 ; ;DataItem; ;
DataItemTable=Table480 }

{ 2 ;1 ;Column ;DimensionSetID ;
DataSource=Dimension Set ID }

{ 3 ;1 ;Column ;DimCount ;
MethodType=Totals;
Method=Count }

}
CODE
{

BEGIN
END.
}
}

“Provided as is and Microsoft does not guarantee that it will fix the customer’s problem and is not liable to any damage arising from using this tool.”

awarn · 2014-10-09

Hi,

I found out what happened - and then how we decided to fix it.

We have tools in the database to copy tables from one company to another, used during the go live process. It turns out that the dimension set tables (480 / 481) were not copied, but the some tables were (open orders being the main culprit, along with ship-to addresses to which we added dimensions), so dimension set ID's that did not exist still existed on some documents.

Then, as users opened the Dimension screen and closed it, new ID's were created, thus corrupting the data.

What we decided to do is to run a routine that would revalidate the dimensions, thus recreating the dimension set ids.

Basically we scanned the tables that we though had bad data, cleared the dimension set ID field, deleted the corresponding Dimension Set record, along with marking the tree node record as not in use. Then we revalidated the dimension, whose regular dimension code re-enabled the tree node record and recreated the dimension set record correctly.

Howdy, Stranger!

Quick Links

Categories

Dimensions not inserted correctly NAV 2013

Answers