<< 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 in Collect Mobile. 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 timespan value in the second parameter to the date-time value in the first parameter, then returns the new date-time. The added timespan value should be written with the syntax of dd:HH:mm:ss (days : hours : minutes : seconds). If the first parameter is NULL or the second or first parameter cannot be converted to a valid date-time then the function returns NULL.

ADDDATE(<DateTimeParameter _1>,<TimespanParameter_2>)

Aggregate = No

 

Example 1

 

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

 

Example 2

 

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

 

Example 3

 

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

AGGAND

The AGGAND function returns TRUE if all non-null value(s) are TRUE for all rows. AGGAND ignores NULL values and returns NULL if there are no rows in the table.

AGGAND(<Parameter_1>)

Aggregate = Yes

 

Example 1 – Form1

 

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

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

 

Example 2 – Form1

 

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

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

AGGOR

The AGGOR function returns TRUE if any non-null value(s) are TRUE for all rows. AGGOR ignores null values and returns NULL if there are no rows in the table.

AGGOR(<Parameter_1>)

Aggregate = Yes

 

Example 1 – Form1

 

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

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

 

Example 2 – Form1

 

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

This formula is checking if any FieldA values are greater or equal to 4. In this case, none of the FieldA 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.

 

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 count value. After sorting, AUTONUM compares the current record's sort value against each sort value of all prior records, 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 – Form1

 

Where AUTONUM Formula = AUTONUM([Form1.FieldA])

 

The records in FieldA are entered in the order from top to botton. The AUTONUM formula sorts the records based on the values of Form1.FieldA in alphanumeric ascending order, then returns a count of the current record and all prior records. In the case where FieldA = 4, the formula returns 2, counting two Form1.FieldA records with parameter values of 2 and 4.

 

Example 2 – Auto-increment SYS_SAMPLE_CODE for each SYS_LOC_CODE

 

SampleForm:

 

 

SYS_SAMPLE_CODE formula = CONCAT([SYS_LOC_CODE],'-',AUTONUM(FILTER(EQ([SYS_LOC_CODE],[SampleForm.SYS_LOC_CODE]),[SampleForm.#ID]),1))

 

The AUTONUM portion of the SYS_SAMPLE_CODE formula uses the FILTER function to assign incrementing numbers to form records only with the same SYS_LOC_CODE.

 

 

SampleForm Result:

 

 

In the above example, two form records are filtered out based on their SYS_LOC_CODE, B-30. Each form record returned a unique #ID, therefore the resulting SYS_SAMPLE_CODES were autoincremented.

BOTTOM

The BOTTOM function sorts the records using the first parameter as the sort criterion, then returns the first non-null value of the second parameter assessed on the last row (highest sort value) of the sorted list.

BOTTOM(<Parameter_1>,<Parameter_2>)

Aggregate = Yes

 

Example – Form1

 

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

This formula is sorting the records in alpha-numeric ascending order based on the FieldA values and returning the FieldB value (B2) associated with the highest sort value record of FieldA (6).

CASE

The CASE function returns output based on the first of an ordered list of conditional statements that evaluates to TRUE. It works in two different ways depending on the number of parameters used.

•If the number of parameters is even, each odd parameter represents a Boolean condition and the following even parameter is the result that will be returned when the condition evaluates to TRUE.

•If the number of parameters is odd, the first parameter is compared against each even parameter. When a match is found, it returns the following odd parameter value.

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

Aggregate = No

 

Example 1 – Even

 

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

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

 

Example 2 – Even

 

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 3 – Odd

 

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 the UTF 8 (0-255, excluding control characters 0-32 and 127-160) character set. If the parameter cannot be converted to an integer, is NULL, is outside of the range (0-255), or is in the list of excluded characters, then the function returns NULL.

CHAR(<NumericParameter_1>)

Aggregate = No

Example 1

 

CHAR([FieldA]) = M

 

Example 2

 

CHAR([FieldA]) = d

 

Example 3

 

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 or NULL values return NULL.

CHECKSUM(<StringParameter_1>)

Aggregate = No

 

Example 1

 

CHECKSUM([FieldA]) = -1700686623

 

Example 2

 

CHECKSUM([FieldA]) = 1652253079

CORREL

The CORREL function calculates the Pearson's correlation coefficient 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>,<OptionalNumericParameter_3>)

Aggregate = Yes

 

Example 1 –  Form1

 

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

 

Example 2 –  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 the function returns 0.

COUNT(<Parameter_1>)

Aggregate = Yes

 

Example –  Form1

 

COUNT([Form1.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 value are not counted.

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

Aggregate = Yes

 

Example 1 – Form1

 

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

DBCONN

The DBCONN function returns the FACILITY_ID, FACILITY_CODE, or FACILITY_NAME of the facility (as a string value) from the database that Link is connected to. The DBCONN function needs to be used with Link through Professional EDP or Enterprise EDP to return values from a connected database.

DBCONN(<StringParameter_1>)

Aggregate = No

 

Example – The expected values are: FACILTY_NAME = 'Springfield', FACILITY_CODE = 'SPG', and FACILITY_ID = '123'.

•DBCONN('facility_name') = 'Springfield'

•DBCONN('facility_code') = 'SPG'

•DBCONN('facility_id') = '123'

 

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. Values are 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 – 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 alphanumeric 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 alphanumeric 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 numeric 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 or cannot be converted to a numeric value, 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.

FROMOADATE

The FROMOADATE function converts a numerical representation of a date (base OLE Automation Date) to a date-time value. If the parameter is NULL or cannot be converted to a numeric value, then the function returns NULL. For more information, see DateTime.ToOADate Method.

FROMOADATE(<NumericParameter_1>)

Aggregate = No

 

Example 1

 

FROMOADATE([FieldA]) = 2019-01-01 12:00:00 AM

 

Example 2

 

FROMOADATE([FieldA]) = 2019-01-02 2:30:45 PM

 

Example 3

 

FROMOADATE([FieldA]) = 2019-04-15 3:33:51 PM

GET

The GET function returns the value of the second parameter when the first parameter evaluates to TRUE. If no records are in the table, the first parameter is never TRUE or when TRUE, the second parameter is always NULL, then the function returns NULL.

GET(<Parameter_1>,<Parameter_2>)

Aggregate = Yes

 

Example – Form1

 

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

GET(EQ([Form1.FieldA],5),[Form1.FieldB]) = NULL

GET(EQ([Form1.FieldA],7),[Form1.FieldB]) = NULL

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>,<OptionalNumericParameter_3>)

Aggregate = Yes

 

Example – Collect Form1

 

INTERCEPT([form1.x],[form1.y],6) = 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.

The same function applied on row 6 would return 0.

LASTN

The LASTN function determines if a value is within the last N values based on a specific sort criterion. The function returns TRUE if the CompareValue parameter is within the last N number of records from the current record, when sorted using the SortCriteria paramter. The function returns FALSE if any of the parameters are NULL when evaluated on the current records, the CompareValue does not fall within the last N number of records or more than N number of records are found between the current records sort value and the CompareValue The function returns NULL if there are no rows in the table.

LASTN(<CompareValue>,<SortCriteria>,<N Number>)

Aggregate = Yes

 

Example – Form1

 

Formula = LASTN(50,[Form1.FieldA],3)

The above formula looks to see if the FieldA value of the last 3 records equals 50. Rows 5, 6 and 7 evaluate TRUE as a value of FieldA equals 50 within the last 3 records. Note that the number of rows back considers the current row also, as row 5 evaluates as TRUE.

LOOKUP

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

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

Aggregate = Yes

 

Example – Form1

 

LOOKUP([Form1.FieldA],’GH’,F[orm1.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 is “12”.

 

MOD

The MOD function calculates the modulus (remainder) of the first integer parameter divided by the second integer parameter. The function 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(<IntegerParameter_1>,<IntegerParameter_2>)

Aggregate = No

 

Example 1

 

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

 

Example 2

 

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

NULL

The NULL() function returns a NULL value.

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

Aggregate = No

 

Example 1

 

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

 

Example 2

The NULL() function can be run with no parameters and will return NULL.

 

NULL() = NULL

ORD

The ORD function returns the UTF 8 numeric value (0-255, control characters 0-32, and 127-160) for a specified string character. It 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 – Form1

 

Formula = (SLOPE([Form1.FieldX],[Form1.FielY],6)

TOOADATE

The TOOADATE function converts a date-time value to a numerical 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, see DateTime.ToOADate Method.

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 sorts the records using the first parameter as the sort criterion, then returns the first non-null value of the second parameter assessed on the first row (lowest sort value) of the sorted list.. 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 – Form1

 

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

This formula is sorting the records in alphanumerical ascending order 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 – Form1

 

TREND([Form1.FieldA],[Form1.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".