A few months ago, when I first started this #TipTuesday series, one of my first posts was around writing off small customer balances. A reader posted a comment last week with a great question: how do you get a list of invoices written off (for an audit, for example)? What I am going to describe below is what would work if you record write offs using the Write Off Amount field in GP or use the Write Off Documents routine I describe in the blog linked above. The key is this is looking only for documents with a “write-off” amount.
The easy way…
My favourite way to do this would be to use the out-of-the-box Receivables Transactions SmartList. If I can do something with a SmartList, it’s my go to solution 90% of the time. Out of the box Dynamics GP reports often have the information but they need a fair bit of editing (modifying) to make them nice to read, in my opinion.
The out of the box Receivables Transactions smartlist is a great starting point for a simple list of write offs. Here’s what I did to get this list:
Add & remove some columns
First, I started with the * default SmartList favourite. I clicked on Columns on the toolbar and made the following changes:
- Remove the Sales Amount field (in my world, that’s pre-tax so not useful).
- Add the “Original Trx Amount” and “Write Off Amount” currency fields. If you use multi-currency, you might want to add the “Originating ” version of the same fields which would display the amounts in the originating currency.
- Add the field “Date Invoice Paid Off” if you want to search based on dates written off. **
** one big caveat here: this field is not one I would normally rely a lot on. From my experience, it will be fairly accurate for something like this, where the write-off is more-than-likely the “most recent” transaction applied to that invoice. Write-offs by definition are usually writing off the remaining balance of a document, hence, the date of this activity would be the date the invoice was “paid off”. Test it out prior to relying on it entirely.
Enter your search criteria
The first criteria would be “Write Off Amount” and “is not equal to” and $0. That will give you all documents with a write-off amount, using the actual Write-off functionality as described at the top of this post.
From this, what you may get is both invoices and credit memos. So, the second criteria I would suggest including is “Document Type” and either “is equal to” with Sales/Invoices from the drop down list or you could go with “is not equal to” and Credit Memos (and potentially another line with Document Type “is not equal to” and Cash Receipts since write-offs can occur during that type of transaction entry too.)
Lastly, you likely will need a date range. This is where the “Date Invoice Paid Off” field will come in handy, most of the time at least. It will (or should) be the date of the write-off. The Document Date field will be useless as that is the date of the invoice itself, not the write-off.
What is missing with this approach? A few things, as already noted. There is also the fact that this approach assumes there is only 1 write-off per invoice and that it was the most recent thing. For example: if a write-off was agreed to (with the customer) prior to an invoice being paid off, and then later the customer paid the rest of the balance, this too might show up on your smartlist because “it has a write off amount” and the date the invoice was paid off (literally) may fit in your range. So… it’s not perfect!
The hard way…
If the simple approach above doesn’t work or if you need more solid information, the hard way to get the data like this would be to create your own SmartList and join data from both the Receivables transaction tables and the Apply tables. I would pull in all credit types of documents with a write off amount and the corresponding sales invoice and debit types of documents that credit document was applied to. The dates would be “true” if using posting dates from the documents that triggered the write-off. The complicating factor here is the potential for a 1:many linkage if 1 credit memo was used to write off amounts on more than 1 invoice.
Planning and testing is required to ensure you have the right fields and links for what you want to accomplish! This one I will leave as a teaser because I want to do a little more work on getting the right data pulled together for an example and I’ll post a part 2 for this (next Tuesday).
Stay tuned!