Collect Forms – Formulas

<< Click to Display Table of Contents >>

Navigation:  Collect > Collect Enterprise > Template Designer Page > Formula Builder >

Collect Forms – Formulas

Formulas add the ability to narrow results, and to select, view, and automate different types of fields and parameters. The Formula Builder is used to create complex formulas that can be configured using the available functions, standard fields, section fields, meta fields, and aggregate fields from selected forms. These options are available in the Formula Object Editor.

 

Formula Builder Components 

 

Value – The actual value attributed to a field. Values may be numeric (start with a number) or a string (start with a quote). Some field values, for example a geography field, have string representations that are in JSON format. To enter an empty/blank value, select the empty box on the right-hand side. The formula parser will add an empty string value of '' to the formula.

 

Function – This tab provides a list of all functions available to use in building formulas. Hovering the mouse over any function provides a tooltip summary of the function. The functions are described in detail in the Functions chapter and examples are provided.

 

Fields – This tab provides access to form fields on the current form or a parent form as well as meta data. Fields are grouped by Standard Fields and Section Fields. Standard Fields are fields directly from the form (current or parent). Section Fields are general, universal field values available for all fields on the form. See Section Fields for more information. Meta fields are fields associated to the entire form template and have values that do not change from form to form. See Meta Fields for more information.

 

Col-Formula_Builder_Fields_Chooser

 

Aggregate Fields – Refers to fields inside aggregate functions, which process all values of fields on specific tables. Aggregate functions and fields allow the extraction of data from other tables and executing more complex validation tasks. For example, a user can determine if a measured value is stable (has not changed much over the last three readings) or can extract a value from a related form.

 

Snippets – This tab allows the user to save a formula to use in another field or on other forms, described in detail in the Formula Snippets article.

 

Test – This tab allows for testing various pieces of a formula by entering test values. Note that for complete testing of formulas, the template can be set to test mode and downloaded on the device.

 

Note: Fields with Default attribute values are required to be visible for the formula to properly function. For formula(s) to work with a hidden field(s), the field(s) must be a formula or pre-populated field.

 

A Search bar is available in the function, field, and aggregate field tabs. The Search bar allows users to quickly and easily search for specific functions and form fields.

 

 

Fields Tab Example

 

The fields shown in the Fields tab in the Formula Object Editor of the Formula Builder correspond to fields added to the form template, as designed on the Form Designer page. The fields are organized by the forms to which they belong. In this example, the BoringLog2 template has three parent forms: Project, Boring, and Test Pits. The Boring form is a parent form with six child forms.

 

fields_form_designer2

 

The image below shows the Boring form and its associated fields.

 

Correspondence template builder fields - form fields

 

The Fields tab displays the standard fields and section fields (i.e., system fields) that belong to the form currently being configured. In this example, the fields relate to the Boring form and its associated child forms.

 

Fields in Template Builder

 

While still in the Formula Builder, fields from the current form and all other forms in the template can be accessed by selecting the Aggregate Fields tab.

 

formula_builder_aggregate

 

formula_builder_aggregate2

 

 

How to Build a Formula

 

A new formula can be created by typing directly into the Formula Text Editor pane or can be built by clicking on the + button in the Formula Tree View to add a formula component from the Formula Object Editor pane. Choose what type of formula to build by selecting the desired component tab in the Formula Object Editor pane (e.g., Functions).

 

The Search String box in the Formula Object Editor pane can be used to filter the functions or fields to easily find the desired item. Search help is also provided as the user types in the Formula Text Editor pane. Possible matches appear for selection with a brief description to understand the function.

 

Col-Formula_Builder_Function_Help

 

The Add Outer Function and Add Parameter Before buttons can be used to help construct the formula in the Formula Tree View. As formula components are added, they appear in both the tree view and the text editor pane.

 

To delete a component, either (1) click the Col-Formula_Builder_Remove_Component symbol in the upper right corner of the component in the tree view or (2) delete the text in the text editor pane and hit the Enter key.

 

The image below illustrates a constructed formula for the sort header of a Log Data form.

 

Col-Formula_Builder_Example

 

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. Pressing the Enter key while in the formula text editor runs the parser.

Evaluating – Process of using the parse tree objects to return a specific value based on the values of the field objects in that tree. The Test tab in the Formula Object Editor pane allows for evaluating various pieces of a formula by entering test values.

 

 

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. Single quotes will be applied automatically if a string value is entered directly into the value tab of the formula builder.

Fields – Can be any field name enclosed in square brackets with or without a table name prefix (e.g., [table.field]). Square brackets will be applied automatically if a field is selected directly from fields tab of the formula builder. 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).