<< 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

 

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

 

Example 2

 

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

 

Example 3

 

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

 

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

 

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

 

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

 

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

 

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

 

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

 

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

 

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

 

Example 2

 

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

 

Example 3

 

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

 

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

 

CHAR([FieldA]) = M

 

Example 2

 

CHAR([FieldA]) = d

 

Example 3

 

CHAR([FieldA]) = ↑

 

Example 4

 

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

 

CHECKSUM([FieldA]) = -622742239

 

Example 2

 

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

 

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

 

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

 

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

 

Example 2 – Collect Form1

 

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

 

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

 

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

 

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

 

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

 

Example 2

 

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

 

Example 3

 

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

 

FORMATNUM([FieldA],'000.00') = 001.24

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

 

Example 2

 

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

 

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

 

Example 2

 

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

 

Example 3

 

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

 

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

 

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

 

ISNUMERIC([FieldA]) = TRUE

 

Example 2

 

ISNUMERIC([FieldA]) = TRUE

 

Example 3

 

ISNUMERIC([FieldA]) = FALSE

 

Example 4

 

ISNUMERIC([FieldA]) = FALSE

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

 

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

 

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

 

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

 

Example 2

 

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

 

Example 3

 

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

 

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

 

ORD([FieldA]) = 97

 

Example 2

 

ORD([FieldA]) = 77

 

Example 3

 

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

 

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

 

TOOADATE([FieldA]) = 43466

 

Example 2

 

TOOADATE([FieldA]) = 43467.6046875

 

Example 3

 

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

 

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>,{<OptionalParameter_1>})

Aggregate = Yes

 

Example

 

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