Within the Edit Attribute Formula window, complex data mappings can be configured using the available functions, section fields, meta fields, and normal fields from the selected source tables. These options are available in the Object Editor pane.
Functions – Functions are designated as either Standard or Aggregate and are grouped by their type.
Normal Fields – The fields displayed in the Object Editor depend on the selected Source Table. By default, Current is the source table selected in the Mapping Project window prior to opening the Edit Attribute Formula window. Use the Source Table drop-down menu to select a different source table, and hence, display different fields.
Section Fields – Section Fields are general, universal field values available for all tables that contain information pertaining to the records on the table. Different data sources will have different section fields but section fields are available for all tables in the data source. Section fields generally start with the ‘#’ sign. For example, a section field for an Excel file is #tablename, which has the name of a worksheet.
Meta Fields – Meta fields are fields associated to the entire data source and have values that do not change from record to record. Meta fields may include items such as the name of the data source. Meta fields also start with the ‘#’ sign.
Note: HEADER data, included in the TRX file, is mapped to the Meta Fields tab. For example, Height (inch) is a property included in the HEADER section of a TRX file and gets mapped to the Meta Fields tab as #height_inch.
HEADER data displayed in a TRX file:
HEADER fields in Link, highlighted in yellow and displayed under the Meta Fields tab:
|
|---|
How to Build a Formula
Formulas can be typed directly into the Text Editor pane or can be built by selecting a function or field from the Object Editor pane. The Search String box can be used to filter the functions or fields to easily find the desired item.
Using text formulas to calculate values involves two distinct processes: Parsing and Evaluating.
Parsing – Process of interpreting the text and converting it into a set of equivalent computer language objects called a parse tree.
Evaluating – Process of using the parse tree objects to return a specific value based on the values of the field objects in that tree.
It should be noted that parsing is performed once when a formula is loaded or created, but evaluation can be done millions of times (each time that formula is used). Parsing is generally a slow process with perhaps tens of thousands of parses per second achievable. Evaluating is fast with millions of evaluations per second achievable, depending on the complexity and type of functions used.
The Edit Attribute Formula window includes two buttons that execute Parse and Evaluate processes, which allow users to determine if the function syntax is correct (i.e., the written text could be correctly converted to a parse tree) and if the value returned from any branch or node on the tree is the expected value (i.e., test the function).
Click on the Parse button to parse the formula and see the graphical representation of the parse tree generated from the formula in the Tree View pane. Verify that the syntax for the formula is correct. See the Formula Syntax section below for details on the logic behind the syntax of formulas.
Note: Pressing the Enter key while in the formula text editor also runs the parser (i.e., same as clicking the Parse Button). |
|---|
Click the Evaluate button to assess the output of the formula. Before clicking the Evaluate button, double click on each field in the left tree view and enter the value for that field to use when evaluating the expression. Note that some functions require multiple records and field values, and this feature does not support the evaluation of formulas containing these functions. If a function node is clicked, the result of the evaluation of that branch is returned.
Single click on a field, constant, or formula in the tree view to replace the entire field, constant, or formula with a new expression. For example, when a field is selected, it will become highlighted in the formula Text Editor pane. The selected field can then be replaced by clicking another field from Fields tab in the Object Editor pane.
Formula Syntax
The formula parser interprets formula text strings using a recursive algorithm that matches the formula text syntax. This is beneficial since it increases parsing performance while simplifying the formula writing syntax and the code required to parse. To capture these benefits, users may experience a small learning curve when first writing formulas.
The formula syntax is such that at any level (the trunk or branch of the parse tree) a formula is simply a single parameter. A parameter, however, can be of three distinct types: A constant, a field, or a function. Each of these parameter types has specific valid starting characters that allow the parser to interpret them as such and “parse” them into their corresponding object types.
•Constants – Can be either fixed decimal numeric values that must start with a numeric digit, or string constants that must be enclosed with single quotes.
•Fields – Can be any field name enclosed in square brackets with or without a table name prefix (e.g., [table.field]). It should be noted that table name prefixes are required when the field values need to change based on the rows in a table (e.g., when using aggregate functions). If the table name prefix is not added to the field, then the field value is fixed at the value of the current row.
•Functions – Can be any valid function name that must start with a letter or underscore and must end with a round parentheses that contains the parameter(s) used in the function. For example, FUNCTIONNAME(param1,param2,param3) defines a function called FUNCTIONNAME with three parameters.
Since functions can contain parameters (any parameters) within their parentheses, any calculation can be achieved based on these three general parameter types with no ambiguity as to order of precedence (i.e., the order in which operations are to be executed).
Example
If a user wanted to multiply two values (3 and 5) together, the formula syntax would be:
MULTIPLY(3,5), which would return a value of 15.
To add the value of field X to 5, the formula syntax would be:
MULTIPLY(3,SUM(5,[X]))
In this case, the first parameter of the MULTIPLY function is a constant value of 3 and the second parameter is a SUM function with two parameters (constant parameter 5 and field parameter X).