Function Descriptions

<< Click to Display Table of Contents >>

EQuIS 7  >>  Collect > EQuIS Collect Enterprise > Form Designer > New Form Template > Forms and Fields > Formula Builder >

Function Descriptions

Functions and formulas add the ability to narrow results, and to select, view, and automate different types of fields and parameters. The functions can be used in numerous ways and many functions can be used in combination with others to generate simple or complex formulas. This topic describes in detail the different functions currently available in EQuIS Collect.

 

formula_builder-functions

 

A variety of standard functions are provided. There are two types of functions:

Non-Aggregate Functions gather data within specified parameters from the current row within the form.

Aggregate Functions gather data of a specified parameter from all possible combinations of rows, including the current row, in all used tables within the form and other specified forms.

 

Standard Functions

 

The standard, non-aggregate functions are grouped by their type and include: Math, String, Boolean, Other, Time, Conversion, and Conditional.

 

Math

The Math functions perform mathematical calculations as data is entered. The functions are only able to compute integer and decimal data. Below is a descriptive list of each math function and its operations.

 

ABS

Description: ABS is a non-aggregate function that calculates the absolute value of a specific parameter.

Example: ABS(-1) = 1.

ADD

Description: ADD is a non-aggregate function that summates the specified parameters together.

Example: ADD(1,2,4) = 7.

COS

Description: COS is a non-aggregate function that takes the cosine of the specified parameter.

Example: COS(15) = 0.965925826.

COSH

Description: COSH is a non-aggregate function that takes the hyperbolic cosine of the specified angle. The angle must be in radians.

Example: COSH(0.1) = 1.0050041680558035.

DIVIDE

Description: DIVIDE is a non-aggregate function that divides two specified parameters (x/y).

Example: DIVIDE(4,2) = 2.

EXP

Description: EXP is a non-aggregate function that calculates the exponential function raised to a specified power (ex).

Example: EXP(4) = 54.5981500331.

LOG

Description: LOG is a non-aggregate function that calculates the logarithm of a specified parameter in a specified base, LOG(value,base).

Example: LOG(100,10) = 2.

MAXVAL

Description: MAXVAL is a non-aggregate function that calculates the largest parameter out of a list. This can be used with any parameter type and ignores null values.

Example: MAXVAL(8.1,4.73,14,60.3,NULL,38.45,3.2,21.04) = 60.3.

MINVAL

Description: MINVAL is a non-aggregate function that calculates the smallest parameter out of a list. This can be used with any parameter type and ignores null values.

Example: MINVAL(8.1,4.73,14,60.3,NULL,38.45,3.2,21.04) = 3.2.

MULTIPLY

Description: MULTIPLY is a non-aggregate function that calculates the product of the specified parameters.

Example: MULTIPLY(2,5,3) = 30.

POWER

Description: POWER is a non-aggregate function that calculates a specified parameter raised to a specified power.

Example: POWER(4,2) = 16.

ROUND

Description: ROUND is a non-aggregate function that rounds a specified parameter to a specified number of fractional digits.

Example: ROUND(10.3508,1) = 10.4, ROUND(10.3508,2) = 10.35, ROUND(10.3508,3) = 10.351.

SIN

Description: SIN is a non-aggregate function that calculates the sine of the specified angle.

Example: SIN(15) = 0.25881904510252074.

SINH

Description: SINH is a non-aggregate function that calculates the hyperbolic sine of the specified angle. The angle must be in radians.

Example: SINH(0.1) = 0.10016675001984403.

SQRT

Description: SQRT is a non-aggregate function that calculates the square root of a specified parameter.

Example: SQRT(4) = 2.

SUBTRACT

Description: SUBTRACT is a non-aggregate function that subtracts the first parameter by the rest.

Example: SUBTRACT(6,1,3) = 2.

TAN

Description: TAN is a non-aggregate function that calculates the tangent of the specified angle.

Example: TAN(15) = 0.2679491924311227.

TANH

Description: TANH is a non-aggregate function that calculates the hyperbolic tangent of the specified angle. The angle must be in radians.

Example: TANH(0.1) = 0.09966799462495582.

 

String

The string functions perform calculations as data is entered. The functions are only able to compute string field data. Below is a descriptive list of each string function and its operations.

 

ADDLEADING

Description: ADDLEADING is a non-aggregate function that adds the third parameters character(s) to the beginning of the first parameter string section, matching the string length specified by the second parameter.

Example: ADDLEADING('arthSoft',9,'E') = EarthSoft.

ADDTRAILING

Description: ADDTRAILING is a non-aggregate function that adds the third parameters character(s) to the end of the first parameter string section, matching the string length specified by the second parameter.

Example: ADDTRAILING('EarthSof',9,'t') = EarthSoft.

CONCAT

Description: CONCAT is a non-aggregate function that links together a set of parameters. This function works with all parameter types.

Example: CONCAT('Earth','Soft') = EarthSoft; CONCAT('moon','-','light') = moon-light.

CONTAINS

Description: CONTAINS is a non-aggregate, case sensitive search function that returns TRUE if the first parameter is found within the specified second parameter.

Example: CONTAINS('soft','Earthsoft','Moonlight','Weather') = TRUE; CONTAINS('soft','EarthSoft','Moonlight','Weather') = FALSE.

CONTAINSWORD

Description: CONTAINSWORD is a non-aggregate function that conducts a case insensitive search of the first parameter string, within the listed following parameters and returns TRUE if found.

Example: CONTAINSWORD(earthsoft,EarthSoft,Moonlight,Weather) = FALSE, CONTAINSWORD(soft,EarthSoft,Moonlight,Weather) = FALSE.

COUNTLIST

Description: COUNTLIST is a non-aggregate function that counts all the comma delimited parameters in the specified list.

Example: COUNTLIST('field1','field23','field7','field4','field12','field9') = 6.

LEN

Description: LEN is a non-aggregate function that returns the number of characters in the specified parameter.

Example: LEN('EarthSoft') = 9, LEN('Test-Length') = 11, LEN('Test Length') = 11, LEN('TestLength') = 10.

REGEX

Description: REGEX is a non-aggregate function that takes the first parameter as a Regular Expression (REGEX) and searches for a match in the subsequent fields and returns TRUE if a match is found.

Example: REGEX('EarthSoft','EarthSoft-2018','earthsoft') = TRUE, REGEX('EarthSoft','earthsoft-2018','earthsoft') = FALSE.

REPLACE

Description: REPLACE is a non-aggregate function that replaces the instances of the second parameter string with the third parameter string in the first parameter string.

Example: REPLACE(‘EarthSoft’,’E’,3) = 3arthSoft.

SPLIT

Description: SPLIT is a non-aggregate function splits the first parameter using the second parameter string (separator) character, the third parameter value defines the split position and returns the corresponding split string.

Example: SPLIT('01/01/2018','/',2) = 2018.

TOLOWER

Description: TOLOWER is a non-aggregate function that converts a specified string field to all lower-case characters.

Example: TOLOWER(EarthSoft) = earthsoft.

TOUPPER

Description: TOUPPER is a non-aggregate function that converts a specified string field to all upper-case characters.

Example: TOUPPER(EarthSoft) = EARTHSOFT.

TRIM

Description: TRIM is a non-aggregate function that removes any initial and trailing blanks from a string field.

Example: TRIM( EarthSoft  ) = EarthSoft, TRIM(  Loc1-Site3) = Loc1-Site3.

 

Boolean

The Boolean functions can be used to evaluate and show or hide specified parameters.

 

ALLIN

Description: ALLIN is a non-aggregate function returns true if all the coma delimited values in the first parameter are found in the combined lists of coma delimited values of the following parameters.

Example: ALLIN('EarthSoft',1996,'Location','EarthSoft','Sample') = TRUE, ALLIN('EarthSoft,Location',1996,'Location','EarthSoft','Sample'), ALLIN('EarthSoft',1996,'Location','EarthSoft-2018','Sample') = FALSE.

AND

Description: AND is a non-aggregate function that requires that all parameters must be met to return TRUE.

Example: AND(EQ(6,6),LT(0.62,0.84)) = TRUE, AND(EQ(6,6),LT(0.90,0.84)) = FALSE, AND(EQ(6,1),LT(0.62,0.84)) = FALSE, AND(EQ(6,1),LT(0.90,0.84)) = FALSE.

BETWEEN

Description: BETWEEN is a non-aggregate function that returns TRUE if the third parameter is between the first two parameters.

Example: : BETWEEN(3,9,4) = TRUE, BETWEEN(3,9,1,) = FALSE, BETWEEN(DATETIME('2018/02/20 11:31:45AM'),DATETIME('2018/02/22 10:43:28 AM'),DATETIME('2018/02/21 14:37:08PM')) = TRUE, BETWEEN(DATETIME('2018/02/20 11:31:45 AM'),DATETIME('2018/02/22 10:43:28 AM'),DATETIME('2018/02/23 09:37:08 AM')) = FALSE.

EQ

Description: EQ is a non-aggregate function which returns TRUE if all values are equal.

Example: EQ(5,5) = TRUE, EQ(5,8) = FALSE.

FALSE

Description: FALSE will hide a specified parameter and always return FALSE.

Example: FALSE()

GE

Description: GE is a non-aggregate function that returns TRUE if the first parameter is greater than or equal to the second parameter.

Example: GE(7,7) = TRUE, GE(9,7) = TRUE, GE(3,7) = FALSE.

GT

Description: GT is a non-aggregate function that returns TRUE if the first parameter is greater than the second parameter.

Example: GT(6,4) = TRUE, GT(4,4) = FALSE, GT(1,4) = FALSE.

IN

Description: IN is a non-aggregate function that returns TRUE if the first parameter equals the specified second parameter.

Example: IN('EF',[Monitored By]) = TRUE, IN('AH',[Monitored By]) = FALSE, wherein the selection in the field [Monitored By] was selected as EF.

ISNULL

Description: ISNULL is a non-aggregate function that returns TRUE if any of the parameters are NULL.

Example: ISNULL(1,2,NULL) = TRUE.

LE

Description: LE is a non-aggregate function that returns TRUE if the first parameter is less than or equal to the second parameter.

Example: LE(7,7) = TRUE, LE(3,7) = TRUE, LE(8,7) = FALSE.

LT

Description: LT is a non-aggregate function that returns TRUE if the first parameter is less than the second parameter.

Example: LT(1,4) = TRUE, LT(9,4) = FALSE, LT(4,4) = FALSE.

NOT

Description: NOT is a non-aggregate function that returns the negation of a specified parameter.

Example: NOT(EQ(2,6)) = TRUE, NOT(EQ(6,6)) = FALSE, NOT(TRUE()) = FALSE.

OR

Description: OR is a non-aggregate function that requires only one parameter be met to return TRUE.

Example: OR(EQ(3,3),LT(0.90,0.84)) = TRUE, OR(EQ(5,3),LT(0.67,0.84)) = TRUE, OR(EQ(5,3),LT(0.90,0.84)) = FALSE.

TRUE

Description: TRUE will show a specified parameter and always return TRUE.

Example: TRUE()

 

Other

The Other group contains additional unique functions.

 

CASE

Description: CASE is a non-aggregate function that examines the 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.

Example: CASE(LT([pH],1),'Value for pH is extreme. Recheck reading and device.',GT([pH],13),'Value for pH is extreme. Recheck reading and device.'). CASE([pH],9,'The pH value is alkaline.’,8, 'The pH value is slightly alkaline.',7,'The pH value is neutral.',6,'The pH value is slightly acidic.').

CHECKSUM

Description: CHECKSUM is a non-aggregate function that converts a string parameter to a highly probable unique integer.

Example: CHECKSUM('EarthSoft') = -622742239, CHECKSUM('EQuIS Collect') = 471719544.

JSON

Description: JSON is a non-aggregate function that returns the value of the property inside the JSON.

Example: JSON([GeographyField],'Latitude') = Returns the latitude value.

NULL

Description: NULL is a non-aggregate function that returns NULL for the specified parameter.

Example: IF(EQ(6,6),NULL(27),2018) = NULL, IF(EQ(6,3),NULL(27),2018) = 2018.

 

Time

Time functions allow for control of temporal parameters.

 

DATETIME

Description: DATETIME is a non-aggregate function where, if one parameter is met then it attempts to convert it to a date-time, otherwise it returns the current date-time, (MM/dd/yyyy HH:mm).

Example: DATETIME('12/28/2017') = 2017-12-28 05:27:44, DATETIME('05:27:44') = 2018-01-17 05:27:44.

DAY

Description: DAY is a non-aggregate function that returns the day number, name or day of year number.

Example: DAY('05/26/2014') = 26, DAY('05/26/2014','name') = Monday, DAY('05/26/2014','dayofyear') = 146.

FORMATDATE

Description: FORMATDATE is a non-aggregate function where, if one parameter is met then it attempts to convert it to a short date (yyyy/MM/dd). If two parameters are met, it uses the second parameter in a string format. If no parameters are met, then it returns the current date as a short date.

Example: FORMATDATE('2016.01.22') = 2016/01/22, FORMATDATE('01.22.2016') = 2016/01/22, FORMATDATE('01.22.2016','02/17/2016 07:35:24') = 02/17/2016 07:35:24.

HOUR

Description: HOUR is a non-aggregate function that returns the hour number from a specified time or date-time.

Example: HOUR('11:26:48') = 11, HOUR('10/17/2013 11:26:48') = 11.

LONGDATE

Description: : LONGDATE is a non-aggregate function that converts the specified date or date-time value, or current date-time if no value is specified, to a long-date format.

Example: LONGDATE('2013/10/17 11:26:48') = Thursday, October 17, 2013, LONGDATE('2013/10/17’) = Thursday, October 17, 2013, LONGDATE('10/17/2013 11:26:48') = Thursday, October 17, 2013, LONGDATE('10/17/2013’) = Thursday, October 17, 2013.

MINUTE

Description: MINUTE is a non-aggregate function that returns minute number from a specified time or date-time.

Example: MINUTE('11:26:48') = 26, MINUTE('10/17/2013 11:26:48') = 26.

MONTH

Description: MONTH is a non-aggregate function that returns the month number or name from a specified date or date-time.

Example: MONTH('08/21/2010') = 8, MONTH('08/21/2010 11:54') = 8, MONTH('08/21/2010','name') = August, MONTH('08/21/2010 11:54','name') = August.

NOW

Description: NOW is a non-aggregate function that returns the current date and time.

Example: NOW() = yyyy-MM-dd HH:mm:ss (current).

QUARTER

Description: QUARTER is a non-aggregate function that returns the quarter number of the year from a specified date or date-time.

Example: QUARTER('2017/09/26') = 3, QUARTER('2017/09/26 10:29') = 3.

SECOND

Description: SECOND is a non-aggregate function that returns the second(s) number from a specified time or date-time.

Example: SECOND('11:26:48') = 48, SECOND('10/17/2013 11:26:48') = 48.

SHORTDATE

Description: SHORTDATE is a non-aggregate function where, if one parameter is met then it attempts to convert it to a short date; otherwise it returns the current date as a short date (yyyy/MM/dd).

Example: SHORTDATE('2013.06.27') = 2013/06/27, SHORTDATE('2013-06-27 04:36') = 2013/06/27.

TIME

Description: TIME is a non-aggregate function that returns the current time.

Example: TIME() = HH:mm:ss (current).

TIMESPAN

Description:  TIMESPAN is a non-aggregate function that calculates the time difference between two temporal parameters. The third parameter is optional and specifies the unit returned; seconds (s), minutes (m), hours (h) or days (d). If no third parameter is chosen, it defaults to days.

Example: TIMESPAN('01-01-2018','02-26-2018','s') = 4838400, TIMESPAN('01-01-2018','02-26-2018','m') = 80640, TIMESPAN('01-01-2018','02-26-2018','h') = 1344, TIMESPAN('01-01-2018','02-26-2018','d') = 56, TIMESPAN('01-01-2018','02-26-2018') = 56, TIMESPAN('01-01-2018 11:13AM','02-26-2018 9:15 AM') = 55.22:02.

TODAY

Description: TODAY is a non-aggregate function that returns the current day.

Example: TODAY() = yyyy/MM/dd (current).

YEAR

Description: YEAR is a non-aggregate function that returns the year number from a specified date or date-time.

Example: YEAR('10/23/2011') = 2011, YEAR('10/23/2011 09:41:38') = 2011.

 

Conversion

The conversion functions perform calculations as data is entered. The functions are only able to compute integer and decimal data. Below is a descriptive list of each conversion function and its operations.

 

DOUBLE

Description: DOUBLE is a non-aggregate function which converts a parameter into a decimal value.

Example: DOUBLE(3.56700) = 3.567, DOUBLE() = 0, DOUBLE('a.4') = NULL.

INTEGER

Description: INTEGER is a non-aggregate function which converts a parameter into an integer value. This function works with all parameter types.

Example: INTEGER(1.28) = 1, INTEGER() = 0, INTEGER(a.4) = NULL.

 

Conditional

Conditional functions evaluate a given condition, or set of conditions, and determine if the specified parameters meet the conditions and return a Boolean response of TRUE or FALSE. These functions work with all data types unless otherwise noted.

FILTER

Description: FILTER is a non-aggregate function capable of filtering specific parameters in all rows within a specified form. FILTER only works with other aggregate functions. The FILTER function evaluates a set of parameters. If the first parameter evaluates to TRUE then it returns the value of the second parameter for the corresponding row, otherwise it returns null.

Example: MAX(FILTER(EQ([Sample Form.pH],5),[Sample Form.DO]). This will return the largest DO (dissolved oxygen) where the pH is 5.

IF

Description: IF is a non-aggregate function that requires three parameters of which the first parameter has to Boolean and the second and third parameters can be of any type. If the first parameter returns TRUE, then it evaluates the second parameter, otherwise it evaluates the third parameter.

Example: IF(EQ(9,9),'TRUE','FALSE') = TRUE, IF(EQ(9,2),'TRUE','FALSE') = FALSE.

PARAM

Description: PARAM is a non-aggregate function that gets the first parameter value from a delimited string and returns the value associated with the second parameter (parameter name). An optional third parameter can be used to specify the character delimiter between parameter = value pairs. An optional fourth parameter can be used to specify the character between the parameter name and value.

Example: PARAM('A=1,B=2,C=3','C') = 3.

 

Aggregate Functions

 

Math

The Math functions perform mathematical calculations. The functions are only able to compute integer and decimal data. Below is a descriptive list of each math function and its operations.

 

AVG

Description: AVG is an aggregate function that averages all the specified parameters in all rows and specified forms.

Example: AVG([Sample Form.pH]).

MAX

Description: MAX is an aggregate function that returns the maximum value found for all the specified parameters in all rows and specified forms.

Example: MAX([Sample Form.pH]).

MIN

Description: MIN is an aggregate function that returns the minimum value found for all the specified parameters in all rows and specified forms.

Example: MIN([Sample Form.pH]).

PRODUCT

Description: PRODUCT is an aggregate function that calculates the product of all the specified parameters in all rows of specified forms.

Example: PRODUCT([Demo.IntegerField]).

SUM

Description: SUM is an aggregate function that sums all the specified parameters in all rows and specified forms.

Example: SUM([Demo.IntegerField]).

 

Other

The Other group contains additional unique functions.

 

AGGAND

Description: AGGAND is an aggregate function that returns true if all the non-null parameters are true.

Example: AGGAND(GE([Sample Form.pH],2.83)) = TRUE.

AGGOR

Description: AGGOR is an aggregate function ((returns true if any of the non-null value are true)).

Example: AGGOR(GE([Sample Form.pH],2.83)) = TRUE.

BOTTOM

Description: BOTTOM is an aggregate function that 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.

Example: BOTTOM([Sample Form.pH],[ Sample Form.Sample ID]) = This sorts the records based on pH value, with the record containing the highest pH value at the bottom. It then returns the Sample ID associated with the bottom record.

COUNT

Description: COUNT is an aggregate function that counts all rows of the specified parameter in specified form. This function works with all parameter types and counts all records.

Example: COUNT([Sample Form.pH]).

COUNTROWS

Description: COUNTROWS is an aggregate function that counts all the rows of specified parameters in specified forms. This function works with all parameter types but will not count empty, null or FALSE records.

Example: COUNTROWS([Sample Form.pH]).

GET

Description: GET is an aggregate function that returns the value of the second parameter when the first parameter evaluates to TRUE.

Example: GET(EQ([Sample Form.pH],’7’),[Sample Form.Sample ID]).

STABLEDIF

Description: STABLEDIF is an aggregate function that checks the stability of the first parameter using the second parameter as a sort criterion. The function checks that the difference between the two consecutive values, in the last number of values specified by the third parameter, is always less then the tolerance specified in the fourth parameter. An optional fifth parameter is a Boolean that when set to TRUE will force all rows to be processed to determine the stability condition. If the fifth parameter is not set, it is defaulted as FALSE and processed where any rows after the current row being evaluated for stability are not checked in the stability calculation.

Example: STABLEDIF([table1.DO],[table1.TIME],3,3).

STABLELE

Description: STABLELE is an aggregate function that checks the stability of the first parameter using the second parameter as a sort criterion. Checks that the last number of values, specified by the third parameter are, are all less than the tolerance specified by the fourth parameter. An optional fifth parameter is a Boolean that when set to TRUE will force all rows to be processed to determine the stability condition. If the fifth parameter is not set, it is defaulted as FALSE and processed where any rows after the current row being evaluated for stability are not checked in the stability calculation.

Example: STABLELE([table1.pH],[table1.TIME],3,6).

STABLEMAX

Description: STABLEMAX is an aggregate function that checks the stability of the first parameter using the second parameter as a sort criterion. Checks that the difference between the largest and smallest value in the last number of values, specified by the third parameter, is within the tolerance specified in the fourth parameter. An optional fifth parameter is a Boolean that when set to TRUE will force all rows to be processed to determine the stability condition. If the fifth parameter is not set, it is defaulted as FALSE and processed where any rows after the current row being evaluated for stability are not checked in the stability calculation.

Example: STABLEMAX([table1.DO],[table1.TIME],3,3).

STABLERD

Description: STABLERD is an aggregate function that checks the stability of the first parameter using the second parameter as a sort criterion. Checks that the relative difference between two consecutive values (the difference divided by the average of the two) in the last number of values, specified by the third parameter, is always less than the tolerance (specified as percent) specified in the fourth parameter. An optional fifth parameter is a Boolean that when set to TRUE will force all rows to be processed to determine the stability condition. If the fifth parameter is not set, it is defaulted as FALSE and processed where the rows after the current row being evaluated for stability are not checked in the stability calculation

Example: STABLERD([table1.pH],[table1.TIME],3,6.

TOP

Description: TOP is an aggregate function that sorts the records using the first parameter as the criteria, which then returns the value of the second parameter assessed on the first (lowest value) row of the sorted first parameter.

Example: TOP([Sample Form.pH],[ Sample Form.Sample ID]) = This sorts the records based on pH value, with the record containing the lowest pH value at the top. It then returns the Sample ID associated with the top record.

 

String

The string functions perform calculations as data is entered. The functions are only able to compute string field data. Below is a descriptive list of each string function and its operations.

 

JOIN

Description: JOIN is an aggregate function that combines the value of the first parameter evaluated on each record, using the optional delimiter specified by the second parameter. If no delimiter is specified, then a coma is used.

Example: : JOIN([Technicians.Samplers],'-'). This formula combines all the samplers selected in each record of the technician form, separating each sampler with a hyphen (-).