Rebuilding "Dimension Set Tree Node" table

paurolapaurola Posts: 43Member
As I have read from other topics, it is possible to build the "Dimension Set Entry" table by looping through "Dimension Set Tree Node" table. But what if the "Dimension Set Tree Node" gets corrupted or messed up by somebody deleting records from it...

This is what happened and after spending quit a lot of time with it, I found out that you can delete all records from the "Dimension Set Tree Node" and NAV will build it up again in the long run - when a dimension set is touched (ie. opening the dimensions of a Gen. Journal Line) a new dimension set entries will be created in both "Dimension Set Entry" and "Dimension Set Tree Node" tables. Old Dimension Set Entry records (duplicates) will remain in the "Dimension Set Entry" table - this is not a good thing, even though I do not know whether this causes any harm.

It would be nice to be able to rebuild the "Dimension Set Tree Node" table based on the "Dimension Set Entry" records but it looks to be a hairy thing to do... comments, anyone?

Comments

  • dayanmpereradayanmperera Posts: 3Member
    Why anyone doesn't make any sound. I also have that problem.
  • lubostlubost Posts: 486Member
    I think you have to rebuild tree only if "someone" changes records in tree manually. Document dimension is defined by Dimension Set ID.
  • JMathisJMathis Posts: 1Member
    It's an old post, but recently I had to figure this out and did not run across many hints or clues.

    We wanted to populate the Dimension Set Tree Node after migrating data from Quickbooks and creating sets manually.

    The trick for me was resetting the auto increment on the Dimension Set Tree Node table.
    It requires a little SQL:
    DBCC CHECKIDENT ([CRONUS USA, Inc_$Dimension Set Tree Node], RESEED,0);

    Then it was a matter of
    • filling a new table with the Dimension Set Entry table
    • clearing the Dimension Set Entry table
    • looping through the new table populating a temp Dimension Set Entry table
    • loop through the temp Dimension Set Entry table calling GetDimensionSetID on each.

    Worked well in Cronus and re-populated the Dimension Set Tree Node table exactly as it was prior to clearing it.
Sign In or Register to comment.