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) / List of Customers Unpa...
Microsoft Dynamics GP (Archived)

List of Customers Unpaid Invoices

(0) ShareShare
ReportReport
Posted on by Jill Connelly 135

I am fairly new to GP and have a request from a customer to provide a list of all unpaid invoices.  Making this difficult is that there are many customers that fall under one corporate umbrella (who is making the request) but it is not set up as a national account.  

I have created a SmartList under Receivables Transactions for this customer (luckily they all have the same name so can filter by name) and it shows me accounts with balance due but can't seem to just show the invoices that are UNPAID.  I can't seem to find an indicator on the invoice to show if paid/unpaid.

Any suggestions?  Or a report that would work instead?

Thanks!

 

*This post is locked for comments

I have the same question (0)
  • Dencio Profile Picture
    Dencio 2,172 on at

    Hi Jill,

    You are right on one of the many tracks!  You can add the column Current Transaction Amount which gives you the oustanding amount in any given document.  In addition you can filter the document type to include invoices only but also consider other debit type documents (invoices, debit note, finance charge,etc) assuming all credit type documents (credit note, return) are fully applied. However, the Current Transaction Amount field will be properly updated when you are performing Paid Transaction Removal periodically e.g. monthend, otherwise, this field will show the original functional amount.

    Let us know how you go!

    Cheers,

    Dencio

  • Mike Smith Profile Picture
    Mike Smith 6,840 on at

     Jill,

    Like Dencio and you have found out, SmartList is the fastest and easiest way to get this data out of GP.

    If you need a report that is a little bit better for presentation quality, you could use Crystal Reports or SRS to access the RM20101 Open Transaction table.  This is where the Current Transaction Amount for any given transaction is stored for the system. 

     Just take into account filters for Invoices versus other documents, Void Status, and Transactions where Current Transaction Amount doesn't equal 0.00 (you need to specify the 0.00 since this table will hold the fully paid transactions until the Paid Transaction Removal utility is run).

  • Jill Connelly Profile Picture
    Jill Connelly 135 on at

    Thank you. This does show me the unpaid invoices except since we keep three months when we do the Paid Transaction Removal, I am still seeing invoices they have paid in the past three months.  However I see this being handy for other needs so glad to know which column I was needing to see.

    I ended up running statements for their accounts with balance due and sending them that.  It shows them what they need and more and hoping it will suffice.

    Thank you so much for the responses. Much appreciated.

    Jill

  • Richard Whaley Profile Picture
    Richard Whaley 25,195 on at

    There is also a field in the invoice record in the Open Items Table called Date Fully Paid.  If this field is blank (equals 01/01/1900) the invoice is not fully paid (read "unpaid").

    Some of our books (Information Flow and Posting and Confessions...) talk about matching data like this.  You might want to visit our web site.

  • MG-16101311-0 Profile Picture
    MG-16101311-0 26,225 on at

    In your Smartlist, add the columns Current Transaction Amount and Document Amount. For transactions that have not had any payments applied to it, check for Current Transaction Amount = Document Amount. For transactions that have been partially applied, check for Current Transaction Amount <> Document Amount and Current Transaction Amount > 0. For transactions that have been fully applied, check for Current Transaction Amount = 0.

    Now, if you set all these customers up under a National Account, then you can run either the Historical Aging or the Aging with Options to produce a list of transactions across the board that remain open.

  • Community Member Profile Picture
    Community Member on at

    Hi Jill, just on one of your comments...(being a small bit pedantic here, but again just to be sure)

    'This does show me the unpaid invoices except since we keep three months when we do the Paid Transaction Removal, I am still seeing invoices they have paid in the past three months'   

    Whether or not you ever run the paid transaction removal process, won't affect the remaining transaction amount. This is only affected by Applying credit documents.

    The paid transaction removal process just transfers fully paid transactions to history. A query on the RM Open table may show sales invoices that are fully applied...however a filter in the smartlist of something like 'Remaining Amount Not Equal to 0.00' will filter out these fully paid transactions that are not in history.

    What you are left with is any transaction where the Remaining Amount is not xero - effectively anything that is not fully applied.

    Best regards,

  • Jill Connelly Profile Picture
    Jill Connelly 135 on at

    I was able to get the information in a Smart List using the Current Txn Amount field.  Not sure why I didn't see it working correctly before but it is showing correctly (we just had some odd transactions I needed to clean up).

     Thank you so much everyone for the help! :-)

  • Community Member Profile Picture
    Community Member on at

    Hi jill

    Can you please share your query because I am also new to GP and want to create report somewhat similar to you

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
mtabor Profile Picture

mtabor 1

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans