Other Functions

<< Click to Display Table of Contents >>

Navigation:  Collect > Collect Enterprise > Template 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). If the first parameter is NULL or cannot be converted to a valid date-time or if the second parameter cannot be converted to a valid timespan, then the function returns NULL.

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. It ignores NULL values and returns NULL if there are no rows in the table.

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. Null values are ignored but if there are no rows in the table, then the function returns NULL.

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 the current record using the first parameter as a sort criterion and the second parameter as an optional starting value. It compares the current record sort value against each sort value of all prior records, after sorting, and adds the number of prior records found to the starting record count. If there are no records, then the function returns NULL.

 

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, and then returns the value of the second parameter assessed on the last (highest sort value) row of the sorted list. The function returns NULL if there are no records.

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 works in two different ways depending on the number of parameters. If the number of parameters is even, then it examines each odd parameter and returns the next even parameter if the previous one evaluates to true. If the number of parameters is odd, then it compares the first parameter against each even parameter and if they are the same, returns the following odd parameter value. If an even number of parameters is passed and an odd parameter that is NULL or cannot be converted to a Boolean is encountered before a true value is encountered, then the function returns NULL.

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.

 

Example 4

FieldA

FieldB

FieldC

EarthSoft

B-30.417

20190415

 

CASE(‘EarthSoft’,[FieldA],’EXTREME’,[FieldB],’MODERATE’,[FieldC],’Low’) = EXTREME

CASE(‘20190415’,[FieldA],’EXTREME’,[FieldB],’MODERATE’,[FieldC],’Low’) = LOW

CHAR

The CHAR function returns the character as a string from the character code value in UTF 8 (0-255) character set. If the parameter cannot be converted to an integer or is NULL or is outside of the range (0-255), then the function returns NULL.

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 or defining numeric identifiers to complex strings. Empty strings return 0, but NULL values return NULL.

CHECKSUM(<StringParameter_1>)

Aggregate = No

 

Example 1

FieldA

EarthSoft

 

CHECKSUM([FieldA]) = -622742239

 

Example 2

FieldA

30B201904

 

CHECKSUM([FieldA]) = 1652253079

CORREL

The CORREL function calculates the Pearson's correlation coefficient (r squared) for a set of X, Y numeric pairs. The first parameter is the X parameter, the second parameter is the Y parameter, and the optional third parameter defines the number of values from the current row to consider (going back). Values are sorted on X and if any NULL values are found in the regression, then the function returns NULL.

CORREL(<NumericParameter_1>,<NumericParameter_2><NumericParameter_3>)

Aggregate = Yes

 

Example – Collect Form1

FieldX

FieldY

1

10

2

20

3

30

4

40

 

CORREL([form1.FieldX],[form1.FieldY]) = 1

COUNT

The COUNT function counts the number of non-NULL parameter values for the specified parameter on all rows. If no rows are in the table, then the function returns NULL. If all the values are found are NULL, then returns 0.

COUNT(<Parameter_1>)

Aggregate = Yes

 

Example

FieldA

EarthSoft

2019

Demo

 

COUNT(FieldA) = 3

COUNTROWS

The COUNTROWS function counts the number of rows that contain some non-NULL parameter values for the specified parameters on all rows. If no rows are in the table, then the function returns NULL. If all the values are found are NULL, then returns 0.

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]) = 3

 

Example 2 – Collect Form1

FieldA

FieldB

FieldC

4

A1


6

B2

EarthSoft

2

C3

Complete

 

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

COUNTTRUE

The COUNTTRUE function counts the number of parameter values that evaluate to TRUE for all rows used. If no rows are in the table, then it returns NULL. Parameters that cannot be converted to a Boolean are not counted.

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 parameters specified in all rows. The DISTINCT function does not include duplicate values or codes. Values will be sorted in alphanumeric ascending order. If the table specified has no rows, the function returns NULL.

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],[form1.FieldC]) = A,D,E,F,J,S,V

This formula is combining the unique FieldA, FieldB and FieldC values from each record in ascending order.

 

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],[form1.FieldC]) = B-30,B-32,E-24,M-19,P-04,W-93

This formula is combining the unique FieldA, FieldB and FieldC values from each record in ascending order.

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. If either of the parameters cannot be converted to a numeric value or are NULL, or if the second parameter evaluates to 0, then the function returns NULL.

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

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. If the first parameter is NULL, then the function returns NULL.

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. If the parameter is NULL or cannot be converted to a numeric value, then the function returns NULL, otherwise it returns the corresponding date-time value. See Microsoft documentation about the FromOADate function here: https://docs.microsoft.com/en-us/dotnet/api/system.datetime.fromoadate?view=netcore-3.1

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 only if the second parameter is not NULL. If no records are in the table or if the first parameter is never TRUE or if when TRUE, the second parameter is always NULL, then the function returns NULL.

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

INTERCEPT

The INTERCEPT function calculates the linear regression intercept for a set of X, Y numeric pairs. The first parameter is the X parameter, the second parameter is the Y parameter, and the optional third parameter defines the number of values from the current row to consider (going back). Values are sorted on X and if any null values are found in the regression, then the function returns NULL.

INTERCEPT(<NumericParameter_1>,<NumericParameter_2><NumericParameter_3>)

Aggregate = Yes

 

Example – Collect Form1

FieldX

FieldY

1

10

2

20

3

30

4

40

5

50

6

60

7

61

8

62

9

63

10

64

11

65

12

66

 

ROUND(INTERCEPT([form1.x],[form1.y],6),4) = 54

Applying the above function on the last row, the function applies linear regression using rows 6 through 12 (goes 6 rows back) and the Y-axis intercept of said regression is 54. Note that rounding is used to eliminate decimal errors.

The same function applied on row 6 would return 0.

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

JARRAY

The JARRAY function is used to create a JSON (JavaScript Object Notation) array of objects. This function will go through all records and generate a JSON object for each record, combining them into an array of JSON objects. The function takes an even number of parameters with the odd parameters corresponding to the JSON objects key or property name and the following parameter its value. If a key is not a valid string value or the value is null, then it will be ignored and not added to the JSON object. If there are no rows in the table, then the function returns NULL.

JARRAY(<’ParameterName1’,<Parameter_1>,<’ParameterName2’,<Parameter_2>…<’ParameterNameN’,<Parameter_N>)

Aggregate = Yes

 

Example 1

Collect Form1

FieldA

FieldB

B-30

0.08

B-31

1

B-32

3

 

JARRAY(‘Location’,[Form1.FieldA],’Value’,[Form1.FieldB]) = [{“Location”: “B-30”,”Value”:”0.08”}, {“Location”: “B-31”,”Value”:”1”}, {“Location”: “B-32”,”Value”:”3”}]

JSON

The JSON function returns the value of a property inside of a JSON (JavaScript Object Notation) object or array. The first parameter contains the JSON string representing an array or object. If any of the parameters are NULL or if the first parameter is an empty JSON, then the function returns NULL. The second and following parameters define how to find the requested value. They can be either a property name or an index of an array.

JSON(<Parameter_1>, <Parameter_2>, {<optionalParameter1>…<optionalParameterN>})

Aggregate = No

 

Example – Collect Form1

func-json-example_zoom45

 

Example 1

JSON([FieldA],’weather’,’0’,’description’) = “cloudy”

This formula is extracting the latitude weather property from the JSON object in FieldA. This property is itself a JSON array and therefore the following ‘0’ tells it to get the first element of that array, which is a JSON object. The final ‘description’ parameter value tells the function to extract the description field value from this object.

 

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 main object.

 

Service Field

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

LASTN

The LASTN function determines if a value is within the last N values based on a specific sort criteria. The function returns TRUE if the compareValue is within the last number of records from the current record, when sorted using the sortCriteria. If any of the parameters are NULL when evaluated on the current row or if the compare value comes before the current rows sortCriteria, then the function returns FALSE. The function also returns FALSE if more than <number> rows are found between the current rows sort value and the compare value. The function returns NULL if there are no rows in the table. It should be noted that the compareValue and number are evaluated once at start of the aggregation process and on the current row.

LASTN(<compareValue>,<sortCriteria>,<number>)

Aggregate = Yes

 

Example – Collect Form1

Row Number

Value

1

10

2

20

3

30

4

40

5

50

6

60

7

70

8

80

9

90

 

LASTN(50,[Form1.Value],3) = TRUE when evaluated on rows 5, 6 and 7 and FALSE for the others.

LOOKUP

The LOOKUP function returns the last parameter where all the preceding pairs of parameters match. Comparisons are case insensitive and trimmed. Returns NULL if a match was not found or if there are no rows in the table or if the found parameter value is NULL.

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. Returns NULL if either of the parameters could not be converted to a number or are NULL or if the second parameter evaluates to 0.

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 3

FieldA

FieldB

13

5

 

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

NULL

The NULL() function returns a NULL value. Parameters are ignored.

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

Aggregate = No

 

Example 1

FieldA

FieldB

FieldC

EarthSoft

B-30.417

20190415

 

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

ORD

The ORD function returns the UTF 8 numeric value (0-255) for a specified string character. Returns NULL if the parameter cannot be converted to a char or if it NULL.

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

SLOPE

The SLOPE function calculates the linear regression slope for a set of X, Y numeric pairs. The first parameter is the X parameter, the second parameter is the Y parameter, and the optional third parameter defines the number of values from the current row to consider (going back). Values are sorted on X and if any NULL values are found in the regression, then the function returns NULL.

SLOPE(<NumericParameter_1>,<NumericParameter_2>,<NumericParameter_3>)

Aggregate = Yes

 

Example – Collect Form1

FieldX

FieldY

1

10

2

20

3

30

4

40

5

50

6

60

7

61

8

62

9

63

10

64

11

65

12

66

 

ROUND(SLOPE([form1.x],[form1.y],6),4) = 10 when evaluated on row 6.

ROUND(SLOPE([form1.x],[form1.y],6),4) = 1 when evaluated on row 12.

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). The function returns NULL if the parameter cannot be converted to a valid date-time. For more information regarding TOOADATE, see Microsoft documentation here: https://docs.microsoft.com/en-us/dotnet/api/system.datetime.tooadate?view=netcore-3.1

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 gets the first non-NULL value using the first parameter as the sort criterion. The function returns NULL if there are no records in the table or if values found for the second parameter are NULL.

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").

TREND

The TREND function determines the best fit line for a set of X, Y values and returns an estimate for a Y value at a certain point (X value) using that determined best fit line. The first parameter is the X parameter, the second parameter is the Y parameter, the third parameter defines the X value to be used to forecast the Y value, and the optional fourth parameter defines the number of values from the current row to consider (going back). Values are sorted on X and if any null values are found in the regression, then the function returns NULL.

TREND(<NumericParameter_1>,<NumericParameter_2>, <NumericParameter_3>, {<optionalParameter1>}, )

Aggregate = Yes

 

Example

FieldA (i.e.,X)

FieldB (i.e., Y)

1

10

2

20

3

30

4

40

 

TREND([FieldA],[FieldB], 5, 4) = 50

This formula is using the last 4 records, sorting the records based on the FieldA parameter (X) values, determining the best fit line for the FieldA, FieldB (X, Y) pairs, and returning the estimated FieldB (Y) parameter value ("50") for the provided X value of "5".