Calculated Fields in Extender and the Builders

Here are a few tips to keep in mind when using the Extender Calculated Fields. These can also apply to SmartList Builder, Excel Report Builder, and Navigation List Builder.

1. When trying to set a value to something that is in a drop down list, keep in mind that most likely the data is stored as a numeric value in the SQL tables. For example, with the SOP Type, in GP, you will see Quote, Order, Invoice, Return, Back Order, and Fulfillment Order. In SQL, this is stored as 1, 2, 3, 4, 5, and 6. If you are doing something in a calculated field, you need to use the numeric value for the type you want.

Here is an example of one where we are looking for the Back Orders.

CASE {Sales Transaction Work:SOP Type}
WHEN 5 THEN 'We apologize for the Delay'
ELSE 300
END

2. Remember that the calculated fields are using some SQL Syntax. When entering string values, you need to have single quotes around the value. You can see that in the Case Statement above where we set the value of the field to "We apologize for the Delay." Because it is a string field, you need the single quotes.

3. When entering in a field from a table that is on your setup, you need to list it in the format of {table name:field name}. When you use the list of fields that is there, it will do this for you, but if you are typing them in, the format and name need to be exact.

4. The Case statement is what can be used as a function to create an If\Then type of statement.

5. There are occasions where the data type isn't what you need it to be. For example, if you have an integer value being stored in a User Defined Field, it is going to be setup as a String value. If you want to do a calculation on it, you have to first change it to an integer value. To do this, you would use the Cast function to change the data type on the field.

6. You cannot use a calculated field in another calculated field. Instead, you would need to enter in the calculation you used for that field where you want to use the field.

7. The calculations use the standard mathematical practices. It will multiply and divide before it does addition and subtraction. It will also do what is in parenthesis ( ) together first.

8. There is a Knowledge Base article with a description of each of the functions available for the calculated fields along with syntax examples. This is Knowledge Base 929207.
support.microsoft.com/default.aspx?scid=kb;EN-US;929207

 

Thank you,
Nicole A.