Other Functions

<< Click to Display Table of Contents >>

 >Collect > Collect Enterprise > Form Designer Page > Formula Builder > Functions >

Other Functions

The Other group contains additional unique functions. 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.  

 

Because of the complexity and uniqueness of the stabilization functions, they are described in a separate article.

 

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.

ADDDATE

The ADDDATE function adds the second timespan parameter value to the first date-time value and returns a new date-time. The added timespan parameter should be written with the syntax of dd:HH:mm:ss (days : hours : minutes : seconds).

ADDDATE(<DateTimeParameter _1>,<Parameter_2>)

Aggregate = No

 

Example 1

FieldA

FieldB

01/01/2019 14:30:45

00:04:05:00

 

ADDATE([FieldA],[FieldB]) = 2019/01/01 18:35:45

 

Example 2

FieldA

FieldB

01/01/2019 12:15:30

01:10:12:34

 

ADDATE([FieldA],[FieldB]) = 2019/01/02 22:28:04

 

Example 3

FieldA

FieldB

01/02/2019 08:25:12

00:06:18:26

 

ADDATE([FieldA],[FieldB]) = 2019/01/02 14:43:38

AGGAND

The AGGAND function returns TRUE if all non-null values are TRUE for all rows.

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

Aggregate = Yes

 

Example 1 – Collect Form1

FieldA

4

6

2

 

AGGAND(GE([form1.FieldA],2)) = TRUE

This formula is checking if all FieldA parameter values are greater or equal to 2. In this case, all FieldA parameter values are greater than or equal to 2 and the formula returns TRUE.

 

Example 2 – Collect Form1

FieldA

4

6

1

 

AGGAND(GE([form1.FieldA],2)) = FALSE

This formula is checking if all FieldA parameter values are greater or equal to 2. In this case, not all FieldA parameter values are greater than or equal to 2 and the formula returns FALSE.

 

Example 3 – Collect Form1

FieldA

FieldB

4

FALSE()

6

FALSE()

2

FALSE()

 

AGGAND(GE([form1.FieldA],2),[form1.FieldB]) = FALSE

This formula is checking if all FieldA parameter values are greater or equal to 2 and returning the FieldB value of FALSE(). In this case, all FieldA parameter values are greater than or equal to 2 but the FieldB parameter values are FALSE, and the formula then returns FALSE.

AGGOR

The AGGOR function returns TRUE if any non-null value(s) are TRUE for all rows.

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

Aggregate = Yes

 

Example 1 – Collect Form1

FieldA

4

6

2

 

AGGOR(GE([form1.FieldA],4)) = TRUE

This formula is checking if any FieldA parameter values are greater or equal to 4. In this case, two of the FieldA parameter values are greater than or equal to 4 and the formula returns TRUE.

 

Example 2 – Collect Form1

FieldA

3

1

2

 

AGGOR(GE([form1.FieldA],4)) = FALSE

This formula is checking if any FieldA parameter values are greater or equal to 4. In this case, none of the FieldA parameter values are greater than or equal to 4 and the formula returns FALSE.

AUTONUM

The AUTONUM function is designed to help auto-increment numbers based on the number of previous records. An example application of the function is on Collect forms with samples and the user wants the Sample IDs to auto-increment.

 

The AUTONUM function returns the number of current record using the first parameter as a sort criterion and the second parameter as an optional starting value. It compares the current record value against each value of all prior records, after sorting, and adds the number of prior records found to the starting record count.

 

AUTONUM(<Parameter_1>,{<OptionalNumericParameter_1>})

Aggregate = Yes

 

Example 1 – Collect Form1

FieldA

4

6

2

5

 

AUTONUM([form1.FieldA]) = 3

This formula is sorting the records, based on parameter values of form1.FieldA, and in this case using the last (current) record as the starting record. The formula returns a count of the current record and all prior records after sorting. In this case the formula returns the value 3, counting three form1.FieldA records with parameter values of 2, 4 and 5.

BOTTOM

The BOTTOM function sorts the records using the first parameter as the criteria, which then returns the value of the second parameter assessed on the last (highest value) row of the sorted first parameter.

BOTTOM(<Parameter_1>,<Parameter_2>)

Aggregate = Yes

 

Example – Collect Form1

FieldA

FieldB

4

A1

6

B2

2

C3

 

BOTTOM([form1.FieldA],[form1.FieldB]) = B2

This formula is sorting the records based on the FieldA parameter values and returning the FieldB parameter value ("B2") associated with the highest value record of FieldA ("6").

CASE

The CASE function examines parameter(s) sequentially. Once a parameter evaluates to TRUE, it returns a specified response. The CASE function works with both and even and odd numbered set of parameters.

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

Aggregate = No

 

Example 1

FieldA

FieldB

FieldC

EarthSoft

B-30.417

20190415

 

CASE(EQ([FieldA],’EarthSoft’),'EXTREME’,EQ([FieldB],’B-30.417’),’MODERATE’,EQ([FieldC],2019),’LOW’) = EXTREME

 

Example 2

FieldA

FieldB

FieldC

EarthSoft

B-30.417

20190415

 

CASE(EQ([FieldA],’Collect’),'EXTREME’,EQ([FieldB],’C3’),’MODERATE’,EQ([FieldC],20190415),’LOW’) = LOW

 

Example 3

FieldA

12

 

CASE(LT([FieldA],3),'Value for pH is strongly acidic.',GT([FieldA],11),'Value for pH is highly basic.') = Value for pH is highly basic.

CHAR

The CHAR function returns the character as a string from the character code value in UTF 8 (0-255) character set.

CHAR(<NumericParameter_1>)

Aggregate = No

Example 1

FieldA

77

 

CHAR([FieldA]) = M

 

Example 2

FieldA

100

 

CHAR([FieldA]) = d

 

Example 3

FieldA

24

 

CHAR([FieldA]) = ↑

 

Example 4

FieldA

198

 

CHAR([FieldA]) = Æ

CHECKSUM

The CHECKSUM function converts a string parameter to a highly probable unique integer, which can be used for detecting errors. Null strings return 0.

CHECKSUM(<StringParameter_1>)

Aggregate = No

 

Example 1

FieldA

EarthSoft

 

CHECKSUM([FieldA]) = -622742239

 

Example 2

FieldA

30B201904

 

CHECKSUM([FieldA]) = 1652253079

CLEAN

The CLEAN) function removes all control characters (less than number 32) from the string parameter. Optional second parameter defaults to TRUE and removes spaces. Optional third parameter defaults to TRUE and replaces accents with unaccented characters. Optional fourth parameter defaults to TRUE and forces uppercase.

CLEAN(<StringParameter_1>,{<OptionalParameter_1>,<OptionalParameter_2>,<OptionalParameter_3>})

Aggregate = No

 

Example 1

FieldA

Earth Moon

 

CLEAN([FieldA]) = EARTHMOON

 

Example 2

FieldA

Earth Moon

 

CLEAN([FieldA],FALSE()) = EARTH MOON

 

Example 3

FieldA

Earth Moon

 

CLEAN([FieldA],FALSE(),TRUE(),FALSE()) = Earth Moon

COUNT

The COUNT function counts all rows of a specified parameter. This function works with all parameter types and counts all records.

COUNT(<Parameter_1>)

Aggregate = Yes

 

Example

FieldA

EarthSoft

2019

Demo

 

COUNT(FieldA) = 3

COUNTROWS

The COUNTROWS function counts all the rows of specified parameters. This function works with all parameter types but will not count empty, null, or FALSE records.

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

Aggregate = Yes

 

Example 1 – Collect Form1

FieldA

FieldB

4

A1

6

B2

2

C3

 

COUNTROWS([form1.FieldA],[form1.FieldB]) = 6

 

Example 2 – Collect Form1

FieldA

FieldB

FieldC

4

A1


6

B2

EarthSoft

2

C3

Complete

 

COUNTROWS([form1.FieldA],[form1.FieldB],[form1.FieldC]) = 8

COUNTTRUE

The COUNTTRUE function counts the number of parameter values that evaluate to TRUE for all rows used.

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

Aggregate = Yes

 

Example 1 – Collect Form1

FieldA

FieldB

TRUE

FALSE

TRUE

TRUE

FALSE

FALSE

 

COUNTTRUE([form1.FieldA],[form1.FieldB]) = 3

DISTINCT

The DISTINCT function returns a list of unique comma-delimited codes from all rows in all parameters specified. The DISTINCT function does not count or include duplicate values or codes.

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

Aggregate = Yes

 

Example 1 – Collect Form1

FieldA

FieldB

FieldC

A

F

E

D

A

J

V

E

S

 

DISTINCT([form1.FieldA],[form1.FieldB]) = A, F, E, D, J, V, S

 

Example 2 – Collect Form1

FieldA

FieldB

FieldC

B-30

E-24

P-04

B-32

M-19

B-30

M-19

P-04

W-93

 

DISTINCT([form1.FieldA],[form1.FieldB]) = B-30, E-24, P-04, B-32, M-19, W-93

DIV

The DIV function calculates the integer division of the first integer parameter divided by the second integer parameter. If the values entered are decimal, the function will round to the nearest integer value and then divide the two values, returning an integer.

DIV(<NumericParameter_1>,<NumericParameter_2>)

Aggregate = No

 

Example 1

FieldA

FieldB

5

2

 

DIV([FieldA],[FieldB]) = 2

 

Example 2

FieldA

FieldB

6

2.9

 

DIV([FieldA],[FieldB]) = 2

 

Example 3

FieldA

FieldB

9

2.51

 

DIV([FieldA],[FieldB]) = 3

EXCEPTION

The EXCEPTION function throws a formula exception, which gets propagated outside of the parser. This is for testing application functionality and robustness when exceptions are accidentally thrown with functions.

EXCEPTION()

Aggregate = No

 

Example

EXCEPTION()

This formula throws an exception and simulates an error. This is principally used for testing and debugging purposes.

FORMATNUM

The FORMATNUM function formats the first decimal parameter using the optional second parameter as the format string. The third optional parameter is used as a minimum length. After formatting the number, the third optional parameter inserts empty spaces to the beginning for the decimal value to complete the minimum length value. By default the format is 0.000 and there is no minimum length.

FORMATNUM(<NumericParameter_1>,{<OptionalParameter_1>,<OptionalNumericParameter_1>})

Aggregate = No

 

Example 1

FieldA

1.24

 

FORMATNUM([FieldA],000.00) = 001.24

This formula is formatting the FieldA decimal value to string sequence given in the second parameter.

 

Example 2

FieldA

12.08

 

FORMATNUM([FieldA],000.000,10) =    012.080

This formula is formatting the FieldA decimal value to string sequence given in the second parameter and adding 3 empty spaces to beginning of the decimal value to equal a total of 10 characters (3 empty spaces + 7 characters).

FROMOADATE

The FROMOADATE function converts a date-time decimal value to a date-time value, with a starting reference point (base OLE Automation Date) of midnight on December 30, 1899.

FROMOADATE(<DateTimeParameter_1>)

Aggregate = No

 

Example 1

FieldA

43466

 

FROMOADATE([FieldA]) = 2019/01/01

 

Example 2

FieldA

43467.6046875

 

FROMOADATE([FieldA]) = 2019/01/02 14:30:45

 

Example 3

FieldA

43570.648506944446

 

FROMOADATE([FieldA]) = 2019/04/15 15:33:51

GET

The GET function returns the value of the second parameter when the first parameter evaluates to TRUE.

GET(<Parameter_1>,<Parameter_2>)

Aggregate = Yes

 

Example – Collect Form1

FieldA

FieldB

4

003B-2019

6

014C-2019

2

025D-2019

 

GET(EQ([form1.FieldA],6),[form1.FieldB]) = 014C-2019

ISEMPTY

The ISEMPTY function returns TRUE if one of the parameters is null, empty, or contains only whitespace.

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

ISNUMERIC

The ISNUMERIC function returns TRUE if the parameter is not null and 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

JSON

The JSON function returns the value of a property inside of a JSON (JavaScript Object Notation).

JSON(<Parameter_1>)

Aggregate = No

 

Example – Collect Form1

Func-JSON-Example

 

Example 1

JSON([FieldA],lat) = 42

This formula is looking at FieldA, in this case a weather service field using https://api.openweathermap.org, and extracting particular information, in this case latitude, from the assigned service setup.

 

Example 2

JSON([FieldA],’main’,’temp’) = 22

This formula is looking at FieldA, in this case a weather service field using https://api.openweathermap.org, and extracting particular information, in this case temperature, from the assigned service setup.

 

Service Field

CONCAT('https://api.openweathermap.org/data/2.5/weather?lat=',[42],'&lon=',[-82],'&APPID=[PLACE_API_KEY_HERE]&units=metric')

LOOKUP

The LOOKUP function returns the last parameter where all the preceding pairs of parameters match. Comparisons are case insensitive and trimmed.

LOOKUP(<Parameter_1>,<Parameter_2>,<Parameter_3>…<Parameter_N>)

Aggregate = Yes

 

Example – Collect Form1

FieldA

FieldB

FieldC

GH

TRUE

12

GH

FALSE

34

 

LOOKUP([form1.FieldA],’GH’,[form1.FieldB],’TRUE’,[form1.FieldC]) = 12

This formula is looking at FieldA for the parameter value “GH”, FieldB for the parameter value “TRUE”, and returning the corresponding FieldC parameter value that matches the FieldA and FieldB criteria. In this case, the corresponding FieldC parameter value of “12”.

MOD

The MOD function calculates the modulus (remainder) of the first integer parameter divided by the second integer parameter.

MOD(<NumericParameter_1>,<NumericParameter_2>)

Aggregate = No

 

Example 1

FieldA

FieldB

5

2

 

MOD([FieldA],[FieldB]) = 1

 

Example 2

FieldA

FieldB

9

7

 

MOD([FieldA],[FieldB]) = 2

 

Example 2

FieldA

FieldB

13

5

 

MOD([FieldA],[FieldB]) = 3

NULL

The NULL() function returns a null value for specified parameters.

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

Aggregate = No

 

Example 1

FieldA

FieldB

FieldC

EarthSoft

B-30.417

20190415

 

NULL([FieldA],[FieldB],[FieldC]) = (null)

NUMVALUE

The NUMVALUE function examines the specified string parameter and returns the first section if numeric. Otherwise, the function returns null. The optional second parameter can be used to define the decimal separator.

NUMVALUE(<StringParameter_1>,{<OptionalParameter_1>})

Aggregate = No

 

Example 1

FieldA

2019EarthSoft

 

NUMVALUE([FieldA]) = 2019

 

Example 2

FieldA

2019,04-EarthSoft

 

NUMVALUE([FieldA],’,’) = 2019.04

ORD

The ORD function returns the UTF 8 numeric value (0-255) for a specified string character.

ORD(<StringParameter_1>)

Aggregate = No

 

Example 1

FieldA

a

 

ORD([FieldA]) = 97

 

Example 2

FieldA

M

 

ORD([FieldA]) = 77

 

Example 3

FieldA

y

 

ORD([FieldA]) = 121

POS

The POS function returns the position of the first string parameter within the second string parameter. The first string parameter position is considered zero (0). Therefore, a string parameter with 4 characters would contain positions 0, 1, 2, and 3. The function searches for the first instance of first string parameter passed (see Example 3).

POS(<StringParameter_1>,<StringParameter_2>)

Aggregate = No

 

Example 1

FieldA

FieldB

C

ABCD

 

POS([FieldA],[FieldB]) = 2

 

Example 2

FieldA

FieldB

b

ABCD

 

POS([FieldA],[FieldB]) = 1

 

Example 3

FieldA

FieldB

t

EarthSoft

 

POS([FieldA],[FieldB]) = 3

 

Example 4

FieldA

FieldB

s

EarthSoft

 

POS([FieldA],[FieldB]) = 5

SUBSTR

The SUBSTR function returns a substring from a source string given in the first parameter. The substring starting position is defined in the second parameter. An optional substring length is defined in the third parameter. The first string parameter position is considered zero (0).

SUBSTR(<StringParameter_1>,<NumericParameter_1>,{<NumericParameter_2>})

Aggregate = No

 

Example 1

FieldA

Test

 

SUBSTR([FieldA],1,2) = es

 

Example 2

FieldA

EarthSoft

 

SUBSTR([FieldA],5,4) = Soft

TOOADATE

The TOOADATE function converts a date-time value to a decimal representation that is the number of days, including fraction, since midnight December 30, 1899 (base OLE Automation Date).

TOOADATE(<DateTimeParameter_1>)

Aggregate = No

 

Example 1

FieldA

2019/01/01

 

TOOADATE([FieldA]) = 43466

 

Example 2

FieldA

2019/01/02 14:30:45

 

TOOADATE([FieldA]) = 43467.6046875

 

Example 3

FieldA

2019/04/15 15:33:51

 

TOOADATE([FieldA]) = 43570.648506944446

TOP

The TOP function sorts the records using the first parameter as the criterion, which then returns the value of the second parameter evaluated on the first (lowest value) row of the sorted first parameter.

TOP(<Parameter_1>,<Parameter_2>)

Aggregate = Yes

 

Example – Collect Form1

FieldA

FieldB

4

A1

6

B2

2

C3

 

TOP([form1.FieldA],[form1.FieldB]) = C3

This formula is sorting the records based on the FieldA parameter values and returning the FieldB parameter value ("C3") associated with the lowest value record of FieldA ("2").

UNITVALUE

The UNITVALUE function examines the string in the first parameter and returns the characters after the first integer or decimal numeric portion of the string. The optional second parameter can be used to define the separator, but the characters present in the optional second parameter will not be returned.

UNITVALUE(<StringParameter_1>,{<OptionalParameter_1>})

Aggregate = No

 

Example 1

FieldA

12m

 

UNITVALUE([FieldA]) = m

 

Example 2

FieldA

12[ft]

 

UNITVALUE([FieldA],'[]') = ft

 

Example 3

FieldA

12[ft]

 

UNITVALUE([FieldA]) = [ft]