﻿ Boolean Functions

# Boolean Functions

The Boolean functions are used to evaluate and return one of two possible values—True or False. Within EQuIS Collect, the functions can be used to evaluate data as they are entered in forms on the Mobile app. The Boolean functions can also be used to show or hide specified fields 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 Boolean function and its operations. The examples are applicable to either fields in a Collect form or an EQuIS Link data source, unless otherwise noted.

ALLIN

The ALLIN function returns TRUE if all the comma delimited values in the first parameter are found in the combined lists of comma delimited values of the subsequent parameters. Returns NULL if any of the parameters are NULL before all values are found, TRUE if all values are found, or FALSE otherwise.

ALLIN(<Parameter_1>,<Parameter_2…<Parameter_N>)

Aggregate = No

Example 1

FieldA

FieldB

FieldC

FieldD

FieldE

EarthSoft

1996

Location

EarthSoft

Sample02

ALLIN([FieldA],[FieldB],[FieldC],[FieldD],[FieldE]) = TRUE

Example 2

FieldA

FieldB

FieldC

FieldD

FieldE

EarthSoft

1996

Location

EarthSoft-2018

Sample02

ALLIN([FieldA],[FieldB],[FieldC],[FieldD],[FieldE]) = FALSE

AND

Function AND returns TRUE if all parameters values evaluate to true, NULL if any of the parameters are NULL or cannot be interpreted as Boolean before encountering a FALSE value, and FALSE if any of the parameter values are FALSE.

AND(<Parameter_1>,<Parameter_2>…<Parameter_N>)

Aggregate = No

Example 1

FieldA

FieldB

FieldC

FieldD

6

0.51

0.62

0.87

AND(EQ([FieldA],6),LT([FieldB],[FieldC])) = TRUE

Example 2

FieldA

FieldB

FieldC

FieldD

6

0.51

0.62

0.87

AND(EQ([FieldA],6),LT([FieldD],[FieldC])) = FALSE

Example 3

FieldA

FieldB

FieldC

FieldD

6

0.51

0.62

0.87

AND(EQ([FieldA],[FieldD])) = FALSE

BETWEEN

The BETWEEN function returns TRUE if the third parameter is between the first two parameters. Returns FALSE if any of the parameters are NULL or if the third parameters value is not between the first and second parameter values.

BETWEEN(<Parameter_1>,<Parameter_v2>,{<Parameter_3>})

Aggregate = No

Example 1

FieldA

FieldB

FieldC

FieldD

9.76

3.20

4.17

1.09

BETWEEN([FieldB],[FieldA],[FieldC]) = TRUE

Example 2

FieldA

FieldB

FieldC

FieldD

9.76

3.20

4.17

1.09

BETWEEN([FieldB],[FieldA],[FieldD]) = FALSE

Example 3

FieldA

FieldB

FieldC

FieldD

2018/02/20 11:31:45

2018/02/22 10:43:28

2018/02/21 14:37:08

2018/02/23 09:37:08

BETWEEN([FieldA],[FieldB],[FieldC]) = TRUE

Example 4

FieldA

FieldB

FieldC

FieldD

2018/02/20 11:31:45

2018/02/22 10:43:28

2018/02/21 14:37:08

2018/02/23 09:37:08

BETWEEN([FieldA],[FieldB],[FieldD]) = FALSE

EQ

The EQ function returns TRUE if all parameters are equal. This function will first check if the values are all of the same type, and if yes, use that type. If the values are not of the same type, the function will cycle through a list of data types to find a common data type to which all parameters can be converted. The function then converts the parameters' data types and compares them. Returns NULL if any of the parameters are NULL.

EQ(<Parameter_1>,<Parameter_2>…<Parameter_N>)

Aggregate = No

Example 1

FieldA

FieldB

FieldC

5

5

8

EQ([FieldA],[FieldB]) = TRUE

Example 2

FieldA

FieldB

FieldC

5

5

8

EQ([FieldA],[FieldB],[FieldC]) = FALSE

Example 3

This example demonstrates how data type conversions can alter how the EQ function evaluates parameters. The string in FieldB is converted to an integer value of "5", which is then compared to the integer value in FieldA.

FieldA

FieldB

FieldC

5

005

8

EQ([FieldA],[FieldB]) = EQ(5,5) = TRUE

FALSE

The FALSE function always returns FALSE. The function can be used to hide a specified field on Collect forms.

FALSE(<Parameter_1>,<Parameter_2>…<Parameter_N>)

Aggregate = No

Example 1

FieldA

FieldB

FieldC

5

1

8

FALSE([FieldA],[FieldB],[FieldC]) = FALSE

FIELDEXISTS

The FIELDEXISTS function returns TRUE or FALSE depending on if a field exists in the Source Data table or Reference Data table. The only parameter is a string representing the table and field (e.g., "table.field").

FIELDEXISTS(<StringParameter_1>)

Aggregate = No

Example: A source data table, called "Table", has the following fields:

FieldA

FieldB

FieldC

1

2

3

FIELDEXISTS("Table.FieldB") = TRUE

FIELDEXISTS("Table.FieldD") = FALSE

 Note: The FIELDEXISTS function is used only in EQuIS Link; it is not displayed in the Collect Formula Builder. It is most useful as part of a mapping's Active attribute. It can improve processing time in Link 7.23.3 by making Link skip an entire mapping if one or more of the critical fields are not present in the source file.

If a mapping has a global Active formula of FIELDEXISTS("Table.FieldD"), and if the example source file does not contain a Table.FieldD field, FIELDEXISTS("Table.FieldD") = FALSE and the entire mapping will be skipped.

GE

The GE function returns TRUE if the first parameter is greater than or equal to the second parameter or FALSE if not. This function will first check if the values are of the same type, and if yes, use that type. If the values are not of the same type, the function will cycle through a list of data types to find a common data type to which the parameters can be converted. The function then converts the parameters' data types and compares them. Returns NULL if any of the parameters are NULL.

GE(<Parameter_1>,<Parameter_2>)

Aggregate = No

Example 1

FieldA

FieldB

FieldC

FieldD

FieldE

5.23

7.84

7.84

6.65

5.23

GE([FieldA],[FieldE]) = TRUE

Example 2

FieldA

FieldB

FieldC

FieldD

FieldE

5.23

7.84

7.84

6.65

5.23

GE([FieldC],[FieldD]) = TRUE

Example 3

FieldA

FieldB

FieldC

FieldD

FieldE

5.23

7.84

7.84

6.65

5.23

GE([FieldE],[FieldB]) = FALSE

GT

The GT function returns TRUE if the first parameter is greater than the second parameter or FALSE if not. This function will first check if the values are of the same type, and if yes, use that type. If the values are not of the same type, the function will cycle through a list of data types to find a common data type to which the parameters can be converted. The function then converts the parameters' data types and compares them. Returns NULL if any of the parameters are NULL.

GT(<Parameter_1>,<Parameter_2>)

Aggregate = No

Example 1

FieldA

FieldB

FieldC

FieldD

FieldE

5.23

7.84

7.84

6.65

2.67

GT([FieldB],[FieldA]) = TRUE

Example 2

FieldA

FieldB

FieldC

FieldD

FieldE

5.23

7.84

7.84

6.65

2.67

GT([FieldB],[FieldC]) = FALSE

Example 3

FieldA

FieldB

FieldC

FieldD

FieldE

5.23

7.84

7.84

6.65

2.67

GT([FieldE],[FieldD]) = FALSE

IN

The IN function returns TRUE if any of the comma-delimited words in the first parameter are found in any of the comma-delimited words in any of the following parameters. Returns NULL if the first parameter is NULL or if a NULL value is encountered before finding a match, and FALSE if no match or NULL values are found.

IN(<Parameter_1>,{<Parameter_2>…<Parameter_N>})

Aggregate = No

Example 1

FieldA

FieldB

FieldC

FieldD

EF,MR

AH

MR,FFA

EFA

IN([FieldA],[FieldB],[FieldC],[FieldD) = TRUE

Example 2

FieldA

FieldB

FieldC

FieldD

EF

AH

MR

GW

IN([FieldA],[FieldB],[FieldC],[FieldD) = FALSE

ISEMPTY

The ISEMPTY function returns TRUE if one of the parameters is NULL, empty, or contains only spaces or tabs (white space) and returns FALSE otherwise.

ISEMPTY(<StringParameter_1>,<StringParameter_2>…<StringParameter_N>)

Aggregate = No

Example 1

FieldA

FieldB

FieldC

EarthSoft

20190415

ISEMPTY([FieldA],[FieldB],[FieldC]) = TRUE

Example 2

FieldA

FieldB

FieldC

EarthSoft

B-30.417

20190415

ISEMPTY([FieldA],[FieldB],[FieldC]) = FALSE

ISNULL

The ISNULL function returns TRUE if any of the parameters are NULL or empty, otherwise it returns FALSE.

ISNULL(<Parameter_1>,<Parameter_2>…<Parameter_N>)

Aggregate = No

Example 1

FieldA

FieldB

FieldC

FieldD

6

10

3

ISNULL([FieldA],[fieldB],[FieldC],[FieldD) = TRUE

Example 2

FieldA

FieldB

FieldC

FieldD

6

10

3

ISNULL([FieldA],[FieldB],[FieldD]) = FALSE

 Note: A parameter is not empty if the parameter contains spaces or tabs (white space).

ISNUMERIC

The ISNUMERIC function returns TRUE if the parameter is not NULL and is convertible to a numeric value.

ISNUMERIC(<Parameter_1>)

Aggregate = No

Example 1

FieldA

20190415

ISNUMERIC([FieldA]) = TRUE

Example 2

FieldA

2019.0415

ISNUMERIC([FieldA]) = TRUE

Example 3

FieldA

EarthSoft

ISNUMERIC([FieldA]) = FALSE

Example 4

FieldA

20.1904.15

ISNUMERIC([FieldA]) = FALSE

LE

The LE function returns TRUE if the first parameter is less than or equal to the second parameter or FALSE if not. This function will first check if the values are of the same type, and if yes, use that type. If the values are not of the same type, the function will cycle through a list of data types to find a common data type to which the parameters can be converted. The function then converts the parameters' data types and compares them. Returns NULL if any of the parameters are null.

LE(<Parameter_1>,<Parameter_2>)

Aggregate = No

Example 1

FieldA

FieldB

FieldC

7.8

7.8

5.2

LE([FieldC],[FieldB]) = TRUE

Example 2

FieldA

FieldB

FieldC

7.8

7.8

5.2

LE([FieldA],[FieldB]) = TRUE

Example 3

FieldA

FieldB

FieldC

7.8

7.8

5.2

LE([FieldA],[FieldC]) = FALSE

LT

The LT function returns TRUE if the first parameter is less than the second parameter or FALSE if not. This function will first check if the values are of the same type, and if yes, use that type. If the values are not of the same type, the function will cycle through a list of data types to find a common data type to which the parameters can be converted. The function then converts the parameters' data types and compares them. Returns NULL if any of the parameters are null.

LT(<Parameter_1>,<Parameter_2>)

Aggregate = No

Example 1

FieldA

FieldB

FieldC

7.8

7.8

5.2

LT([FieldC],[FieldA]) = TRUE

Example 2

FieldA

FieldB

FieldC

7.8

7.8

5.2

LT([FieldA],[FieldB]) = FALSE

Example 3

FieldA

FieldB

FieldC

7.8

7.8

5.2

LT([FieldB],[FieldC]) = FALSE

NOT

The NOT function returns the negation of a specified parameter. If the parameter is NULL or cannot be interpreted as a Boolean, then the function returns NULL.

NOT(<Parameter_1>)

Aggregate = No

Example 1

FieldA

FieldB

FieldC

1

5

5

NOT(EQ([FieldA],[FieldB])) = TRUE

Example 2

FieldA

FieldB

FieldC

1

5

5

NOT(EQ([FieldB],[FieldC])) = FALSE

OR

The OR function returns TRUE if any of the parameters evaluate to TRUE. Returns NULL if a NULL value or value that cannot be interpreted as a Boolean is encountered before a TRUE value parameter.

OR(<Parameter_1>,<Parameter_2>…<Parameter_N>)

Aggregate = No

Example 1

FieldA

FieldB

FieldC

FieldD

6

0.5

0.6

0.8

OR(EQ([FieldA],3),LT([FieldB],[FieldD])) = TRUE

Example 2

FieldA

FieldB

FieldC

FieldD

6

0.5

0.6

0.8

OR(EQ([FieldA],6),LT([FieldC],[FieldB])) = TRUE

Example 3

FieldA

FieldB

FieldC

FieldD

6

0.5

0.6

0.8

OR(EQ([FieldA],3),LT(FieldD],[FieldC])) = FALSE

Example 4

FieldA

FieldB

FieldC

FieldD

FALSE

FALSE

TRUE

OR([FieldA],[FieldB],[FieldC],[FieldD])=NULL

OR([FieldA],[FieldB],[FieldD])=TRUE

TRUE

The TRUE function always returns TRUE. The function can be used to force a field to be required and is typically used in the Collect templates to make fields required.

TRUE()

Aggregate = No

Example 1 – Collect Form1

FieldA

To make FieldA always be required, the REQUIRED attribute formula would be TRUE().