...
Formula editor is used to create Visualization and Calculation rules for fields. You can access it by creating new field or editing and existing field in the Form Designer. At the bottom of the screen you can choose either to add Calculation or Visualization rule for the chosen field by clicking Edit. Some Fields may have only Visualization rule available if they can't be used in calculations.
Expression Builder View
| Reference | Description |
|---|---|
| 1 | Expression builder |
| 2 | Available fields for selection |
| 3 | Arithmetic and logical operators |
| 4 | Available functions |
| 5 | Available options in pick list and multi-select fields |
...
When a function is inserted it will provide additional information about how the function must be structured.
| Function | Argument | Aggregate field(s) can be used in | Description | POWER | (Value or Field, power) | |
|---|---|---|---|---|---|---|
| ABS | (Value) | No | Returns the absolute value of a number. | |||
| AVG | (Value1, Value2,…) | Yes | Returns the average of a list of numbers. | |||
| AVERAGEIF | (Condition, Value1, Value2,...) | Yes | Returns the average of all numbers in a range of values, based on a given criteria. | |||
| BETWEEN | (Value, Lower Value, Higher Value) | No | Returns true if given value is between two numbers. | |||
| CONTAIN | (Field, Value) | No | Use this function to evaluate selected items in a pick list or multi-select field. For example, CONTAIN(PickList, "Pass"). If you want your fields to be visible when two options are selected(available only in Multilist) you can combine two CONTAIN function, or any other: CONTAIN(MultiList, "Option 1") AND CONTAIN(MultiList, "Option 2") OR CONTAIN(MultiList, "Option 3") Every possible logical operators can be applied and if you want to change the priority just add parenthesis where necessary: CONTAIN(MultiList, "Option 1") AND (CONTAIN(MultiList, "Option 2") OR CONTAIN(MultiList, "Option 3")) | |||
| COUNT | (Value1, Value2,...) | Yes | Returns the number of arguments. | |||
| COUNTBLANK | (Value1, Value2,...) | Yes | Returns the number of arguments that are empty. | |||
| COUNTIF | (Condition, Value1, Value2,...) | Yes | Returns the number of arguments that meet a certain criteria. | |||
| CUME | (Value) | No | Takes the prior record Cume value and adds the current record "Field". | |||
| DATE | (Value) | No | Returns plain text as date | |||
| DATEADD | (Date, Days) | No | Returns a specified date with the specified number interval added to a specified datepart of that date. | |||
| POWER | (Value, power) | No | Returns a number by a chosen power. Static numbers can be typed in or other fields can be referenced (if they are numeric fields). Powers must be integers. | |||
| SQRT | (Value or Field) | No | Returns the square root of a number or chosen numeric field. | |||
| MIN | (Value1, Value2,…) | Returns the smallest number from a list of numbers. | ||||
| MAX | (Value1, Value2,…) | Return the largest number from a list of numbers. | AVG | (Value1, Value2,…) | Returns the average of a list of numbers. | |
| SUM | (Value1, Value2,…) | Returns the sum of a list of numbers. | ||||
| ROUND | (Value, Optional: Precision) | Rounds a number. If no Precision is entered the default behavior will be rounding to the nearest integer - ROUND(4.5) = 5, ROUND(4.44456) = 4. If you want rounding to second digit after the decimal separator, include the optional precision argument. For example, ROUND(4.44456, 2) = 4.44. | ||||
| ROUNDTOEVEN | (Value) | Rounds a number to closest even value - ROUNDTOEVEN(5.4999) = 4, ROUNDTOEVEN(5.500001) = 6 | ||||
| ROUNDTOTEN | (Value) | Rounds a number to the nearest 10. | ||||
| HASVALUE | (Field) | Returns "true" if the selected field has value and "false" if there is no value. | ||||
| CONTAIN | (Field, Value) | Use this function to evaluate selected items in a pick list or multi-select field. For example, CONTAIN(PickList, "Pass"). If you want your fields to be visible when two options are selected(available only in Multilist) you can combine two CONTAIN function, or any other: CONTAIN(MultiList, "Option 1") AND CONTAIN(MultiList, "Option 2") OR CONTAIN(MultiList, "Option 3") Every possible logical operators can be applied and if you want to change the priority just add parenthesis where necessary: CONTAIN(MultiList, "Option 1") AND (CONTAIN(MultiList, "Option 2") OR CONTAIN(MultiList, "Option 3")) | ||||
| IF | (Condition, ThenValue, Optional: ElseValue) | Use the If / Then / Else function to conditionally evaluate data conditions and perform various calculations based on that data condition. For example, If(Result1 > 10, "Success", "Fail"). The Else value is optional and if you skip it and the condition is not satisfied no value will be assigned to field - If(Result1 > 10, "Success"). |
...