Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / How to delete a main a...
Microsoft Dynamics AX (Archived)

How to delete a main account

(0) ShareShare
ReportReport
Posted on by Jørgen Dam Knudsen 340

Hi,

I've setup a chart of accounts and shared it bewtween 3 companies.(one of them a test company)
I've attached a "Test" account structure, and made a couple og postings in the test company.

Now it's not possible to delete any accounts in the chart of accounts, even accounts without postings.
An error occour: "A financial dimension value is based on the 116070 record and has been used on a  transaction. You cannot delete the 116070 record"

I've deleted all financial dimensions and all account structures, but still the same error.

Please help.

*This post is locked for comments

  • Community Member Profile Picture
    Community Member on at
    RE: How to delete a main account

    Hi,

    User created an vendor mistakenly in Production in D365 and now he is unable to delete it as it is throwing an error "A financial dimension value is based on the XXXXXX record and has been used on a transaction. You cannot delete the XXXXXX record".

    One-time.png

    Is there any way to delete it from front end. Please suggest.

    Thanks,

    Kiran

  • Suggested answer
    saransh321 Profile Picture
    saransh321 212 on at
    RE: AX 2012 How to delete a main account

    posting teh answer from dynamics community forum 

    first answer by 

    Brandon Wiese responded on 20 Sep 2013 6:32 PM

    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.

    second answer by

    André Arnaud de Calavon responded on 21 Sep 2013 3:27 PM

    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!

  • Community Member Profile Picture
    Community Member on at
    RE: AX 2012 How to delete a main account

    Hi All, You can use the job below to delete the old combinations related to ledger account

    static void clear_LedgerAttributeCombinations(Args _args)

    {

       MainAccount     mainAccount;

       DimensionAttributeValueCombination dimAttributeValueCombo;

       DimensionAttributeValueGroupCombination dimAttributeValueGroupCombo;

       DimensionAttributeLevelValue dimAttributeLevelValue;

       DimensionAttributeValue dimAttributeValue;

       ;

       ttsBegin;

       while select mainAccount

           where mainAccount.MainAccountId == "xxxxx"

       {

               while select forUpdate dimAttributeLevelValue

                   where dimAttributeLevelValue.DisplayValue == mainAccount.MainAccountId

               {

                   delete_from dimAttributeValue

                       where   dimAttributeValue.RecId == dimAttributeLevelValue.DimensionAttributeValue &&

                               dimAttributeValue.EntityInstance == mainAccount.RecId &&

                               dimAttributeValue.DimensionAttribute == DimensionAttribute::getMainAccountDimensionAttribute();

                   dimAttributeLevelValue.delete();

                   while select forUpdate dimAttributeValueGroupCombo

                       where dimAttributeValueGroupCombo.DimensionAttributeValueGroup == dimAttributeLevelValue.DimensionAttributeValueGroup

                   {        

                       delete_from dimAttributeValueCombo

                           where dimAttributeValueCombo.RecId == dimAttributeValueGroupCombo.DimensionAttributeValueCombination;

                       dimAttributeValueGroupCombo.delete();

                   }

               }        

           }

       ttsCommit;

    }

  • lally Profile Picture
    lally on at
    RE: AX 2012 How to delete a main account

    Hi All ,

    Seems to be MS forgot the some basic concepts .

    Just think in a End user point of view , how can user delete the main account / vendor account /Customer account with out going to back end table level as mentioned above ?

  • Community Member Profile Picture
    Community Member on at
    RE: AX 2012 How to delete a main account

    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'

  • Karen Hanley Profile Picture
    Karen Hanley 815 on at
    RE: AX 2012 How to delete a main account

    Thanks for the response, however the RECID in the DimensionAttributeLevelValue Table does not map to the RECID in the Main Account table.  So if I have account 1000 existing in 2 different LedgerChartofAccounts and I am trying to delete one of them, I dont know which record in the DimensionAttributeLevelValue Table  to delete.

  • Community Member Profile Picture
    Community Member on at
    RE: AX 2012 How to delete a main account

    The main account table (MainAccount) has a field named LedgerChartOfAccounts, which determines which COA the main account is attached to.

  • Karen Hanley Profile Picture
    Karen Hanley 815 on at
    RE: AX 2012 How to delete a main account

    Is there any harm in deleting all records from DimensionAttributeLevelValue  Table ?

    Scenario: We imported COA but then customer had so many changes we reimported a new COA instead of appending.  Now I need to delete the first COA but I cant b/c of this error.

          Is there any way in the AOT to determine which account goes to which COA.  For example, account 1000 exists in both COA.  For now, we are just deleting the lower RECID b/c our first COA import is the one we want to delete, but there has got to be a better way to figure this out.  

           Also, if we do delete the wrong one, does it matter?   I dont understand the purpose of this table, but if we deleted all records in the table would we run into issues?

    Thanks!   ( I hope MS builds this utility that you speak of. To have every partner create it seems silly)

  • Syed Shabab Mujtaba Profile Picture
    Syed Shabab Mujtaba 278 on at
    Re: AX 2012 How to delete a main account

    Hi,

    Iam going to prepare production environment, from test environment i have to delete all transactions and have to make Main Accounts values to zero. I am facing challenge making Main account values to zero, please guide me how to make Accounts value to zero for production. Even after deleting all the transactions and data still accounts are holding values.

    Please guide. Awaiting your reply. Thanks in advance

  • Verified answer
    Community Member Profile Picture
    Community Member on at
    Re: AX 2012 How to delete a main account

    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. Let me explain a hierarchy of these tables here: It start with LedgerDimension field on the table that has a relation to

    DimensionAttributeValueCombination -> DimensionAttributeValueGroupCombination -> DimensionAttributeLevelValue->DimensionAttributeVaue->DimensionAttribute tables. You can take a look at view "DimensionAttributeLevelValueAllView" for detail.

    Now the problem is, if there is any reference data in any of the table the record cannot be deleted. The problem with the approach of how LedgerDimension stores that data is that, the data in those tables will not be deleted (for reusability) even you delete the transaction or change the ledger dimension on the transaction so once you use the dimension it will always stay there in the tables.

    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. This solution will not harm any of the existing data and when user select the same combination of ledger dimension (that you have deleted) the framework will once again populate the data in these tables.  

    Thanks,

    Tabish

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Daivat Vartak – Community Spotlight

We are honored to recognize Daivat Vartak as our March 2025 Community…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Kudos to the February Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 292,516 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 231,432 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans