Cannot delete an unused main account in Dynamics AX 2012 R2

(0) Share

Report

Posted on by Community Member

I am not able to delete an unused main account. I get the following error message:

"A financial dimension value is based on the 9411 record and has been used on a transaction. You cannot delete the 9411 record"

I know that many others have been having this problem but I have not been able to find any solution to this. Can anybody help?

*This post is locked for comments

I have the same question (0)

All responses (5)

Answers (0)

Suggested answer

Brandon Wiese 17,788 on at

Like (0)

Report

I can explain the reason for the message and why you are not able to delete the main account, but unfortunately cannot give you a good solution for how to fix it.

When you add new records to financial dimension entities (Main account, Department, Cost center, etc.), they are merely available for use and can be immediately deleted. However, as soon as they are actually used, additional records are created in tables such as DimensionAttributeValue, DimensionAttributeValueCombination, DimensionAttributeLabelValue, and many others. Even if you then delete the journal line (presumably) that referenced the new Main account, the fact that you have ever used it means that these records will exist forever. AX provides no mechanism for deleting these records. The only method would be with direct SQL, and the schema behind those tables is rather complicated.

I just yesterday had to solve this exact problem, where someone added and subsequently used and immediately deleted a whole bunch of Departments that should have been Cost centers. I was able to delete all of the supporting records, verify that no damage was done to any other references across all AX tables, and then delete the Departments from the UI. It is not a task to be taken lightly, and I would never post that SQL for use by someone who didn't understand the risks deeply.

Was this reply helpful? Yes No
Suggested answer

André Arnaud de Cal... 301,156 Super User 2025 Season 2 on at

Like (0)

Report

Hi Katrin,

Read the answer of Brandon very carefully. One of the checks for a main account being used is done on the table "DimensionValueAttributeCombination". You can search for the main account in this table and delete the records. Then also the main account itself can be deleted.

You have to be very sure it is not used elsewhere. Note that within Brandons answer it is stated that there are more tables part of the dimension framework. When you delete records in this table, other tables might have records that should be removed as well. I have not noticed problems if only the appropriate records in the mentioned table are deleted.

Another note: It is possible if you have multiple charts of accounts or e.g. fixed assets or dimension values with the same value as your main account, you can delete wrong records!

Was this reply helpful? Yes No
Suggested answer

Nicholas Ethier 5 on at

Like (0)

Report

Hi Andre

I believe the actual table name is not DimensionValueAttributeCombination but it's named dbo.DIMENSIONATTRIBUTEVALUECOMBINATION

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report

this message suggest that you have some records which are related to the record which you are trying to delete.

the existing records which are causing issue is explained by Brandon Wiese

This issue can be recovered only when you consider deleting all types of transaction data using following class

SysDatabaseTransDelete

Please verify and update us with your findings

Was this reply helpful? Yes No
André Arnaud de Cal... 301,156 Super User 2025 Season 2 on at

Like (0)

Report

Hi Nicholas,

You are right. It was mistyped; I shuffeled some parts. The dbo. Prefix is when you access the database directly from the SQL server.

For all other people:

The question is over one year old. So please don't add more (suggested) answers as the original poster never had replied or marked an answer. I assume she already solved her problem.

Was this reply helpful? Yes No