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 re-inventing 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 you don’t need any changes (additional fields, etc.). That being said, whenever getting a view off the internet, test first and ensure you 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 if you add 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 you start joining on the apply tables, it can get more complicated in a hurry with identifying exactly which fields you want to include and understanding what can happen if you have a 1:many relationship 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. If you’re testing a view like this, include scenarios like that to ensure you’re getting the dollar amount fields that 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. If you own SmartList Builder from eOne, that would be the tool you would 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 your new SmartList a name.
- At the bottom of the Database View list of products, expand Views and you should see the view that was created in SQL
- In my case I simply 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 at least 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
That’s it really, run the SmartList just created and view the data you’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, you 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 you need to filter based on what descriptions a user types in when entering a write-off, if you don’t use the Write-off field consistently.
That’s it… the nice thing about this is with the view already built for you, creating this SmartList takes about 10 minutes end to end if you have 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 you might for a list of write-offs for an auditor that should be within a specific fiscal period.