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) / Using SmartLists to Re...
Microsoft Dynamics GP (Archived)

Using SmartLists to Reconcile Open AR

(0) ShareShare
ReportReport
Posted on by Jim Lines 2

Hi,

I'm trying to reconcile my open AR as of 12/31/2012 and am having trouble selecting the correct columns.  I'm using Sales>>Receivables Transactions with the currency columns: Sales Amount, Original Trx Amount, and Current Trx Amount.  Here's my interpretation of those columns:

-Sales Amount:  Total of sales document

-Original Transaction Amount: Total Sales amount less any discounts, etc.

-Current Trx Amount: Amount remaining to be paid on that transaction (i.e. the difference between the total and what's been applied to the document.)

Is my understanding correct?  If yes, how come if the document is open and has no funds applied, it shows a zero amount in the Current Trx Amount column? I would hope to see a column the actual remaining amount open.

I'm trying to tie to the client's QuickBooks 2012 Open AR and it's becoming a nightmare...  Thanks in advance!!!

Jim

*This post is locked for comments

  • Jim Lines Profile Picture
    Jim Lines 2 on at
    RE: Using SmartLists to Reconcile Open AR

    OK... getting closer.  Is there a column that can be added to the report to show by document #, the actual remaining amount?

    Jim

  • Jim Lines Profile Picture
    Jim Lines 2 on at
    RE: Using SmartLists to Reconcile Open AR

    Victoria,

    You'll see in my response to Ian that the GL is all reconciled.  I'm just trying to tie open AR.  I also think I found what could be preventing me from getting my correct open amounts.  I'm going to do some investigating on my HATB report and I'll update the case.  

    Again, thanks so much!

    Jim

  • Jim Lines Profile Picture
    Jim Lines 2 on at
    RE: Using SmartLists to Reconcile Open AR

    Hi Ian,

    Thanks for responding.  The GL is correct, so I am just reconciling and adjusting the Open AR for this one customer and the GL Posting is turned off.  Any correcting adjustments I am making are not posting to the GL (I'm deleting the GL batches).  

    There are no currencyrate issues and since I'm only working with a single customer record(s), so I am good there.

    I think a major part of my reconciliation (confustion) is that when I look at the HATB as of 12/31/12, I am not seeing accurate remaining balances.  For example, I have an invoice in the amount of $12,800 with a remaining balance of ~$1,600.  But there is no column that shows the net amount.

    ** I may have just stumbled upon something as I type... I just looked at their aging buckets and see that the "Over 90 Days" column is not included on the HATB... (obviously I should have noticed this earlier).  

    Let me figure out what they did to the report, fix it and then get back to you and Victoria.

    Thanks to both of you!

    Jim

  • Victoria Yudin Profile Picture
    Victoria Yudin 22,766 on at
    RE: Using SmartLists to Reconcile Open AR

    Jim,

    Reading Ian's response made me think of something that's not quite clear. Are you reconciling from the AR subledger to the General Ledger, all in GP? Or from the AR subledger in GP to the customer balance in Quickbooks? I was answering the latter. If it's the former, then how will you reconcile one customer's balance to the GL, which should be a sum of all customers?

  • Ian Richardson Profile Picture
    Ian Richardson 4,150 on at
    RE: Using SmartLists to Reconcile Open AR

    hi Jim

    one of the GP basics - was posting to the gl was turned off before the opening balances were posted so that they would not be adding to the opening trial balance?

    Are there any currency issues with the trial balance?  you talk about current tx amount rather than originating tx amount implying that there are not, but it is a consideration.  If there are any, was the correct exchange rate used for the transactions?

    Are there customer classes that you can use to balance subsets of the data?  Narrowing it down to a particular customer(s) may help find the issue.

    I'll echo Victoria, use the historical aged trial balance rather than smart list.

    Ian

  • Victoria Yudin Profile Picture
    Victoria Yudin 22,766 on at
    RE: Using SmartLists to Reconcile Open AR

    Hi Jim,

    If the total of the invoices was correct and the total of the payments was correct, at the end of the day, the total due should be correct. If it is not, then I would suspect something other than how the payments were applied. If the total does match, but the individual invoices do not, then you can unapply and re-apply them in GP. Or, since you're doing this test 5 months after the fact anyway, you might just say that the ending balances matching is enough. :-)

  • Jim Lines Profile Picture
    Jim Lines 2 on at
    RE: Using SmartLists to Reconcile Open AR

    Victoria,

    I'm hoping you may be able to point me in the right direction.  The situation is that I am trying to help a client reconcile the Open A/R in GP to the audited final Open A/R from their QuickBooks.  They've been entering invoices and payments ts in GP in parallel with QB since Aug 2012.  

    I believe part of the problem why I can't get this to tie is that they had some temps entering data and they were applying payments to invoices as they saw fit instead of applying them payment = invoice.  Fortunately I am only reconciling one customer.  Would you agree that the way they applied this payments will affect the ending Open A/R balance in GP?

    No payments have been applied in 2013 since they are not yet live on GP.  Do you have any advice?

    Thanks again.

    Best regards,

    Jim

  • Jim Lines Profile Picture
    Jim Lines 2 on at
    RE: Using SmartLists to Reconcile Open AR

    Victoria,

    Thanks for the response!  I really appreciate you detailing the data fields.  I'll take all of this into consideration in my reconcile. I'll follow up with a post if I run into any other issues!

    Jim

  • Verified answer
    Victoria Yudin Profile Picture
    Victoria Yudin 22,766 on at
    RE: Using SmartLists to Reconcile Open AR

    Jim,

    First to go over some definitions:

    - Sales Amount is the subtotal, not including any freight, tax, misc, discount, etc. In many cases it will be the same as the total of the document, but not always.

    - Original Trx Amount is the total of the transaction, including everything.

    - Current Trx Amount is what is due/open, considering everything that has been applied to this transaction, whether posted or not. Current Trx Amount for Receivables transactions will also be 0 for any voided transactions (column for this is Void Status).

    All that said, SmartList is not date sensitive (in other words, you cannot use SmartList to get results as of a particular date). So unless absolutely nothing has been entered into the Receivables module since 12/31/2012, you cannot use SmartList to reconcile as of 12/31/2012.

    To get a historical trial balance, you can go to Reports | Sales | Trial Balance and choose Historical Aged Trial Balance.

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

Announcing Our 2025 Season 1 Super Users!

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

Vahid Ghafarpour – Community Spotlight

We are excited to recognize Vahid Ghafarpour as our February 2025 Community…

Tip: Become a User Group leader!

Join the ranks of valued community UG leaders

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,399 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans