yesterday I came across the following issue and thought I’d share my findings and solution.
I’ve setup a chart of accounts and shared it between 3 companies.(one of them a test company).i have mapped one ledger account (Main Account) with bank accounts of all three companies.I have attached a “Test” account structure.and made a couple of postings in the test company.
when I want to modify a main account or delete a bank account that was not used (but defined with a main account).The message is the bank account cannot be deleted because the financial dimension related to the account has transactions.
A financial dimension value is based on the <dimension value> record and has been used on a transaction. You cannot delete the <dimension value> record.
We knew these dimension values weren’t used on any posted or un-posted transactions, so the expectation was that we could simply delete these without any issues. So you can imagine the surprise when we tried to delete the dimension values(Ledger,Bank,customer,vendor etc.)
The error means that there are some existing reference records.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 part of the dimension framework.
These tables are used in storing data for a Ledger dimension Dynamics AX 2012. There will be multiple entries in these tables for a single ledger dimension.AX provides no mechanism for deleting these records.The solution will be to make a utility that deletes all the record in those tables that don’t have any reference in any other tables.
To find out which DimensionAttributeLevelValue record corresponds to the main account, you need to use a few more tables.
LedgerChartOfAccountStructure –> Links COA to account structure
DimensionHierarchy –> AKA Account structure table. Also links to LedgerChartOfAccountsStructure, so you match the MainAccount by COA all the way to the account structure.
DimensionAttributeValueGroup –> Stores groups of values for the dimension set, which is linked to both DimensionAttributeLevelValue and DimensionHierarchy.
So you can start from DimensionAttributeLevelValue and drill back (or join) in this way…
DimensionAttributeLevelValue.DimensionAttributeValueGroup = DimensionAttributeValueGroup.Recid
DimensionAttributeValeuGroup.DimensionHierarchy = DimensionHierarchy.Recid
DimensionHierarchy.Recid = LedgerChartOfAccountsStructure.DimensionHierarchy
LedgerChartOfAccountsStructure.ChartOfAccounts = MainAccount.LedgerChartOfAccounts
MainAccount.MainAccountId = ‘Your account number’
1) Error occurring as there are record exist in the DimesionAttributeLevelValue table for the customer
2) Go to the DimensionAttributeLevelValue table –> filter by Bank,Vendor,Customer number in the DisplayValue field –> you can see the records exist in the table for the customer
3) Delete the record from the table
4) After this try deleting the Bank,Vendor,Customer.
(It will allow you to delete the Bank,Vendor,Customer record)\
Happy Daxing. 🙂