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

•Input Parameters Data Conversion

•ALLIN

•AND

•BETWEEN

•EQ

•FALSE

•FIELDEXISTS

•GE

•GT

•IN

•ISEMPTY

•ISNULL

•ISNUMERIC

•LE

•LT

•NOT

•OR

•TRUE

 

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 in Collect Mobile or in several field-attributes that control the way a field behaves in Collect Mobile. 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 or data mappings in a Collect form or an EQuIS Link Project, unless otherwise noted.

 

Input Parameters Data Conversion

 

Some functions can convert data types on the parameters passed to them. These functions 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.

 

The list is as follows:

 

•Object

•Boolean

•Integer

•Decimal

•Date/Time

•String

 

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. The function 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

 

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

 

Example 2

 

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

 

Example 3

 

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

 

Example 4

 

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

AND

Function AND returns TRUE if all parameter 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

 

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

 

Example 2

 

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

BETWEEN

The BETWEEN function returns TRUE if the third parameter is between the first two parameters (greater than the first parameter and less than the second parameter). BETWEEN can compare only numeric values and unformatted date/time values. Returns FALSE if the third parameter's value is outside the range of the first and second parameter values or is not numeric or date/time, or if any of the parameters are NULL.

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

Aggregate = No

 

Example 1

 

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

 

Example 2

 

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

 

Example 3

 

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

 

Example 4

 

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

EQ

The EQ function returns TRUE if all parameters are equal. This function converts data types on its parameters, as explained in Input Parameters Data Conversion. The function returns NULL if any of the parameters are NULL.

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

Aggregate = No

 

Example 1

 

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

 

Example 2

 

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

 

Example 3

Where FieldA is of type Integer and FieldB is of type String.

 

EQ([FieldA],[FieldB]) = EQ('5','005') = FALSE

 

This example demonstrates different field types and data conversions can alter how the EQ function evaluates. The string value in FieldB is converted to an integer value of “5”, which is then compared to the integer value in FieldA

FALSE

The FALSE function always returns FALSE. The function can be used in Boolean field Attributes, such as VISIBLE, to determine how a field behaves in Collect forms.

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

Aggregate = No

 

Example 1

 

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

 

Example 2

To make FieldA always be invisible in the data form, the Visible attribute formula would be 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:

FIELDEXISTS("Table.FieldB") = TRUE

FIELDEXISTS("Table.FieldD") = FALSE

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 converts data types on its parameters, as explained in Input Parameters Data Conversion. The function Returns NULL if any of the parameters are NULL.

GE(<Parameter_1>,<Parameter_2>)

Aggregate = No

 

Example 1

 

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

 

Example 2

 

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

 

Example 3

 

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

 

The following examples show how the data types on values can affect the evaluation. If any parameters are found to be strings (i.e., coming from a String field type), then all values are considered strings.

 

Example 4

 

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

 

Example 5

 

GE([FieldD],[FieldE]) = TRUE. A string starting with the characters “-6” sorts after a string starting with “-5”, so FieldD is greater than or equal to FieldE.

GT

The GT function returns TRUE if the first parameter is greater than the second parameter or FALSE if not. This function converts data types on its parameters, as explained in Input Parameters Data Conversion. The function returns NULL if any of the parameters are NULL.

GT(<Parameter_1>,<Parameter_2>)

Aggregate = No

 

Example 1

 

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

 

Example 2

 

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

 

Example 3

 

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

 

The following examples show how the data types on values can affect the evaluation. If any parameters are found to be strings (i.e. coming from a String field type), then all values are considered strings.

 

Example 4

 

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

 

Example 5

 

GT([FieldD],[FieldE]) = TRUE. A string starting with the characters “-6” sorts after a string starting with “-5”, so FieldE is greater than FieldD.

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 lists in any of the following parameters. The function 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

 

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

 

Example 2

 

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

 

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

 

Example 2

 

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

ISNULL

The ISNULL function returns TRUE if any of the parameters are NULL and returns False if none of them are NULL or contain only spaces or tabs (white space).

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

Aggregate = No

 

Example 1

 

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

 

Example 2

 

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

 

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

 

ISNUMERIC([FieldA]) = TRUE

 

Example 2

 

ISNUMERIC([FieldA]) = TRUE

 

Example 3

 

ISNUMERIC([FieldA]) = FALSE

 

Example 4

 

ISNUMERIC([FieldA]) = FALSE

LE

The LE function returns TRUE if the first parameter is less than or equal to the second parameter and FALSE if not. This function converts data types on its parameters, as explained in Input Parameters Data Conversion. The function returns NULL if any of the parameters are null.

 

LE(<Parameter_1>,<Parameter_2>)

Aggregate = No

 

Example 1

 

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

 

Example 2

 

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

 

Example 3

 

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

 

The following examples show how the data types on values can affect the evaluation. If any parameters are found to be strings (i.e. coming from a String field type), then all values are considered strings.

 

Example 4

 

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

 

Example 5

 

LE([FieldD],[FieldE]) = FALSE. A string starting with the characters “-6” sorts after a string starting with “-5”, so FieldD is not less than or equal to FieldE.

LT

The LT function returns TRUE if the first parameter is less than the second parameter or FALSE if not. This function converts data types on its parameters, as explained in Input Parameters Data Conversion. The function returns NULL if any of the parameters are null.

LT(<Parameter_1>,<Parameter_2>)

Aggregate = No

 

Example 1

 

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

 

Example 2

 

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

 

Example 3

 

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

 

The following examples show how the data types on values can affect the evaluation. If any parameters are found to be strings (i.e. coming from a String field type), then all values are considered strings.

 

Example 4

 

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

 

Example 5

 

LT([FieldD],[FieldE]) = FALSE. A string starting with the characters “-6” sorts after a string starting with “-5”, so FieldD is not less than FieldE.

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

 

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

 

Example 2

 

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

OR

The OR function returns TRUE if any of the parameters evaluate to TRUE. The function 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

 

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

 

Example 2

 

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

 

Example 3

 

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

 

Example 4

 

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

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

TRUE

The TRUE function always returns TRUE. The function can be used in Boolean field Attributes, such as

REQUIRED, to determine how a field behaves in Collect forms.

TRUE()

Aggregate = No

 

Example 1

 

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