View Historical Transactions with Negative Amounts Based on Document Types (in SmartList Designer for Dynamics GP)

Regular Expressions in Acumatica

SmartList Designer Tips

Next in our series of SmartList Designer blogs, we’ll look at optimizing a basic purchasing SmartList. (If you need a refresher on SmartList Designer, or missed the first blog, you can find it here.)

Let’s say that a purchasing manager wants to view all the historical transactions for a vendor. However, the out-of-the-box SmartList displays all transactions as a positive amount. That is fine for invoices, but could cause confusion when viewing payments, credit memos, returns, etc. It’s especially helpful to have the various transactions display properly

The goal then is to create a SmartList that displays vendor historical transactions with the invoices as positive and other transactions (payments, credit memos, returns) as negative. We can do that by editing the document type.

Here’s how to Edit the Document Types

First, highlight Purchasing and click New, give the list a name. Then expand Microsoft Dynamics GP>Tables>Purchasing.

Select the following fields from the PM Paid Transaction History (include additional fields if needed)

Click Execute Query to test the result set.

Click to add a calculated field.

Use the Constants tab and change the type to “Text.” The following calculation can be copied and pasted into the Value field and inserted into the Expression (Note: there is a character limit of 80 so copy/paste it in sections.)

case {PM Paid Transaction History File.Document Type} when 1 then {PM Paid Transaction History File.Document Amount} else ({PM Paid Transaction History File.Document Amount}*-1) END

Click Save and receive the message that the calculated field is valid. Then click OK, highlight the field and click OK to add it to the list. The new list will display with the calculated field (Doc Amount). This will display invoices as positive amounts and all other transactions (if finance charge or debit memo) as negative.

You’re now ready to export to Excel, and quickly calculate totals for each vendor or all vendors, or simply eliminate confusion in reporting. If you have questions or need some assistance, visit our support page for more help.

For more SmartList Designer tips like this, view our recorded webinar.

Leave a Reply