EONE BLOG

Tech Tuesday: Negative Values in SmartList Builder


This week’s Tech Tuesday article is from our very own GPUG All Star Nominee, Nicole Albertson!

After spending a week at the GPUG Summit, I wanted to talk about a little known feature in SmartList Builder.  There is a Field Option called Negative Values that allows you to display a field value as negative based on the value of another field that has the field type of drop down list or yes/no.

For example, if you are looking at the Sales Transaction Work, you may want to display the Document Amount as a negative value if the SOP Type is a Return, but as a positive value for any other SOP Type.  The Negative Values option can be used for this.

I have seen many people create a calculated field with a Case statement in it to look at the SOP Type and if it is a Return, to take the Document Amount * -1.  There is a much easier way to do this.

If we take a look at the SmartList Builder setup below, we can see the Document Type field. If we select it and then hit the Field Options button (blue arrow) next to the Display Name, it will open the Set Field Options Window.


In the Set Field Options window, there is a tab called Negative Values.  If we look at the tab, it gives us the option to “Display as negative based on field”.  In my example, I will select the Sales Transaction Work table and the SOP Type field.  Then I get the option to select the values I want to use to decide when the Document Amount is going to be displayed as a negative value.  I mark the Return and save the SmartList setup.


Now when I run the SmartList, you can see that the Returns are showing as a Negative and the rest of the SOP Types are positive still.


This can be extremely helpful if your end goal is to export the data to Excel.  As you can see, the data comes into Excel as a negative value, so when I try to do a calculation on it, it is already negative and I don’t have to format it as such.


Did you know eOne will be taking SmartList Builder back January 2014? If not or if you have any questions for Nicole or our team regarding SLB please let us know at slb@eonesolutions.com!


1 Comment

  1. Vernon Loveless on March 11, 2020 at 4:33 pm

    I am attempting to use the Negative feature with the Receivables Transactions SmartList downloaded from the eOne web site. The fields I want to show as negative are the “Original Trx Amount” and “Current Trx Amount”. As these fields do not exist in the Work tables, I changed the SmartList to start with the Open table and matched the Work and History tables. For the Work, I matched it to “Blank”. Not sure if any of that will make any difference.
    So, now if I select the “Original Trx Amount” or “Current Trx Amount” (or any currency amount field) and attempt to use the Negative feature I do not see any of the fields that will allow me to direct the Negative feature. As the Table, I select the RM Open File, then click Fields, the only fields that show are: Apply Withholding, Correction, Direct Deposit, EC Transaction, EFT Flag, Electronic, Simplified.
    Similar question, if I down load the AR Aging Current, can I change any of the values to show Negatives?
    Should this work? I am running GP2016 with SLB v 16.00.0031

Leave a Comment





RECENT POSTS


2020 Q3 Promotions
Last Chance! Dynamics GP Integration Bootcamp in July 2020!
Happy Fourth of July from eOne Solutions!
Throwback Thursday: Microsoft CSP Billing Integration Case Study
Throwback Thursday: Tips, Tricks & Gotcha Moments when Connecting

POPULAR POSTS


2016 SmartConnect Integration Bootcamps
Tech Tues: Automatically refreshing Pivot Tables in Excel Refreshable Reports
Happy Thanksgiving from the eOne team!
New Releases of Extender and SmartList Builder
2017 SmartConnect Integration Bootcamps

CATEGORIES

ARCHIVES

open all | close all

TAGS

Business Central CRM Dynamics 365 dynamics crm Dynamics GP Dynamics NAV Econnect Employee Spotlight eone eOne News Events Excel Excel Report Builder Extender Extender Enterprise Flexicoder GP GPUG integration Meet the Team Microsoft dynamics crm Microsoft Dynamics GP Office Relationships partners Popdock promotions release Reporting SalesForce SalesForce.com scripting SmartConnect SmartConnect.com SmartConnect Bootcamp SmartConnect Office Hours SmartList SmartList Builder SmartList Designer SmartView SQL Support Tech Tuesday Templates training Zendesk

Integrate & Automate without Any Code.

SmartList Data has Never Been Faster.

The Easiest Way to Report on GP Data.