Tech Tuesday: Negative Values in SmartList Builder
Tuesday, October 29, 2013
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.
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.
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.
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!
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