Delete Dimension Value record

alex9

Let's say, you create a new record in Dimension Value table. Then you select this new dimension value somewhere in the application, let's say, in General Journal line. This is the first time you are using this dimension value, so new record in Dimension Set Entry is created. Next, you change your mind and decide not to use this dimension value. You delete this dimension vaue from journal line. Next, you want to delete this record from Dimension Value table. But you get an error "This Dimension Value has been used in posted or budget entries. You cannot delete it". ](*,)
Of course, it's never been used in any posted or budget entries... The function CheckIfDimValueUsed in table 349 (OnDelete trigger) simply checks if record with the same Dimension Value ID exists in Dimension Set Entry table.

CheckIfDimValueUsed() : Boolean
DimSetEntry.SETCURRENTKEY("Dimension Value ID");
DimSetEntry.SETRANGE("Dimension Value ID","Dimension Value ID");
EXIT(NOT DimSetEntry.ISEMPTY);

The problem is when you delete dim value from general journal line, the record in Dimension Set Entry table is not deleted. New dimension set ID is being assigned to the journal line, but old one is not deleted. It stuck forever, even if it is never in use again. So, you are not able to delete dimension value record even if it is not used at all.
Is it supposed to work like that, or I am missing something?

Marije_Brummel

It doesn't really hurt does it?

If it were used ever, it does not have to be created.

It's the design.

alex9

Well, it could generate redundant entries under certain circumstances. For example, in customization when dimension values are created automatically. And even in standard solution, it is very easy to get redundant entries by user's mistake. If user mistyped when entering dimension value, and noticed that only after dim value has been used - that's it, no way to fix it. I don't think such sort of user mistakes should be irreversible. It is not posting, it is just entering data...

Prevoius design of OnDelete trigger in table 349 was much more consistent. It checked supplemental dimension tables only, and nothing else. That makes much more sense. I understand, with current design which uses Dimension Set Entry table it should be different approach to check if dim value have been used, but technically it is not impossible. However, MS obviously chose the easiest way at this moment. Not saying that the error message does not reflect a reality now and leads to confusion.

miljance

We have the same situation with come of our customers migrating to 2013 R2. Some of the Dimensions are created automatically and eventual Dimension Value deletion should be possible. Dimension is always tightly bound to the Record itself and it is used only for this record but can eventually be droped with the Record itself. Dimension Value record should ideally be droped with the Record in order that Dimension Value list is not burdened. If someone has a proposal for a clean way to manage Dimension Set Entries and Dimension Set Tree Node during Dimension Value deletion please let us know. Does anyone know if there any consequences if Dimension Value is deleted without running OnDelete trigger?

Juha

You can change the test so it test if the dimension value is actually used and allow deletion if not.
To see if the dimension is used you just have to see if any of the dimension set ID's with the dimension value exist on any records.
If you can rely on the tablerelation the test loop isn't complicated, but can of course be slow. Make a filterstring containing all the dimension set IDs where the dimension value is used. loop all records in Field table with filter on RelationTableNo=Dimension Set Entry, for each Field record open a recordref and fieldref and filter with the dimension set ID filterstrin, test if a record exist.

I would also delete the dimension sets that includes the dimension value.

miljance

Thank you Juha for this tip, i might implement your method. At first moment i did check only specific tables which were involved in process.

After Reading a bit more about "Dimension Set Tree Node" I decided not to touch "Dimension Set Entry" and I have just deleted the Dimension Value. I figured that in worst case scenario, Flowfields in "Dimenstion Set Entry" will not be calculated and table will have more entries indeed but these are not accesible anyway. Theoretically, Dimension Value can be restored with proper Code. I have even tried to delete dimension Value but for already used "Dimension Set ID". Except for missing Name complete "Dimensions Set" was shown from Document. Posting wasn't not possible though. I didn't want to mess with the tree, because it looked like it can be a tricky thing.