Dynamics GP
Dynamics GP
Organizer:
- Organized by
-
-
Smartlist – Customer Statement past due invoices
-
Smartlist – Customer Statement past due invoices
Good Day everyone,
Has anyone created a customer AR showing all past due invoices and amounts using a smartlist to export into Excel?
Thank you
-
6 Replies
-
Christine,
I have used smartlist to generate an AR balance. I have it search for transactions that don’t have a zero balance on the current trx amount field. I am sure you can add some date field to the search criteria to for example, get items older than the prior month. You just have to know that returns, credit memos and payments will show as positive figure in Excel, and adjust for that. (Or include only invoices).
Hope that helps.
-
Pamela,
Thank you so much for your response and providing direction on this.
As I am a visual learner, I had found Receivables Transactions by Customer that was created by our company.
would this work? See Column display Name, example (attached)
For the credit memos, what do these represent. As our company creates returns to the customers. Not sure what you mean by credits. Is this to write off amounts from AR Statement?
Thank you,
Christine
-
-
Hi Christine!
In SmartList, the Receivables Transactions SmartList (SL) is what you want to run. To pull currently open (due) receivables open the Receivables Transaction SmartList list by clicking on the expansion + button. Here you see a list of previously built reports – the asterisk * is a default report provided by Microsoft. Any of them with an Asterisk were built by Microsoft – and they are okay, but for adequate reporting you’ll likely want to add columns.
For the SL you are wanting, in the command bar click COLUMNS and then ADD and highlight and choose any of the aging buckets – i.e. 31-60 days, etc to see the clients balance in that bucket – otherwise you want to pull in the following columns:
Document Amount – the original invoice or credit memo amount
Current Transaction Amount – the amount left owed on the document
Due Date –
Void Status
Posted
At least these columns – but choose any others you want to see – you are NOT altering this for anyone else – only you can see it.
Now, in the command bar, choose ‘Search’ and in the first search criteria choose the column “Current Transaction Amount” and in the filtering command choose ‘Is Not Equal To’ and enter zero (0) – this will filter out all transactions that have been fully paid.
In Search, also choose to search on the column Void Status and choose either ‘is equal to’ ‘Normal’ and the next search field, choose the column Posted and filter that to ‘is not equal to’ ‘No’
You are now left with a list of exactly what is left due in Receivables – but importantly this is a snap of what is due RIGHT NOW – not yesterday or next week, for that you must run the Historical Aged Trial Balance report in the Sales Module.
Good luck and let us know if you have any other questions!
-
-
Christine,
Since you’re quite new to this position (and likely GP), you may not know yet the full extend of the community power and all the shared support..
Have a look at this blog site from a respected GP expert that’s been around the community for 20+ years: Victoria Yudin | Receivables SQL Code
This site is a treasure chest for GP users (and fans), and if you have someone that can help you with creating SQL views in your system, then you’ll be in heaven with her scripts. You can use those scripts both in SmartList Designer or Builder (eOne), or even use them directly in Excel refreshable workbooks.
Good luck!
-
Log in to reply.
