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) / Adding field to show u...
Microsoft Dynamics GP (Archived)

Adding field to show unapplied amount in RM Statements report

(0) ShareShare
ReportReport
Posted on by Renee Leung 6,775

Is there a calculated field or table/globals field that shows a customer's unpaid invoice amount? i.e. $1000 invoice and a payment of $200 is applied, so the remaining balance due is $800. I'd like to modify this in the RM Statements report, which currently displays a running total of the balance due, such that if you have a balance due of 2 invoices, each $100, the report would display as follows:  

Line 1 $100 due

Line 2 $200 due

Total: $200 due

Instead, I would like it to say:

Line 1 $100 due

Line 2 $100 due

Total $200 due

I've tried Current Trx Amount, but the results are not correct.

*This post is locked for comments

  • Renee Leung Profile Picture
    Renee Leung 6,775 on at
    RE: Adding field to show unapplied amount in RM Statements report

    Answer: support.microsoft.com/.../912126

  • Renee Leung Profile Picture
    Renee Leung 6,775 on at
    RE: Adding field to show unapplied amount in RM Statements report

    *subtotal of $250, not $350. Sorry.

  • Renee Leung Profile Picture
    Renee Leung 6,775 on at
    RE: Adding field to show unapplied amount in RM Statements report

    Hmm. Given that, is there no way for me to create some sort of calculated field with what's available in Report Writer? Currently, the out-of-the-box RM Statements report shows a column of Balance Due. The only idiosyncrasy with this column is that it shows you the accumulating balance with each additional invoice.  So if I had Invoices 1,2,  3 each for $100 dollars each where invoice #3 had a remaining unpaid balance of $50, this Balance Due column would show $100, $200 and then $250 with a subtotal of $350. In other words, it shows you the accumulating balance with each transaction, instead of the open amount per trx, which I would find confusing as a customer!! Is there some subtraction equation I could use so that the amounts displayed in the Balance Due column would instead be: $100, $100, $50. (And then you'd have the subtotal of $250 at the bottom).

  • L Vail Profile Picture
    L Vail 65,271 on at
    RE: Adding field to show unapplied amount in RM Statements report

    Hi,

    When you say 'synced', do you mean would it print your Crystal Report instead of the GP Statement? Out of the box the answer is no.

    If you mean would it 'age' appropriately, then that depends on  how you write your Crystal Report.  The GP aging merely changes the 'bucket' number that the transaction lives in. You have seven available buckets according to how you set it up in RM Setup. The GP statement does not depend on the dates, it depends on the buckets. Also, the statement pulls data from the RM Open table (rather than history). You need to be sure you move transactions to history if you do not want them to appear on the GP statement.

    Kind regards,

    Leslie

  • Renee Leung Profile Picture
    Renee Leung 6,775 on at
    RE: Adding field to show unapplied amount in RM Statements report

    Thanks, but if I were to use a Crystal Report, would it be synced with the "Statements" routine in Dynamics GP? This is located under Sales . Routines . Statements.

  • Suggested answer
    Mahmoud Saadi Profile Picture
    Mahmoud Saadi 32,738 on at
    RE: Adding field to show unapplied amount in RM Statements report

    I definitely agree with what you say and the urgent need to include unapplied amount in RM Statement. I would recommend creating a customized report for that purpose.

    I would like to refer to Ms. Victoria Yudin work on this issue, she has created an SQL script including the following data;    

       Customer_ID

       Customer_Name

       Customer_Terms

       Customer_Class

       Price_Level

       Document_Type

       Document_Number

       Document_Date

       Due_Date

       Document_Amount

       Unapplied_Amount

       Days_Past_Due

       Last_Payment_Date

       Last_Payment_Amount

    Reference:

    victoriayudin.com/.../sql-view-for-all-unapplied-receivables-transactions-in-dynamics-gp

    I would recommend considering Crystal Reports in order to create Receivable Transactions Unapplied amount Report.

  • Renee Leung Profile Picture
    Renee Leung 6,775 on at
    RE: Adding field to show unapplied amount in RM Statements report

    I am able to do that via SmartList, but I do not see it possible via Report Writer for the Statements report.

    I tested adding the field 'Current Trx Amount' but it does not populate the correct data to sum of the customer's total balance due!

  • Suggested answer
    Mahmoud Saadi Profile Picture
    Mahmoud Saadi 32,738 on at
    RE: Adding field to show unapplied amount in RM Statements report

    Mr Mariano Gomez has elaborated on this issue before, he included the following;

    Under "Receivable Transactions" Smart List, Add both columns; "Current Transaction Amount" and "Document Amount".

    Now, consider the following scenarios;

    1- Current Transaction Amount = 0, Fully applied transaction

    2- Current Transaction Amount <> Document Amount and Current Transaction Amount > 0, Partially applied transaction

    3- Current Transaction = Document Amount, Transaction with no payment applied to it

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,494 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 231,305 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans