<< Click to Display Table of Contents >> Navigation:  Collect > Collect Enterprise > Template Designer Page > Formula Builder > Functions > Overview of Functions in EQuIS

Functions can be used in numerous ways to manipulate or transform data, and many functions can be used in combination with others to generate simple or complex formulas. This chapter describes in detail the different functions available in EQuIS Collect and EQuIS Link.

 

Functions are designated as either Standard or Aggregate, which are defined as:

•Standard Functions process data within specified parameters from the current row within the data source.

•Aggregate Functions process data of the specified parameters from all possible combinations of rows, including the current row, in all used tables within the formula. Since aggregate functions iterate over all rows in the tables used, which can require much processing and adversely impact performance, it is recommended that they be used only when absolutely necessary. If a formula contains an aggregate function(s) and multiple tables, each additional table included in the formula has the effect of multiplying the records for each table by the records of each additional table. To maximize the performance of aggregate functions, it is recommended to minimize the number of iterations being calculated by chaining multiple aggregate functions together with each one calculating one table or data set. The chain of aggregate functions can be contained within a non-aggregate function that will calculate the results of each aggregate function. Encapsulating aggregate functions within a non-aggregate function allows the function iterations to be added instead of multiplied, resulting in far fewer calculations. Below is an example showing the recommended approach to maximizing aggregate function performance.

 

Example Aggregate Function Formula (Recommended):

MAXVAL(MAX(FILTER(EQ([#id],[Lithology.#parent_#id]),[Lithology.bottom])),MAX(FILTER(EQ([#id],[Well construction.#parent_#id]),[Well construction.bot_depth])),MAX(FILTER(EQ([#id],[Sample.#parent_#id]),[Sample.sample_end_depth])),MAX(FILTER(EQ([#id],[Well Pipe.#parent_#id]),[Well Pipe.bot_depth])))

This formula shows a function expression that has a non-aggregate function containing multiple chained aggregate functions. Each aggregate function is run independently and MAXVAL, the non-aggregate equivalent of MAX, adds the iterations of each aggregate function.

 

Example Aggregate Function Formula (Not Recommended):

MAX(FILTER(EQ([#id],[Lithology.#parent_#id]),[Lithology.bottom]),FILTER(EQ([#id],[Well construction.#parent_#id]),[Well construction.bot_depth]),FILTER(EQ([#id],[Sample.#parent_#id]),[Sample.sample_end_depth]),FILTER(EQ([#id],[Well Pipe.#parent_#id]),[Well Pipe.bot_depth]))

This formula shows a function expression with multiple aggregate functions chained together, which requires more calculations to be performed. MAX is an aggregate function that multiplies the iterations of each aggregate function, so each aggregate function is run against all of the other aggregate functions.

 

 

Types of Functions

 

The functions are grouped by their type and include:

•Boolean – These functions are used to evaluate and return one of two possible values, denoted as True and False.

•Conditional – Conditional functions evaluate a given condition, or set of conditions, and determine if the specified parameters meet the conditions and return a Boolean response of True or False. These functions work with all data types, unless otherwise noted.

•Conversion – Conversion functions involve the process of changing a value from one data type to another data type. The functions are only able to compute integer and decimal data.

•JSON – These functions are used to evaluate values configured in a JSON object and/or JSON array.

•Math – The math data type includes trigonometric functions, logarithmic functions, and other common mathematical functions. Math functions perform mathematical calculations and are only able to compute integer and decimal data.

•String – A string data type is comprised of a set of characters that can also contain spaces and numbers. These functions are only able to compute string field data.

•Time – Time functions allow for control of temporal parameters.

•Other – The other group contains additional unique functions.

 

 

For each function, the following elements are provided:

•Function name

•A description of the function, including possible return values

•Syntax to use in the formula builder (shown in italics)

•Delineation as a standard or aggregate function

•Example(s)

 

 

 

Alphabetical Listing