Calculating Number of Days Past Due in SmartList Builder

Do you want to be reminded when a payable becomes past due by a certain number of days?  This can be difficult because it needs to be based on a calculation using the system date and due date of the invoice.  Here is a way to create a calculated field using SmartList Builder to do it.

First, I created a basic SmartList in SmartList Builder using the PM Transaction Open table.

Then I clicked on Calculations, and created a new calculation.

This calculation uses two functions (if you do not see the Functions, click on the white triangle next to the Fields header on the right hand side of the window and change the view to Functions): DATEDIFF to calculate the difference between two dates in days, and GETDATE to pull the system date in to the calculation.  So, in this example, it will calculate the difference (in days) between the due date and the system date.

After you add a calculate field to a SmartList, make sure you mark the Default checkbox for the field if you want it to display by default in the SmartList.

The resulting SmartList has a field called Days Past Due that calculates exactly that!  Then, if desired, you could set search criteria for the SmartList to display only those records where Days Past Due > 90.  Then when you save the SmartList favorite, you can choose to Add a Reminder based on the SmartList as well.  More information on using SmartLists as the basis for custom reminders in GP in a future blog post :)

Christina Phillips is a Microsoft Certified Trainer and Dynamics GP Certified Professional. She is a supervising consultant with BKD Technologies, providing training, support, and project management services to new and existing Microsoft Dynamics customers. This blog represents her views only, not those of her employer.