A calculated field allows the entry of a formula. Calculated fields are entered using SQL syntax.
There are 5 field types that can be used for the calculated field; Currency, Number, Percentage, Quantity, and Short String.
|
The calculated data is not stored. All calculations are done on the fly when the data is retrieved. |
To add a calculated field:
| 1. | Select the Field you want the Calculated field to be in. |
| 2. | Click the Add Calc button. |
| 3. | Enter a Field Name for the calculated field. |
| 4. | Select the field type. If you selected Currency, Percentage or Quantity for the Field Type, select the number of decimal places as well. |
| 5. | Enter the script for the calculation. By clicking on the drop down arrow on the header in the Fields/Functions section, you can switch back and forth between the Fields and Functions available. |
| a. | Add fields from the Extender Window/Form to the calculation by double-clicking on a field from the Fields list or selecting the field and clicking the Add button ( ). |
| b. | Add functions to the calculation by double-clicking on a function from the Functions list or by selecting the function and clicking the Add button (). The Functions list is displayed by selecting Functions from the View button above the Fields list. |
| c. | Add Constants to the calculation by clicking in the Formula section where the constant is to be placed and type in the constant. |
| 6. | Click Save. |
Functions
Extender calculated fields can use all of the functions that are available in Transact-SQL. For convenience, the most common of these functions have been added to the functions list on the Edit Calculated Field window. These functions are divided into five areas:
| • | Arithmetic Operators |
| • | Date Functions |
| • | Mathematical Functions |
| • | System Functions |
| • | String Functions |
Removing Calculated Fields
Removing a calculated field removes all calculations. You may need to do this to recreate a calculated field after you have made changes to the Extender resource.
To remove a calculated field:
| 1. | Highlight the calculated field |
| 2. | Click the Remove Field Button. |
| 3. | When prompted with the message “Are you sure you want to remove this calculation?”, click Delete to remove the field. |
|
If Multicurrency Management is registered, calculated fields with a field type of currency can be displayed in functional and originating currencies. You do not have to create 2 calculations. The functional currency is calculated by applying the exchange rate to the result of the originating calculation. See the “Multicurrency” section for more details of how to set up multicurrency for a form. |
While you cannot use one calculated field in another calculated field, you can copy the setup of a calculated field into another. This is especially useful when you have complex calculations that are performed in more than one location in Extender.