web
You’re offline. This is a read only version of the page.
close
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 GP (Archived) / What Table(s) does the...
Microsoft Dynamics GP (Archived)

What Table(s) does the AR Historical Aged Trial Balance pull from?

(0) ShareShare
ReportReport
Posted on by David Cerny 300

My AR HATB is out of balance compared to any other AR source (Customer Maintenance, Smarlist, Receivables Summary, etc.).  I want to get it back in balance, but cannot determine which table(s) need to be adjusted.

Note: Checklinks has been run and the entire AR has been Reconciled a number of times, but report is still out of balance.

David

 

*This post is locked for comments

I have the same question (0)
  • Tim W Profile Picture
    Tim W 2,925 on at

    Essentially four tables are the transaction source for this report but they are read into temp tables and manipulated by a stored procedure that then feeds the report output.  I've had the exact situation you have in the past.  What I found was historical apply records that were not in balance with the historical document values in the companion table.  At the end of the day (a long day) I found a number of records in the RM History table that had a current trx amount when they should have been zero and also apply records with the incorrect amount.

    The HATB was building these incorrect records into the report when the other reports did not as it does the extra logic of matching apply records to amounts not just taking the current amount on a document as the amount. 

    • RM Open -- RM20101
    • RM Applied Open -- RM20201
    • RM History -- RM30101
    • RM Applied History -- RM30201

    Best

    Tim

     

     

  • David Cerny Profile Picture
    David Cerny 300 on at

    Tim,

    Thanks for the response.  I was able to find the problem transactions (using ExcelBuilder to extact the tables).  I ran a Checklinks on the RM tables, but the accounts are still out of balance.  How did you fix the problem?

    David

  • Tim W Profile Picture
    Tim W 2,925 on at

    One of the lacking features with check links is the ability to fix RM apply records.  Doesn't matter if they are in open RM20...  or history RM30.....  checklinks just does not fix these issues, I'm sure there is a TK somewhere from support though on how to fix this as it is not an un-common issue in my experience.  You just get used to fixing the apply amounts in the apply tables and current transaction amounts in the document tables, I've always done this through SQL though you could use Access I suppose.

    Off the top of my head you probably have issues where the current transaction amount is > zero (amount unapplied) on documents in the RM30101 table that should be set to zero (update RM30101 set CURTRXAM = 0 where dex_row_id = xxxxx) and/or issues with the apply table where the applied amounts are not correct on either the payment or the invoice record (update RM30201 set APPTOAMT = xxx, ORAPTOAM = xxx where dex_row_id = xxx).

    Might want to test this in test first but you're really only changing document relation values not posted values so you wont do to much damage regardless.

    Best of luck,

    Tim 

     

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

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics GP (Archived)

#1
Community Member Profile Picture

Community Member 2

#2
mtabor Profile Picture

mtabor 1

#2
Victoria Yudin Profile Picture

Victoria Yudin 1

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans