Home > Calculated Fields, General - Builders, Payables Management, Sales > Transaction Amounts with correct signs (What you want Credit memos to be negative?)

Transaction Amounts with correct signs (What you want Credit memos to be negative?)

Isn’t it irritating that for most Transactions (all in Payables Management & Receivables Management) are a “positive” number even Credit Memos and Payments?   Don’t you wish you could export GP SmartList to Excel, run the Subtotal feature and get a REAL total?  

Here is how to “fix” this in the Builders (SmartList, Excel & Navigational Lists):

  • Determine what field you can use to decide if the value should be negative or positive – your “type field” (Document Type for Payables; RM Document Type-ALL for Receivables; SOP Type for Sales Order Processing) 
  • Verify the field data type   (Press the Show Details (down chevrons) on the far left of the field header line, Now you should be seeing the Data Type on the second row under each field name)
    • If the Data Type is not Dropdown list (or Yes/No)  you will need to do a calculated field – scroll down… for Option #2
    • If the Date Type is Dropdown list (or Yes/No), then this field will be available in the next step.  But only if you have set the dropdown field options. See my post Drop Down Lists in SmartList Builder for details
  • Select the amount field that you want to correct the sign by clicking on the Display Name of that field
  • Press the Blue Arrow between Display Name and Display on the Field header line to launch the Set Field Options window. (This window changes its appearance based upon the data type for the field.)
  • Press the Negative values tab
  • Select your “type field” from above
  • Check Mark the boxes for the type values that should be negative

Note – these values are only used for displaying in your List.  If you want to do a Summary list – these sign corrections will not be used – you will need to do a Calculated Field.

Option #2 – A Calculated Field to correct the sign of a field:

Here we are going to use our handy-dandy Case Statement:  (For more info see Try CASE )

CASE {type field}
WHEN 4 THEN – {amount field}
WHEN 5 THEN – {amount field}
ELSE {amount field}
END

or

CASE
WHEN {type field} >=7 THEN – {amount field}
ELSE {amount field}
END

Uncheck the Default & Display boxes for the original amount field and check the Default & Display boxes for your new Calculated field so that users see this new field instead.

Works like a charm.

Happy building,
Devon

  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s