Expression functions and operators

This topic lists and describes the functions and operators that you can use to construct expressions, using the Edit Expression Binding dialog box.

Math operators


Operator	Description	Expression
NEGATE	Returns the negative value of a number	-( `number` ) or -( `number1` + `number2` )
+	Returns the sum of two numbers or concatenation of two strings Note: For date values, use the DATEADD function.	`value1` + `value2` where `value` is a number or string
-	Returns the difference or negation of two numbers Note: For date values, use the DATEDIFF function.	`number1` - `number2`
*	Returns the product of two numbers	`number1` * `number2`
/	Returns the quotient of two numbers	`number1` / `number2`
%	Returns the remainder after dividing two numbers	`number1` % `number2`

Math functions


Operator	Description	Expression
ABS	Returns the absolute value of a number	ABS ( `number` )
CEILING	Returns the least integer that is greater than or equal to the value of a number	CEILING ( `number` )
FLOOR	Returns the first integer that is less than or equal to the value of a number	FLOOR ( `number` )
MAX	Returns the larger of two numbers	MAX ( `number1`, `number2` )
MIN	Returns the smaller of two numbers	MIN ( `number1`, `number2` )
POWER	Returns the value of a number raised to a power	POWER ( `number1`, `number2` ) where `number1` is the value `number2` is the exponent
ROUND	Returns the value of a number rounded to the number of decimal places	ROUND ( `number1`, `number2` ) where `number1` is the value `number2` is the number of decimal places
SIGN	Returns 1 or -1 to indicate positive or negative value of a number	SIGN ( `number` )
SQRT	Returns the square root of a number	SQRT ( `number` )
TRUNC	Returns the value of a number rounded to the number of decimal places	TRUNC ( `number1`, `number2` ) where `number1` is the specified value `number2` is the number of decimal places

Comparison operators


Operator	Description	Expression
== or =	Compares two values and returns True if they are equal and False otherwise	`value1` == `value2` where `value` is a number, Boolean value, or string
>=	Compares two values and returns True if the first value is greater than or equal to the second value and False otherwise	`value1` >= `value2` where `value` is a number, Boolean value, or string
<=	Compares two values and returns True if the first value is less than or equal to the second value and False otherwise	`value1` <= `value2` where `value` is a number, Boolean value, or string
>	Compares two values and returns True if the first value is greater than the second value and False otherwise	`value1` > `value2` where `value` is a number, Boolean value, or string
<	Compares two values and returns True if the first value is less than the second value and False otherwise	`value1` < `value2` where `value` is a number, Boolean value, or string
!= or <>	Compares two values and returns True if they are not equal and False otherwise	`value1` != `value2` where `value` is a number, Boolean value, or string

Logical operators


Operator	Description	Expression
AND or &&	Logically And’s two Boolean values	`BooleanValue1` && `BooleanValue2`
NOT or !	Logically negates a Boolean value	!( `BooleanValue` )
OR or \|\|	Logically Or’s two Boolean values	`BooleanValue1` \|\| `BooleanValue2`

Logical functions


Operator	Description	Expression
CONTAINS	Returns True if the string to seek is contained within the string to be searched and False otherwise	CONTAINS ( `string1`, `string2` ) where `string1` is the string to be searched `string2` is the string to seek
IF	Evaluates the condition and returns the value based on result Note: The value can be a number, Boolean value, or string.	IF ( `value1`, `value2`, `value3` ) where `value1` is the comparison expression `value2` is the result if true `value3` is the result if false
ISEMPTY	Returns True if the string is empty and False otherwise	ISEMPTY ( `string` )

Text functions


Operator	Description	Expression
CONCAT	Returns the concatenation of two strings	CONCAT ( `string1`, `string2` )
INSTR	Returns the 1-based starting index of the search string within the value and 0 if the search string is not found within the value	INSTR ( `string1`, `string2` ) or INDEXOF ( `string1`, `string2` ) where `string1` is the specified value `string2` is the search string
LEFT	Returns a part of a string based on the number of characters from the start of the strin	LEFT ( `string`, `number` ) where `string` is the specified value `number` is the number of characters
LEN	Returns the length of a string, as number of characters	LEN ( `string` ) or LENGTH ( `string` )
LOWER	Returns a string in lower case	LOWER ( `string` )
LTRIM	Returns a string in which whitespace is removed from the start of the string Note: Quotation marks are included to display that the trailing whitespace was removed.	LTRIM ( `string` )
REPLACE	Returns a string in which all occurrences of the search string in the value are replaced by the replacement string	REPLACE ( `string1`, `string2`, `string3` ) where `string1` is the specified value `string2` is the search string `string3` is the replacement string
RIGHT	Returns a part of a string based on the number of characters from the end of the string	RIGHT ( `string`, `number` ) where `string` is the specified value `number` is the number of characters
RTRIM	Returns a string in which whitespace is removed from the end of the string Note: Quotation marks are included to display that the trailing whitespace was removed.	RTRIM ( `string` )
SUBSTRING	Returns a part of a string based on the 1-based starting index and optionally the number of characters	SUBSTRING ( `string`, `number1`, `number2` ) where `string` is the specified value `number1` is the 1-based start index `number2` is the number of characters (optional)
TRIM	Returns a string in which whitespace is removed from the start and end of the string Note: Quotation marks are included to display that the trailing whitespace was removed.	TRIM ( `string` )
UPPER	Returns a string in upper case	UPPER ( `string` )

Accumulator functions


Operator	Description	Expression
AVGACCUM	Returns the average of the property value optionally grouped by property Note: Properties of numeric data type are supported. This function is available for report forms only.	AVGACCUM (`string1`,`string2`) where `string1` is the property name `string2` is the group property name (optional)
AVGPAGEACCUM	Returns the average of the property value within the page. Note: This function is supported only for properties of numeric data type.	AVGPAGEACCUM ( `string` ) where `string` is the property name
CNTACCUM	Returns the count of rows where the property value is not blank optionally grouped by property Note: This function is available for report forms only.	CNTACCUM (`string1`,`string2`) where `string1` is the property name `string2` is the group property name (optional)
CNTPAGEACCUM	Returns the count of rows where the property value is not blank or empty within the page.	CNTPAGEACCUM ( `string` ) where `string` is the property name
MAXACCUM	Returns the maximum of the property value optionally grouped by property Note: This function is available for report forms only.	MAXACCUM (`string1`,`string2`) where `string1` is the property name `string2` is the group property name (optional)
MAXPAGEACCUM	Returns the maximum value of the property within the page.	MAXPAGEACCUM ( `string` ) where `string` is the property name
MINACCUM	Returns the minimum of the property value optionally grouped by property Note: This function is available for report forms only.	MINACCUM (`string1`,`string2`) where `string1` is the property name `string2` is the group property name (optional)
MINPAGEACCUM	Returns the minimum value of the property within the page.	MINPAGEACCUM ( `string` ) where `string` is the property name
SUMACCUM	Returns the summation of the property value optionally grouped by property Note: Properties of numeric data type are supported. This function is available for report forms only.	SUMACCUM (`string`, `string2`) where `string1` is the property name `string2` is the group property name (optional)
SUMPAGEACCUM	Returns a summation of the property values within the page. Note: This function is supported only for properties of numeric data type.	SUMPAGEACCUM ( `string` ) where `string` is the property name

Aggregate functions


Operator	Description	Expression
AVG	Returns the average of a property that is optionally grouped or filtered by a group property	AVG ( `string1`, `string2` ) where `string1` is the property name `string2` is the group property (optional)
AVGEXP	Returns the average of an expression that is optionally grouped of filtered by a group property.	AVGEXP (`string1`,`string2`,`string3`) where `string1` is the collectionID of the collection which the expression is evaluated. `string2` is the expression to be evaluated for each row in the collection. `string3` is the group property name (optional).
CNT	Returns the count of non-null or non-blank values a property that is optionally grouped or filtered by a group property	CNT ( `string1`, `string2` ) where `string1` is the property name `string2` is the group property (optional)
CNTEXP	Returns the count of non-null or non-blank values of an expression that is optionally grouped or filtered by a group property.	CNTEXP (`string1`,`string2`,`string3`) where `string1` is the collectionID of the collection which the expression is evaluated. `string2` is the expression to be evaluated for each row in the collection. `string3` is the group property name (optional).
MAXAGG	Returns the maximum value of a property that is optionally grouped or filtered by a group property Note: The value can be a number, date, or string depending on the data type of the property.	MAXAGG ( `string1`, `string2` ) where `string1` is the property name `string2` is the group property (optional)
MAXEXP	Returns the maximum value of an expression that is optionally grouped or filtered by a group property. Note: The value can be a number, date, or string depending on the data type of the expression.	MAXEXP (`string1`,`string2`,`string3`) where `string1` is the collectionID of the collection which the expression is evaluated. `string2` is the expression to be evaluated for each row in the collection. `string3` is the group property name (optional).
MINAGG	Returns the minimum value of a property that is optionally grouped or filtered by a group property Note: The value can be a number, date, or string depending on the data type of the property.	MINAGG ( `string1`, `string2` ) where `string1` is the property name `string2` is the group property (optional)
MINEXP	Returns the minimum value of an expression that is optionally grouped or filtered by a group property. Note: The value can be a number, date, or string depending on the data type of the expression.	MINEXP (`string1`,`string2`,`string3`) where `string1` is the collectionID of the collection which the expression is evaluated. `string2` is the expression to be evaluated for each row in the collection. `string3` is the group property name (optional).
SUM	Returns the summation of a property that is optionally grouped or filtered by a group property	SUM ( `string1`, `string2` ) where `string1` is the property name `string2` is the group property (optional)
SUMEXP	Returns the summation of an expression that is optionally grouped or filtered by a group property	SUMEXP (`string1`,`string2`,`string3`) where `string1` is the collectionID of the collection which the expression is evaluated. `string2` is the expression to be evaluated for each row in the collection. `string3` is the group property name (optional).

Date functions


Operator	Description	Expression
CURDATE	Returns the current date	CURDATE ( )
CURDATETIME	Returns the current date and time	CURDATETIME ( )
DATE	Returns a date that contains the year, month, and day	DATE ( `year`, `month`, `day` )
DATEADD	Returns a new date based on the interval that is added to the date	DATEADD ( `string`, `number`, `date` ) where `string` is the date interval
DATEDIFF	Returns the difference between two dates based on the interval	DATEDIFF ( `string`, `date1`, `date2` ) where `string` is the date interval
DATEPART	Returns a part of the date based on the interval	DATEPART ( `string`, `date` ) where `string` is the date interval
ISOWEEKNUM	Returns the ISO 8601 week number of the date	ISOWEEKNUM ( `date` )

Mongoose functions


Operator	Description	Expression
C	Returns the value of a component	C ( `string` ) where `string` is the component name
FORMAT	Returns the formatted or translated value of a string by using substitution string(s)	FORMAT ( `string1`, `string2`, `string3` ) where `string1` is the format `string2` and `string3` are substitute strings (optional)
P	Returns the value of a property	P ( `string` ) where `string` is the property name
STRINGS	Returns the translated value of a string	STRINGS ( `string` ) where `string` is the string name
USERNAME	Returns the current user name	USERNAME ( )
V	Returns the value of a variable	V ( `string` ) where `string` is the variable name

DataView functions


Operator	Description	Expression
AVG	Returns the average of a property where optionally all rows in the group are aggregated, or the group is ignored and all rows are aggregated	AVG ( `string1`, `string2` ) where `string1` is the property name `string2` is use all rows (optional)
AVGEXP	Returns the average of an expression where optionally all rows in the group are aggregated, or the group is ignored and all rows are aggregated	AVGEXP (`string1`,`string2`,`string3`) where `string1` is the idoAlias that identifies the IDO within the DataView. `string2` is the expression to be evaluated for each row in the collection. `string3` is use all rows (optional).
CNT	Returns the count of non-null or non-blank values a property where optionally all rows in the group are aggregated, or the group is ignored and all rows are aggregated	CNT ( `string1`, `string2` ) where `string1` is the property name `string2` is use all rows (optional)
CNTEXP	Returns the count of non-null or non-blank values of an expression where optionally all rows in the group are aggregated, or the group is ignored and all rows are aggregated	CNTEXP (`string1`,`string2`,`string3`) where `string1` is the idoAlias that identifies the IDO within the DataView. `string2` is the expression to be evaluated for each row in the collection. `string3` is use all rows (optional).
DATAVIEWPROP	Returns the value of a DataView property at the same level of the DataView as the expression	DATAVIEWPROP ( `string` ) where `string` is the property name
DATAVIEWEXP	Returns the value of another expression column at the same level of the DataView as the expression	DATAVIEWEXP ( `string` ) where `string` is the name of another expression column
MAXAGG	Returns the maximum value of a property where optionally all rows in the group are aggregated, or the group is ignored and all rows are aggregated Note: The value can be a number, date, or string depending on the data type of the property.	MAXAGG ( `string1`, `string2` ) where `string1` is the property name `string2` is use all rows (optional)
MAXEXP	Returns the maximum value of an expression where optionally all rows in the group are aggregated, or the group is ignored and all rows are aggregated Note: The value can be a number, date, or string depending on the data type of the expression.	MAXEXP (`string1`,`string2`,`string3`) where `string1` is the idoAlias that identifies the IDO within the DataView. `string2` is the expression to be evaluated for each row in the collection. `string3` is use all rows (optional).
MINAGG	Returns the minimum value of a property where optionally all rows in the group are aggregated, or the group is ignored and all rows are aggregated Note: The value can be a number, date, or string depending on the data type of the property.	MINAGG ( `string1`, `string2` ) where `string1` is the property name `string2` is use all rows (optional)
MINEXP	Returns the minimum value of an expression where optionally all rows in the group are aggregated, or the group is ignored and all rows are aggregated Note: The value can be a number, date, or string depending on the data type of the expression.	MINEXP (`string1`,`string2`,`string3`) where `string1` is the idoAlias that identifies the IDO within the DataView `string2` is the expression to be evaluated for each row in the collection. `string3` is use all rows (optional)
SUM	Returns the summation of a property where optionally all rows in the group are aggregated, or the group is ignored and all rows are aggregated	SUM ( `string1`, `string2` ) where `string1` is the property name `string2` is use all rows (optional)
SUMEXP	Returns the summation of an expression where optionally all rows in the group are aggregated, or the group is ignored and all rows are aggregated	SUMEXP (`string1`,`string2`,`string3`) where `string1` is the idoAlias that identifies the IDO within the DataView. `string2` is the expression to be evaluated for each row in the collection. `string3` is use all rows (optional).