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!