Skip to main content

Notifications

Announcements

No record found.

Microsoft Dynamics NAV forum
Suggested answer

Tracking down accounts that have never been used. Chart of accounts cleanup

Posted on by 10

Help please.

I am trying to establish which accounts in our general ledger have never been used and can be deleted. I have created a jet report which has found a number of accounts that have never had a balance since day one, however, I can't establish if there have been transactions at any stage, ( ins and outs ). I don't have access to development tools. Is there a way other that possibly exporting hundreds and thousand of transactions and analysing.

Many thanks,  Ivor

  • Suggested answer
    YUN ZHU Profile Picture
    YUN ZHU 70,124 Super User 2024 Season 2 on at
    RE: Tracking down accounts that have never been used. Chart of accounts cleanup

    Hi, I tend to handle this outside the system, like using Excel.

    For example, export all General Ledger Entries first. (Using Configuration Package)

    pastedimage1645401561781v1.png

    Add a PivotTable for G/L Account No. (It's best to consider Bal. Account No. as well.)

    pastedimage1645401751224v2.png

    Then you will get the G/L account details that have been used.

    pastedimage1645401805267v3.png

    Hope this will help.

    Thanks.

    ZHU

  • Suggested answer
    JAngle Profile Picture
    JAngle 33,155 on at
    RE: Tracking down accounts that have never been used. Chart of accounts cleanup

    You can delete a GL if there are no postings in the current accounting period (income statement). Instead of a nl(“sum”) use a nl(“count”) with a date filter for the current accounting period with g/l entry table. I’d run that alongside a nl(“rows”) for the g/l account table. The nl(“count”) could be part of a if statement. If the count is above 1 then false and if below true. The true values are the delete contenders. Use cell b1 to add a keyword of hide+? and put another if statement in to assess the result of the count formula. If true display the word “show”, if false display “hide”. Your list of rows will only be the valid candidates. The count for balance sheet codes will have to be for a much bigger date range

  • Suggested answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Tracking down accounts that have never been used. Chart of accounts cleanup

    Ivor,

    That is a good one. What if you summarized all GL Account that had Transactions Posted. Then do a VLOOKUP to the GL Accounts listing and for those that have entries cross them out and the remainder are the ones no entries have posted.

    Just a thought.

    Hope it helps.

    Thanks,

    Steve

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

Anton Venter – Community Spotlight

Kudos to our October Community Star of the month!

Announcing Our 2024 Season 2 Super Users!

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

Dynamics 365 Community Newsletter - September 2024

Check out the latest community news

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 290,564 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 228,651 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans