| Table of Contents |
|---|
Purpose
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 |
If you click once on a Pick List or Mulit-Select Field, available options will appear on the empty place bellow the operator buttons. Options will also display if the pick list / multi-select field is based on a data source URL setting (i.e. Technician or Location).
Building Expressions
To add a field or function to expression builder you have 2 options:
- Click twice on selected field / function
- Type the expression directly - the expression builder will hint you if there is match
Functions
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 |
|---|---|---|---|
| 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 date part of that date. |
| DATEDIFF | (FirstDate, SecondDate, Optional: Unit) | No | Returns the count of the specified date part boundaries crossed between dates. Units: 1) "days" 2) "hours". |
| DATETIMEDIFF | (FirstDate, FirstTime, SecondDate, SecondTime, Format) | No | Returns the difference between two DateTime values in specific format. Format : 1) "YY:MM:DD:HH:MM" 2) "MM:DD:HH:MM" 3) "DD:HH:MM" 4) "HH:MM" 5) "MM" 6) "Months" 7) "Weeks" 8) "Days" 9) "Hours" 10) "Minutes" |
| DATESUBTRACT | (Date, Days) | No | Returns a specified date with the specified number interval subtracted from a specified date part of that date. |
| DETERMINEPEAK | (XValues, YValues, Axis, ExcludeFlags: Optional) | Yes | Determine Peak Intersection On Plotted Curve. XValues: Number field in grid YValues: Number field in grid Axis: 1) "XAxis" 2) "YAxis" ExcludeFlags : Optional Yes/No Field in grid. If selected Yes, the function will skip X and Y values for that grid row. |
| HASVALUE | (Value) | No | Returns "true" if the selected field has value and "false" if there is no value. |
| IF | (Condition, ThenValue, Optional: ElseValue) | No | 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"). |
| LENGTH | (Value) | No | Returns the length of the specified string. |
| MAX | (Value1, Value2,…) | Yes | Return the largest number from a list of numbers. |
| MIN | (Value1, Value2,…) | Yes | Returns the smallest number from a list of numbers. |
| PI | (Precision) | No | Returns number PI round by precision. Precision: Decimal number with max value of 15 |
| 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. |
| PRIOR | (Value) | No | Returns the field in prior data grid row. |
| ROUND | (Value, Optional: Precision, Optional: Method) | Yes | Returns a number rounded to a specified number of digits.. Method: 1) "STANDARD" - 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. 2) "EVEN" - Precision must be 0. Return the value rounded to the nearest even number. ROUND(7.01, 0, "EVEN") = 8, ROUND(6.99, 0, "EVEN") = 6 3) "TEN" - Precision must be 0. Return the value rounded to the nearest 10. ROUND(15.01, 0, "TEN") = 20, ROUND(14.99, 0, "TEN") = 10 4) "DOT05" - Precision must be 0. Return the value rounded to the nearest 0.05. ROUND(5.12, 0, "DOT05") = 5.1, ROUND(5.13, 0, "DOT05") = 5.15 5) "DOT25" - Precision must be 0. Return the value rounded to the nearest 0.25. ROUND(5.12, 0, "DOT25") = 5, ROUND(5.13, 0, "DOT25") = 5.25 6) "DOT5" - Precision must be 0. Return the value rounded to the nearest 0.5. ROUND(5.24, 0, "DOT5") = 5, ROUND(5.26, 0, "DOT5") = 5.5 If no method is selected the function applies "Standard" method. |
| SQRT | (Value) | No | Returns the square root of a number or chosen numeric field. |
| SELECTOPTIONS | (Field, Option1, Condition1, Option2, Condition2...) | No | Select option(s) for chosen field. Have PickList with options: OptionA, OptionB and Number field. SELECTOPTIONS(PickList, "OptionA", Number < 10, "OptionB", Number > 10) will select OptionA if we set number with lower value than 10, OptionB if value is over 10 and nothing if the value is 10(no such condition). |
| SUM | (Value1, Value2,…) | Yes | Returns the sum of a list of numbers. SUM(Grid.Number, Grid.SecondNumber) |
| SUMIF | (Condition, Value1, Value2,…) | Yes | Returns the sum of any given numbers that meet a certain criteria. SUM(">10", Grid.Number) will sum only values from rows in which Number field has Value bigger than 10. |
| TIMEDIFF | (FirstTime, SecondTime, Optional: Rounding) | No | Returns the difference between two time values. Rounding: 1) TIMEDIFF(Time1, Time2, "Closest15") If Time1 = 15:00 and Time2 = 15:22 the result will be 00:15 because 22 is closer to 15 than to 30, if Time1 is still 15:00 but Time2 is 15:24 the result will be 00:30 2) TIMEDIFF(Time1, Time2, "Upper15") If Time1 = 15:00 and Time2 = 15:22 the result will be 00:30 because first upper quarter over 22 is 30, if Time1 is still 15:00 but Time2 is 15:24 the result still will be 00:30 3) TIMEDIFF(Time1, Time2, "Closest30") If Time1 = 15:00 and Time2 = 15:14 the result will be 00:00 because 14 is closer to 00 than to 30, if Time1 is still 15:00 but Time2 is 15:15 the result will be 00:30 4) TIMEDIFF(Time1, Time2, "Upper30") If Time1 = 15:00 and Time2 = 15:14 the result will be 00:30 because first upper half over 14 is 30, if Time1 is still 15:00 but Time2 is 15:24 the result still will be 00:30 5) TIMEDIFF(Time1, Time2, "Closest60") If Time1 = 15:00 and Time2 = 15:29 the result will be 00:00 because 29 is closer to 00 than to 60, if Time1 is still 15:00 but Time2 is 15:30 the result will be 01:00 6) TIMEDIFF(Time1, Time2, "Upper60") If Time1 = 15:00 and Time2 = 15:29 the result will be 01:00 because first upper hour over 29 is 60, if Time1 is still 15:00 but Time2 is 15:30 the result still will be 01:00 |
Text references in the expression builder often require double quotation marks around the text string.
For example:
If you want a field to show up based on another field that is a Yes/No field type when Yes is selected, and the name of the Yes/No field is named, Is this activity complete?. The resulting expression would appear as: Is this activity complete="yes"
To get this result, using the Expression Builder, you would first choose the dependent field from Available Fields. Next, select the = sign from the middle area. Finally, type the dependent value surrounded in quotations.
Logical Operators
To follow is an example of how a a logical operator can be used. In this example, the logical operators are part of the arguments of a nested IF statement.
Tips
- If a formula is incorrect, the expression builder has some validation built into it to alert you of of any bad syntax.
- Text references in the expression builder often require double quotation marks around the text string.