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

•FILTER

•IF

•PARAM

 

Conditional functions evaluate a given condition, or set of conditions, and determine if the specified parameters meet the conditions and then determine what value should be returned. These functions work with all data types unless otherwise noted.

 

Within EQuIS Collect, the functions perform calculations as data are entered in forms on the Mobile app. Within EQuIS Link, the functions provide a means to transform data when loading to a selected format.  

 

Below is a descriptive list of each function and its operations. The examples are applicable to either fields in a Collect form or an EQuIS Link data source, unless otherwise noted.

FILTER

The FILTER function evaluates the first parameter and if it evaluates to TRUE, then the function returns the value of the second parameter. Otherwise, the function returns NULL.

FILTER(<Parameter_1>,<Parameter_2>)

Aggregate = No

 

Example 1

 

FILTER(EQ([FieldA],12),[FieldB]) = EarthSoft

This formula begins by comparing the parameter values of FieldA with a constant parameter value of “12” and determines if they are equal. In this case, FieldA and the constant parameter value of “12” are equal, and return TRUE, which leads to the formula then returning the FieldB parameter value for the corresponding row, “EarthSoft”.

 

Example 2

 

FILTER(EQ([FieldA],[FieldC]),[FieldB]) = Demo

This formula begins by comparing the parameter values of FieldA and FieldC and determines if they are equal. In this case, FieldA and FieldC are equal, and return TRUE, which leads to the formula then returning the FieldB parameter value for the corresponding row, “Demo”.

 

Example 3

 

FILTER(EQ([FieldA],[FieldC]),[FieldB]) = NULL

This formula begins by comparing the parameter values of FieldA and FieldC and determines if they are equal. In this case, FieldA and FieldC are not equal, and return FALSE, which leads to the formula then returning NULL.

 

Example 4

 

FILTER(EQ([FieldA],’Yes’),[FieldB]) = Demo

This formula begins by comparing the parameter values of FieldA with a constant parameter value of “Yes” and determines if they are equal. In this case, FieldA and the constant parameter value of “Yes” are equal for the third row and return TRUE, which leads to the formula then returning the FieldB parameter value for the corresponding row, “Demo”. For the other rows, the formula returns NULL.

 

Example 5 – Use in Aggregate functions (with parent and child Collect forms)

FILTER can be placed inside of an Aggregate function to ensure it evaluates only on a group of form records. For Aggregate functions that have parameter to define a sorting field (e.g., TOP, BOTTOM, AUTONUM described in Other Functions, or all Stabilization Functions), the FILTER function should be placed on that sort parameter. FILTER with an Aggregate function can restrict the function to run only on sub-form records that belong to a single parent form in the Collect template. The following example will use the FILTER and BOTTOM functions to get the last time value from a set of sub-forms (found in records on ChildForm) and display it on the parent form (ParentForm).

 

ParentForm

 

ChildForm

 

For parent Well ID = Well ID 001:

BOTTOM(FILTER(EQ([#id],[ChildForm.#parent_#id]),[ChildForm.Date]),[ChildForm.Date]) = 2019/05/20 08:54:57

 

For parent Well ID = Well ID 002:

BOTTOM(FILTER(EQ([#id],[ChildForm.#parent_#id]),[ChildForm.Date]),[ChildForm.Date]) = 2019/05/22 10:33:25

IF

The IF function evaluates the first parameter and if it renders TRUE, then the function returns the value of the second parameter. Otherwise, the function returns the third parameter. If the first parameter is NULL, then the function returns NULL.

IF(<Parameter_1>,<Parameter_2>,<Parameter_3>)

Aggregate = No

 

Example 1

 

IF(EQ([FieldA],[FieldB]),’TRUE’,’FALSE’) = TRUE

This formula begins by comparing the parameter values of FieldA and FieldB and determines if they are equal. In this case, FieldA and FieldB are equal, and return TRUE, which corresponds to the formula then returning the constant parameter value of “TRUE”.

 

Example 2

 

IF(EQ([FieldA],[FieldC]),’TRUE’,’FALSE’) = FALSE

This formula begins by comparing the parameter values of FieldA and FieldC and determines if they are equal. In this case, FieldA and FieldC are not equal, and return FALSE, which corresponds to the formula then returning the constant parameter value of “FALSE”.

 

Example 3

 

IF(EQ([FieldA],[FieldB]),[FieldD],[FieldE]) = EarthSoft

This formula begins by comparing the parameter values of FieldA and FieldB and determines if they are equal. In this case, FieldA and FieldB are equal and the statement returns TRUE, which corresponds to a formula, FieldD, that has a value of “EarthSoft”.

 

Example 4

 

IF(EQ([FieldA],[FieldC]),[FieldD],[FieldE]) = Demo

This formula begins by comparing the parameter values of FieldA and FieldC and determines if they are equal. In this case, FieldA and FieldC are not equal and the statement returns FALSE, which corresponds to a formula, FieldE, that has a value of “Demo”.

 

PARAM

The PARAM function gets the first parameter value from a delimited string of the form p1=v1, where the name of the parameter is the second parameter passed. An optional third parameter can be used to specify the character delimiter between parameter value pairs (default is “,”). An optional fourth parameter can be used to specify the character between the parameter name and value (default is “=”).

PARAM(<StringParameter_1>,<StringParameter_2>,{<OptionalParameter_1>,<OptionalParameter_2>})

Aggregate = No

 

Example

 

PARAM([FieldA],'Lon') = 83.0364

This formula is extracting the value associated with “Lon” from the FieldA parameter.

 

Example 2

 

PARAM([FieldB],’E’) = 5

This formula is extracting the value associated with “E” from the FieldB parameter.

 

Example 3

 

PARAM([FieldA],’B’,’;‘,’-’) = 2

This formula is extracting the value associated with “B” from the FieldA parameter using custom delimiter values.