SmartList Builder: Displaying Negative Amounts based on Document Type
Here's a nugget I picked up at GPUG Summit 2013 Tampa by attending the BDA06 Going Deep with SmartList Builder (Complete survey) session hosted by Nicole Albertson from eOne Business Solutions.
Traditionally, whenever I needed to get, say, return document amounts or credit document amounts in general to show as negative in a SmartList, I would recur to the outdated method of setting up a calculated field based on a T-SQL CASE statement to take the document amount and multiply it by negative 1 (-1). The calculation looked something like this, in the case of a Return document in a Sales Transactions smart list:
This method has been used for several years now, but not without its flaws:
1. If you have a large data set, performance can be negatively impacted. Remember, the more calculations, the more pass through SQL that needs to be executed by SmartList Builder. This method in particular, seems fairly complex for a simple arithmetic operation.
2. In retrospect, this method is good if you have a fairly decent understanding of T-SQL, which puts you in the category of a power user or at least an entry level T-SQL developer. But what if you are just the average end-user? - no pun intended.
Simply put, there's got to be a better way, right?
As it turns out I can simply click on the Document Amount field, then click on the Field Options expansion button to open the Set Field Options window.
Once in the Set Field Options window, you just need to click on the Negative Values tab. You will then mark the Display as Negative Value based on Field check mark and choose the table and field that you will use as basis to flip the sign - in the case of a Sales Transaction, that would be the SOP Type field.
SmartList Builder is "smart" enough to determine the possible values for the field in question - in fact, Yes/No and List Valued fields are usually supported.
Once again, in the case of the SOP Type list valued field, you would select the Return document type as the option to display the document amount as negative.
How handy is this? And the best of all, it required zero code.
Until next post!
MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/
Traditionally, whenever I needed to get, say, return document amounts or credit document amounts in general to show as negative in a SmartList, I would recur to the outdated method of setting up a calculated field based on a T-SQL CASE statement to take the document amount and multiply it by negative 1 (-1). The calculation looked something like this, in the case of a Return document in a Sales Transactions smart list:
 |
| Calculated Field for a Negative Value based on Document Type |
This method has been used for several years now, but not without its flaws:
1. If you have a large data set, performance can be negatively impacted. Remember, the more calculations, the more pass through SQL that needs to be executed by SmartList Builder. This method in particular, seems fairly complex for a simple arithmetic operation.
2. In retrospect, this method is good if you have a fairly decent understanding of T-SQL, which puts you in the category of a power user or at least an entry level T-SQL developer. But what if you are just the average end-user? - no pun intended.
Simply put, there's got to be a better way, right?
As it turns out I can simply click on the Document Amount field, then click on the Field Options expansion button to open the Set Field Options window.
 |
| Set Field Options window |
Once in the Set Field Options window, you just need to click on the Negative Values tab. You will then mark the Display as Negative Value based on Field check mark and choose the table and field that you will use as basis to flip the sign - in the case of a Sales Transaction, that would be the SOP Type field.
 |
| Negative Values tab on Set Field Options window |
SmartList Builder is "smart" enough to determine the possible values for the field in question - in fact, Yes/No and List Valued fields are usually supported.
Once again, in the case of the SOP Type list valued field, you would select the Return document type as the option to display the document amount as negative.
How handy is this? And the best of all, it required zero code.
Until next post!
MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/
Comments