Table of Contents
Introduction
OpenGround contains powerful expression engines that can be used to perform calculations using your data.
Expressions are editable formulas that utilize prebuilt functions. They can reference Groups and Headers to manipulate data stored within your OpenGround database.
Expressions can be used in a variety of ways, such as to:
- Format and manipulate data on log and other report templates
- Automatically calculate values stored within the database
- Calculate values and perform validations within the Data Collector mobile field application
- Manipulate data during import/export
This guide provides an overview of expressions within OpenGround.
Please also see the Functions Guide for additional information on the functions that can be used when writing expressions.
We also have articles that are intended to supplement this guide with additional information relevant to each of the applications where expressions can be used (please see Expression Engines below), including:
Expression Language
OpenGround utilizes its own proprietary expression language. Many of the included functions are similar to those you would find in Excel formulas, gINT expressions, or other programming languages, but function syntax/usage and the general rules for writing functions may differ from other languages.
Expression Syntax
Overview
The example below illustrates the typical syntax for an OpenGround expression:
fixed([LocationDetails.FinalDepth],2,false)fixed() is a function that can be used to format a value to a specified number of decimal places.
The fixed() function accepts 3 “arguments”, which are the 3 values separated by commas within the parentheses:
- The first argument is the value to be formatted. In this case it is the value from the FinalDepth Header (field) in the LocationDetails Group (table) in the OpenGround database.
- The second argument is the number of decimal places, in this case 2.
- The third argument is a true/false value to indicate if thousand separators should be used for the formatted value, in this case set to false so the separators are not used.
Case-Sensitivity
Some function names are case-sensitive and some are not. It's best to follow the capitalization in our Functions Guide to avoid potential errors.
Group and Header references are always case-sensitive. For example, in the example above, errors would result if the Group.Header reference was [LOCATIONDETAILS.FINALDEPTH].
Group and Header References
Group and Header references utilize ‘database names’ rather than ‘display names’.
You can find the ‘database name’ by going to System>Configuration Packs>Manage Model.
The Group database name is the ‘Name’:
The Header database name is the ‘Column Name’:
If you are working within Template Studio, most objects will include dropdowns for the Group and Header. The database names are the names in parentheses:
The specific syntax for referencing a Group/Header will depend on the Expression Engine (see below) being used.
In Templates and other engines, references are commonly made using this syntax:
[Group.Header]In some other engines and scenarios, other syntaxes are used. Please see the relevant links below for more information pertaining to each of the Expression Engines.
Expression Engines
Depending on where your expression is being executed within OpenGround, a specific “Expression Engine” will be used. In general, expressions can be used similarly within different expression engines. However, there may be some differences between engines including:
- The syntax when referencing Groups and Headers may be slightly different.
- Certain functions may only be supported in specific engines or usage might be slightly different (please see our Functions Guide for more information).
- Expressions may also have different character limits.
- Aggregation may be performed differently.
Please see the links below for more information pertaining to each engine.
The engines currently used within OpenGround include:
- Templates Engine – Used to execute the expressions in log and other report templates within Template Studio.
- Model Calculation Engine – Used to execute the expressions defined for calculated headers within OpenGround Professional.
- Data Collector Engine – Used to execute expressions within the Data Collector mobile field application.
- Import Mapping Engine – Used to execute expressions within Import/Export Mappings.
Nesting Functions/Expressions
It is possible to “nest” functions/expressions within other functions to build more complex expressions.
For example:
text(fixed([LocationDetails.FinalDepth],2,false))+’ m’The expression above formats the FinalDepth in LocationDetails to 2 decimal places using the fixed() function. It then uses the text() function to convert that value to text so that a ‘ m’ units label can be appended to the end (a numeric value must be converted to text before it can be concatenated with another text value – see Text Formatting for more information).
The expression below builds on the example above to include additional levels of nesting:
if(
isblank([LocationDetails.FinalDepth]),
'',
text(fixed([LocationDetails.FinalDepth],2,false))+' m '
)This version of the expression uses an if() function to decide whether or not to print the FinalDepth:
- The first argument of the if() function is a condition that uses the isblank() function to check if a FinalDepth has been entered for the Location.
- The second argument of the if() function is the value that is returned if the condition is true – in this case if the FinalDepth is blank. In which case, the expression would return (print) nothing, represented by the empty text string (‘’).
- The third argument of the if() function is the value that is returned if the condition is false – in this case if the FinalDepth is not blank. In which case, the expression would return the FinalDepth formatted to 2 decimal places with the ‘ m’ units label.
Using the if() and isblank() functions to check for blank values prevents printing a ‘ m’ label with no numerical value attached to it in the event a FinalDepth has not been entered for that Location.
Text Formatting & Manipulation
A very common use of expressions is to format and manipulate text. Please see this article for more information.
Referencing Foreign Groups & Aggregation
Most expressions within OpenGround are evaluated for a specific Group by referencing Headers from within that Group. In some circumstances it is possible to reference Headers from other Groups as well, such as from parent or child groups. Please see this article for more information.
Referencing child groups typically requires the use of special “aggregate functions” to collate the set of multiple child values into a single value. Aggregation is performed slightly differently in Template Studio compared to Model Calculations. Please see our Aggregation in Templates and our Aggregation in Model Calculation guides for more information.
Other Topics
Using Picklist Descriptions in Expressions
Writing Expressions
We recommend writing expressions in an external text editor, such as Notepad++. Expressions can then be pasted into OpenGround.
Some expression fields within OpenGround accept multiple-line expressions. Others require expressions to be formatted to a single line.
For multiple-line expressions, a line continuation character is not needed at the end of each line.
Example Expressions
There are a number of places to find example expressions including:
- Our Functions Guide includes examples of how to use certain functions (additional examples coming in the future).
- Our standard Configuration Packs include many example expressions. You can often find an expression that is similar to what you want to achieve and modify it for your purposes.
- Template Studio includes an Expression Library with example expressions.
- Example expressions are included elsewhere in our documentation to address specific purposes, such as:
- If there's a specific expression you need help with, ask in our forum or contact support.