Dynamics GP & SmartList Builder: Show Invoices within Aging Periods

Posted by Georgia Stewart on September 28, 2016


Have you ever wanted to get a quick look at Microsoft Dynamics GP customer documents ages based on due dates?  Wouldn’t it be great if you could then export that list to Excel?  You can, by using calculated columns in SmartList Builder, which is an add-on product from eOne Solutions.  Here, I am going to show you how to use calculated fields for the periods to age each invoice based on today’s date, less the document’s due date.  Therefore, resulting in the document current transaction amounts aged as of the current date.

Note:  this SmartList is not meant to replace the RM Aged Trial Balance with Options or RM Historical Aged Trial Balance report.

How-to Create the SmartList

First, start by creating a new SmartList. I called mine ‘_AR Aged Open Transactions’. I used the underscore in front of the SmartList Name so that it would be at the top of my list. I also chose the Microsoft Dynamics GP Product and the Sales Series.

The first Microsoft Dynamics GP Table that I selected for the SmartList is the RM Open File. You can select any fields from that table that you would like to see in your SmartList. I marked the following fields to be included as an example:

  • Customer Number
  • Document Number
  • Due Date
  • Document Date
  • Salesperson ID
  • Sales Territory Code

The second Microsoft Dynamics GP Table that I selected for the SmartList is the RM Customer MSTR. I then selected the Customer Name to be included in the SmartList.

Next, I created several calculated columns. Click the Calculations button in the top of the SmartList Builder window. Then click the + sign at the upper right of the window to add a new calculation.

Doc Type – The Document Type field that is in the RM Open File is a list of numbers. I used a calculated field to provide text for each of those numbers.

Here is the Calculation used in the calculated field:

CASE
{RM Open File:RM Document Type-All}
When 1 Then 'Invoice'
When 2 Then 'Scheduled'
When 3 Then 'Debit Memo'
When 4 Then 'Finance Charge'
When 5 Then 'Service Repair'
When 6 Then 'Warranty'
When 7 Then 'Credit Memo'
When 8 Then 'Return'
When 9 Then 'Payment'
Else ''
End

Original Amount – The Original Transaction Amount in the RM Open File is always a positive number. I used a calculated field to make it a negative number when the Document Type is a Credit Memo, Return, or Payment.

Here is the Calculation used in the calculated field:

CASE {RM Open File:RM Document Type-All}
When 7 Then ({RM Open File:Original Trx Amount} * -1)
When 8 Then ({RM Open File:Original Trx Amount} * -1)
When 9 Then ({RM Open File:Original Trx Amount} * -1)
Else {RM Open File:Original Trx Amount}
End

Current Amount – The Current Transaction Amount in the RM Open File is always a positive number. I used a calculated field to make it a negative number when the Document Type is a Credit Memo, Return, or Payment.

Here is the Calculation used in the calculated field:

CASE {RM Open File:RM Document Type-All}
When 7 Then ({RM Open File:Current Trx Amount} * -1)
When 8 Then ({RM Open File:Current Trx Amount} * -1)
When 9 Then ({RM Open File:Current Trx Amount} * -1)
Else {RM Open File:Current Trx Amount}
End

In my SmartList example, I created the following Aging Periods using the calculation shown for each period. Each of the calculations uses the Due Date from the RM Open File, but you could use the Document Date instead. Also, the Field Type should be set to Currency for each calculated field. The calculation is a bit more complex because I wanted to be sure the Current Transaction Amount is negative when the Document Type is a Credit Memo, Return, or Payment. Here is an example of what the window looks like for the calculation for the Current aged period.

Current

CASE
When Datediff(d, {RM Open File:Due Date}, getdate()) < 31
Then
CASE When {RM Open File:RM Document Type-All} > 7
Then ({RM Open File:Current Trx Amount} * -1)
Else {RM Open File:Current Trx Amount}
End
Else 0
End

31 to 60 Days

CASE
When Datediff(d, {RM Open File:Due Date}, getdate()) between 31 and 60
Then
CASE When {RM Open File:RM Document Type-All} > 7
Then ({RM Open File:Current Trx Amount} * -1)
Else {RM Open File:Current Trx Amount}
End Else 0 
End

61 to 90 Days

CASE
When Datediff(d, {RM Open File:Due Date}, getdate()) between 61 and 90
Then
CASE When {RM Open File:RM Document Type-All} > 7
Then ({RM Open File:Current Trx Amount} * -1)
Else {RM Open File:Current Trx Amount}
End
Else 0
End

91 to 120 Days

CASE
When Datediff(d, {RM Open File:Due Date}, getdate()) between 91 and 120
Then
CASE When {RM Open File:RM Document Type-All} > 7
Then ({RM Open File:Current Trx Amount} * -1)
Else {RM Open File:Current Trx Amount}
End
Else 0
End

Over 120 Days

CASE
When Datediff(d, {RM Open File:Due Date}, getdate()) > 120
Then
CASE When {RM Open File:RM Document Type-All} > 7
Then ({RM Open File:Current Trx Amount} * -1)
Else {RM Open File:Current Trx Amount}
End
Else 0
End

After you create the calculated fields, you need to highlight the Calculated Fields in the Tables section and mark each of the fields so they display in your SmartList.

Next, you will need to add two restrictions so that it doesn’t display 0 dollar amounts and/or voided documents.  To do this, click the Restrictions button at the top of the SmartList Builder window. Then click the + at the top of the window to add a new restriction. Each of the restrictions I added are from the RM Open File.

  1. Field: Current Trx Amount; Restriction: Is Not Equal To; Value: 0
    (restricts the SmartList to display open documents that have a current transaction amount)
  2. Field: Void Status; Restriction: Is Equal To; Value: 0
    (restricts the SmartList to not display voided documents)

You may also want to arrange the columns in SmartList Builder. Click the Columns button. You can highlight each column and move it to the location where you want it to display. Columns at the top of the list will display on the left in the SmartList window.

Save the SmartList Builder, then open SmartList and allow the changes to populate.

Here is the example from my local version of Dynamics GP.  (Click on the image below to enlarge)

You can now use the SmartList to export the data to Excel. When the data is in Excel, you can create sub-totals for each customer.

Learn more tricks like this one, by attending one of our free webinars.  View our upcoming schedule at www.crestwood.com/events and register today.  If you have further questions, contact us at Support@Crestwood.com


Share this:
 
 

Comments:

Add your comment: