Formula
Introduction
Formula allows the user to calculate the value of an expression according to the current value of its variables. An expression is an equation such as "MAX(x,0) + 3 * y" or "dt_date > #2000-08-22# = false" that can be specified with the "Set Formula Text" input connector or in the Property Panel. Formula expressions can contain variables, mathematical operators, constant values, and functions. They can return a number, date, text, or true/false value as the result of their evaluation. Each of the variables in the expression represents an assignable value of a specified data type. Variables can be any of the following data types: number, text, true/false, date, or single-attribute entryset. A variable's data type assignment affects both how it behaves in the expression and what values it accepts. An expression is valid only when its variables are assigned appropriate data types for their context.
Functions
The Formula module supports a number of built-in functions that can be used to perform mathematical and analytical computations. The syntax for many of these functions is similar to their syntax in Excel. You can browse the available functions by clicking the "Browse Functions..." button in the Property Panel.
Variable Data Type Syntax
A variable is any text in an expression that is not a reserved constant or function name. A variable may only contain alphanumeric characters and '_' and may only contain numbers if they are postfixed. There is a syntax for automatically assigning a data type to a variable in an expression. This is to end a variable with a underscore followed by its desired data type. "x_date" will automatically become a date variable; "xValues_entryset" will automatically become an entryset variable.
Setting the Formula's Expression
When a Formula is added to a custom module, it contains no expression and no variable input connectors. When the expression is first specified via either the Property Panel or the "Set Formula Text" input connector, the variables in the expression are discovered. If the expression is valid and its variables are assigned valid data types for that expression, then dynamic input connectors are created for each variable and the expression is ready for evaluation. If not, an error message will appear and the expression will not be able to be evaluated. Remember that it is only possible to set the data types of new variables in the Property Panel or by using the "_datatype" syntax. Whenever a variable's data type is changed, the expression is tested for validity and regenerated for testing.
When a Formula's expression is replaced with a new expression, the new variables are discovered and compared to the existing variables. For variables with the same name as old variables, any existing connections and values are maintained. For new variables, new input connectors are created. Variables that are no longer present have their input connectors discarded.
Null Values
It is possible for the values used as input to variables in a formula to be null, especially if they are coming from an Excel sheet or other external data source. Null values do not make sense in many contexts, for example in numerical operations. When the formula cannot be evaluated due to a null value the Formula module will display an error. Null values on input connectors for variables that are of the type Text will be automatically converted to an empty Text value: "". For variables that are not the Text type, null values can be detected with the ISNULL() function, which is located under the "Logical" category in the function browser.
Formula as Plug-In
A Formula will frequently be used as a plug-in to an Attribute Calculator. This enables the Attribute Calculator to use the Formula to calculate the values of multiple entries in an entryset. See the Attribute Calculator documentation for further information.
Property Panel:
Formula Properties
- Browse Functions
- Opens a window allowing you to browse information about available functions and select one to insert in an expression.
- Enter Formula here:
- The expression that the Formula evaluates.
Testing
- Send "Test" result value as Output Message
- Checking this box causes the the result calculated with the test input variables to be sent to the "Get Output" output connector.
- Test
- Calculates the result value using the variable values as input to the formula's variables.
- Result
- The value calculated by the expression using the variable values.
- Variable
- A list of variables detected in the current expression. Each variable's value can be set via its dynamic "Set Variable [x]" input connector. These values can also be set manually for testing by editing the "Value" field.
- Type
- The data type that the variable will supply in the expression. This determines how the expression will operate and which values are acceptable for the variable.
- Value
- Specify values manually for each variable in order to test the result using the current expression.
Input Menu:
Formula Variable Connectors → Set Variable [x] (Dynamic Menu)
Sets the value for user defined variable [x] in the Formula's current expression.
Accepted Datatypes:
- Number
Triggered Outputs:
- Output Message:
- Get Output
Property Connectors → Set Formula Text
Sets the calculated expression. An incoming formula expression must be syntactically valid.
Accepted Datatypes:
- Text
Triggered Outputs:
- Output Message:
- Get Formula Text
- Output Message:
- Get Output
Output Menu:
Get Output
Gets the calculated value of the Formula according to the current value of its variables.
Output Datatype:
- Number
Triggered By:
- Input Message:
- Property Connectors → Set Formula Text
- Input Message:
- Formula Variable Connectors → Set Variable [x]
- Action:
- Fire-On-Connect
- Action:
- Testing the expression in the Property Panel with "Send Test Evaluation as Output Message" selected.
Get Formula Text
Gets the current Formula Expression.
Output Datatype:
- Text
Triggered By:
- Input Message:
- Property Connectors → Set Formula Text
- Input Message:
- Formula Variable Connectors → Set Variable [x]
- Action:
- Fire-On-Connect