Help ! table 480 has been empty

grouic

Hello,

For an unknown reason, the table 480 has been emptied in the morning ! (it was correctly populated yesterday ev. regarding the backup).

Do you know a way, a script ? to re-fill the table ?

Thx for the help

Grouic

Luc_VanDyck

Restore your yesterdays backup into a new database, open that database with your NAV client, run table 480, copy all records and paste them in the table in you current live database.

grouic

Hello,

Thx for the fast answer. I was actually creating a XMLPort to export/import the data. I found once a script to rebuild the T481, I was wondering if a similar script existed for the T480.

I still can't understand how it is possible that table is empty ... nobody can't connect in sql an run a truncate table, and I can't imagine there is a Nav code that delete all the data ... mystery

TonyDuarte

I would say the same as Luc.

About the missing data you could try to check sql transaction logs and see if anything was done that could clean up the table.

Also try to figure out if something as executed during night that could cause it.

BardurKnudsen

Well; if you have an intact table 481, it should be quite simple to recreate table 480 from it.
Table 481 serves as an index for table 480, and each record is a node in an index tree. An existing Dimension Set is represented by a tree node where the field "In Use"=TRUE. To find the dimension values that are part of that set is basically just follow the nodes from the leaf up to the root.

The following is provided with no guarantee and only serves as inspiration, and you will NEVER run this on a production environment, and test it thoroughly at home.

So, if you create a batch report with one data item = table 481 and set DataItemTableView=WHERE(In Use=CONST(Yes)) then you have an iterator for all the dimension sets.
Then, for each of these, you just have to follow the nodes up to the root and create the corresponding table 480-records:

DimensionSetTreeNode := "Dimension Set Tree Node";
WHILE NOT Done DO BEGIN
DimensionValue.SETRANGE("Dimension Value ID",DimensionSetTreeNode."Dimension Value ID");
DimensionValue.FINDFIRST;
DimensionSetEntry.INIT;
DimensionSetEntry."Dimension Set ID" := "Dimension Set ID";
DimensionSetEntry."Dimension Code" := DimensionValue."Dimension Code";
DimensionSetEntry."Dimension Value Code" := DimensionValue.Code;
DimensionSetEntry."Dimension Value ID" := DimensionSetTreeNode."Dimension Value ID";
IF DimensionSetEntry.INSERT THEN;

DimensionSetTreeNode.SETRANGE("Dimension Set ID",DimensionSetTreeNode."Parent Dimension Set ID");
Done := (DimensionSetTreeNode."Parent Dimension Set ID" = 0) OR NOT DimensionSetTreeNode.FINDFIRST;
END;


So the entire object listing looks like this:
OBJECT Report 50000 Create table 480 from 481
{
OBJECT-PROPERTIES
{
Date=;
Time=;
Modified=Yes;
Version List=;
}
PROPERTIES
{
ProcessingOnly=Yes;
}
DATASET
{
{ 1 ; ;DataItem; ;
DataItemTable=Table481;
DataItemTableView=WHERE(In Use=CONST(Yes));
OnAfterGetRecord=VAR
DimensionSetEntry@1000 : Record 50480; <---NB! 50480 is a copy of 480 for testing!!!
DimensionSetTreeNode@1001 : Record 481;
DimensionValue@1002 : Record 349;
Done@1003 : Boolean;
BEGIN
DimensionSetTreeNode := "Dimension Set Tree Node";
WHILE NOT Done DO BEGIN
DimensionValue.SETRANGE("Dimension Value ID",DimensionSetTreeNode."Dimension Value ID");
DimensionValue.FINDFIRST;
DimensionSetEntry.INIT;
DimensionSetEntry."Dimension Set ID" := "Dimension Set ID";
DimensionSetEntry."Dimension Code" := DimensionValue."Dimension Code";
DimensionSetEntry."Dimension Value Code" := DimensionValue.Code;
DimensionSetEntry."Dimension Value ID" := DimensionSetTreeNode."Dimension Value ID";
IF DimensionSetEntry.INSERT THEN;

DimensionSetTreeNode.SETRANGE("Dimension Set ID",DimensionSetTreeNode."Parent Dimension Set ID");
Done := (DimensionSetTreeNode."Parent Dimension Set ID" = 0) OR NOT DimensionSetTreeNode.FINDFIRST;
END;
END;
}

}
REQUESTPAGE
{
PROPERTIES
{
}
CONTROLS
{
}
}
LABELS
{
}
CODE
{

BEGIN
END.
}
RDLDATA
{
}
}

Provided as is with no guarantee of functionality and only for inspiration.

Lgoo

It sounds like you have created a record variable of table 480 and forgett to change the property "temporary" to true. Check your last implemented changes to the applikation.

grouic

Hello,

Lgoo you're just right.

To determine which action was empty the table, I put a "raise error" in a ondelete trigger on the table (in sql server). Once a user called me to say "There is a strange message ..." I was able to debug the concerned program.

I called the standard function GetDimensionSet with a TempDimSetEntry variable that was not ... temporary (I forgot to put the option). Despite the local variable in GetDimensionSet is temporary, Navision worked directly on the table, and the first line is TempDimSetEntry.DELETEALL.

I accept my error, but I really think Microsoft should add an error message when an attempt to empty this important system table is done (error is human !).

I'm also wondering if the system should not raise an error during the compilation when a function waiting a temp variable is called with a non temp variable (?).

Thx all for your help.

BardurKnudsen

It could be good to see how the customer's license is set up regarding table 480. I really thought we had configured them to only have indirect permission to table 480 and 481, as well as the demo company's permissions also only have indirect permissions. If we have configured the customer license wrongly, then I would like us to correct that mistake.