Last week, my #TipTuesday was a post about how to get a list of write-offs of receivables transactions. I went through what I called the "easy" way but it had a few caveats. It would work in what I believe to be only the most perfect of scenarios. This is the follow-up where I'll walk through in fairly high-level detail a way that should be a lot more reliable as it's based on the apply table(s) in Dynamics GP.

Step 1 - Start with the AR Apply Detail view from Victoria Yudin

Instead of reinventing the wheel, I'm starting with a SQL view Victoria has on her website. It's ready to "run" in SQL Server Management Studio if I don't need any changes (additional fields, etc.). That being said, whenever I get a view off the internet, I test it first and ensure I know exactly what it is doing first!

Please note, there are some caveats such as she's only including functional currency amounts in the view. Things like that are very easy to change by adding a few more fields to the SQL view but that's not the point of this post.

One thing to keep in mind is once users start joining on the apply tables, it can get more complicated in a hurry with identifying exactly which fields to include and understanding what can happen if there are 1:many relationships in the mix. Victoria mentions this in her comments on the first few lines too, mentioning that credit documents applied to more than one debit document will appear on multiple lines. When testing a view like this, include scenarios like that to ensure the dollar amount fields make sense as it's very easy to end up with duplication by misunderstanding the fields.

Step 2 - Create a SmartList using the view

Next, I used the out-of-the-box SmartList Designer (SLD) tool to create a new SmartList in Dynamics GP. For those who own SmartList Builder from eOne, that would be the tool to use instead. I'm not going to turn this into a "how to create a SmartList" post so here are the quick points that I followed to create mine (and specific to SmartList Designer):

  • Open SmartList, click on New to open SLD and give my new SmartList a name.
  • At the bottom of the Database View list of products, expand Views and I see the view that I created in SQL
  • I added it and executed the query to preview the results and that was it. (Other than setting the Series to Sales so it would appear in a meaningful place in the SmartList object hierarchy). The view has enough fields to get started and is joined to the RM Customer Master to include the customer name. If nothing else, start with it on its own and then add fields to the view if needed or via joining to other tables in SLD.
  • I added the new SmartList to a security task that was appropriate so users could "see" it.

Step 3 - run the SmartList

Now, run the SmartList just created and view the data I've got. Now, back to the original point of this post: getting write-offs. What this view/SmartList shows is a list of credit documents (returns, credit memos and payments) and what they are applied to. From here, if I follow the same method as I did in my first post, I would search for Writeoff amount <> 0, which is displayed below. I'm still assuming that what I'm searching for are write-offs using the Write-off field (as opposed to random credit notes applied to invoices which are then hard to differentiate from "normal" credit notes!).

The view contains any apply records from a credit-type of document so if that methodology doesn't apply, I can instead search by document number or date range or whatever might help. One suggestion would be to amend the view to include the Transaction Description from the RM transaction tables if I need to filter based on what descriptions a user types in when entering a write-off if I don't use the Write-off field consistently.

That's it… the nice thing about this is with the view already written, creating this SmartList takes about 10 minutes end to end if a user has the right permissions to create the SQL view and then create the SmartList etc. It's going to be more reliable when filtering to certain date ranges like users might for a list of write-offs for an auditor that should be within a specific fiscal period.