How to Add Calculated Field in SmartList Builder and Designer

Introduction

One of the common requests we receive from clients who run multi currency businesses is how to show originating dollar amounts vs. functional dollar amounts in a SmartList report. It is very simple to add additional available columns to any existing SmartList reports like below, the Originating Document Amount is added to the Payables Transaction report.

However, you may notice that originating dollar amounts for the transactions made in functional currency are displayed as blank or $0 instead of the actual amounts. Is it possible to show Originating Amounts for non-functional currency transactions and actual amount for functional currency transaction in the same column? The short answer is YES!

In this blog, we use this originating amount topic as an example to talk about how easy to add a calculated field through SmartList Builder or through GP SmartList Designer with extra steps if you do have SmartList Builder purchased.

 

Add Calculated Field in SmartList Builder

In SmartList:

  1. Click on Payables Transactions
  2. New or Modify to open SmartList Builder window
  3. Calculations to open Calculated Field window
  4. Add to open Add Calculated Field window
  5. Enter Field Name and Select Field Type
  6. Type in the Calculation expression
  7. Validate to ensure no errors in expression
  8. Save

9. Smart Default to display this newly added field

10. Save. The Calculated Field becomes one of the available fields that can be added as one of the report columns.

For functional currency (USD) it shows the document amount instead of blank (as shown in the Originating Document Amount) and for non-functional currency (e.g. NZD) it shows the originating Document Amount.

 

Add Calculated Field in SmartList Designer

If you don’t have SmartList Builder installed, calculated fields can also be added using SmartList Designer which is a tool that comes with GP but you will need to take some extra steps to create your own new SmartList first.

In SmartList:

  1. Click on Payable Transactions
  2. New to open SmartList Designer Window
  3. Enter a List Name
  4. In SmartList Designer Expand Database View panel – View – Company – Payables Transactions – Select Originating Document Amount (or other fields you would like report to show)
  5. Click on the fx icon open the Calculated Field window
  6. Add to open the Create Expression window

7. Enter the Name of this new field e.g. Originating Amount and select Type e.g. currency then use the Table Fields, Functions and Constants tabs to create the Expression

TIP: To insert a Space, or = , or THEN, you need to use Constants tab, select ‘Text’ as type enter the value you need e.g. enter a space “ “ then click insert to build the expression.

8. Enter a relationship for the old Payable Transactions to join the new Payable Transactions fields added

9. This is how the calculated field amount looks like in the final report