A few months ago, when I first started this #TipTuesday series, one of my first posts was about writing off small customer balances. A reader posted a comment last week with a great question: how do I get a list of invoices written off (for an audit, for example)? What I am going to describe below is what would work if I 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:

  1. Remove the Sales Amount field (in my world, that's pre-tax so not useful).
  2. Add the "Original Trx Amount" and "Write Off Amount" currency fields. If I use multi-currency, I might want to add the "Originating" version of the same fields which would display the amounts in the originating currency.
  3. Add the field "Date Invoice Paid Off" if I 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 before relying on it entirely.

Enter search criteria

The first criteria would be "Write Off Amount" and "is not equal to" and $0. That will give me all documents with a write-off amount, using the actual Write-off functionality as described at the top of this post.

From this, what I may get is both invoices and credit memos. So, the second criterion I would suggest including is "Document Type" and either "is equal to" with Sales/Invoices from the drop-down list or I 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, I 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) before an invoice was paid off, and then later the customer paid the rest of the balance, this too might show up on a smartlist because "it has a write-off amount" and the date the invoice was paid off (literally) may fit in the date range. So… it's not perfect!

The hard way…

If the simple approach above doesn't work or if users need more solid information, the hard way to get the data like this would be to create a new 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 the 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 are required to ensure users have the right fields and links for what they 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!