Conditional Functions

<< Click to Display Table of Contents >>

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

Conditional Functions

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

FieldA

FieldB

12

EarthSoft

 

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

FieldA

FieldB

FieldC

12

Demo

12

 

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

FieldA

FieldB

FieldC

12

EarthSoft

9.67

 

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

FieldA

FieldB

7

EarthSoft

12

2019

Yes

Demo

 

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

#id (Parent Form)

Location (Form Records)

End Parameter Time

1023

Well ID 001

*see results below

1038

Well ID 002

*see results below

 

ChildForm

#parent_#id

#id (Child Form)

Date

pH

Temp

Turb

1023

1

2019/05/20 08:45:49

9.34

9.86

119

1023

2

2019/05/20 08:48:26

7.02

11.03

106

1023

3

2019/05/20 08:52:04

7.10

11.10

97

1023

4

2019/05/20 08:54:57

7.20

11.19

102

1038

5

2019/05/22 10:24:40

8.85

12.09

128

1038

6

2019/05/22 10:26:59

7.07

10.81

103

1038

7

2019/05/22 10:29:17

7.13

10.92

109

1038

8

2019/05/22 10:33:25

7.19

11.00

101

 

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

FieldA

FieldB

FieldC

9.4

9.4

3.7

 

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

FieldA

FieldB

FieldC

9.4

9.4

3.7

 

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

FieldA

FieldB

FieldC

FieldD

FieldE

9.4

9.4

3.7

EarthSoft

Demo

 

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 return TRUE, which corresponds to the formula then returning the FieldD value of “EarthSoft”.

 

Example 4

FieldA

FieldB

FieldC

FieldD

FieldE

9.4

9.4

3.7

EarthSoft

Demo

 

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 returns FALSE, which corresponds to the formula then returning the FieldE 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

FieldA

Lat=42.3149,Lon=83.0364

 

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

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

 

Example 2

FieldA

FieldB

FieldC

A=1,B=2

C=3,E=5,F=6

D=4,E=5,G=7

 

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

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

 

Example 3

FieldA

A-1,B-2

 

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

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