Expression functions and operators

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

Note: All parameters in the syntax statements are required unless noted otherwise.

Math operators


Operator	Description	Syntax/Examples
NEGATE	This operator returns the opposite value of a number: If a number is positive, this returns a negative value. If the number is negative, this returns a positive value.	`-( number )` or `-( number1 + number2 )`
+	This operator returns the sum of two numbers or concatenation of two strings. Note: For date values, use the DATEADD function instead of this one.	`( value1 + value2 )` where `value` is a number or string.
-	This operator returns the difference or negation of two numbers. Note: For date values, use the DATEDIFF function instead of this one.	`( number1 - number2 )`
*	This operator returns the product of two numbers.	`( number1 * number2 )`
/	This operator returns the quotient of two numbers.	`( number1 / number2 )`
%	This operator returns the remainder left after dividing two numbers.	`( number1 % number2 )`

Math functions


Operator	Description	Syntax/Examples
ABS	This function returns the absolute value of a number.	`ABS( number )` Example: ABS( -35 ) returns: 35
CEILING	This function returns the least integer that is greater than or equal to the value of a number.	`CEILING( number )` Example: CEILING( 26.75 ) returns: 27
FLOOR	This function returns the greatest integer that is less than or equal to the value of a number.	`FLOOR( number )` Example: FLOOR( -3.67 ) returns: 4
MAX	This function returns the greater of two numbers.	`MAX( number1, number2 )` Example: MAX( 8, 11 ) returns: 11
MIN	This function returns the lesser of two numbers.	`MIN( number1, number2 )` Example: MIN( -13, -15 ) returns: -15
POWER	This function returns the value of a number raised to a power.	`POWER( number1, number2 )` where: `number1` is the value. `number2` is the exponent. Example: POWER( 2, 3 ) returns: 8
ROUND	This function 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. Example: ROUND( 12.3684, 2 ) returns: 12.3700
SIGN	This function returns 1 or -1 to indicate positive or negative value of a number.	`SIGN( number )` Example: SIGN( -35 ) returns: -1
SQRT	This function returns the square root of a number.	`SQRT( number )` Example: SQRT( 49 ) returns 7
TRUNC	This function returns the value of a number truncated at the specified number of decimal places.	`TRUNC( number1, number2 )` where: `number1` is the specified value. `number2` is the number of decimal places to display after being truncated. Example: TRUNC( 345.156, 1 ) returns: 345.1

Comparison operators


Operator	Description	Syntax/Examples
== or =	This operator compares two values and returns True if they are equal or False if they are not.	`( value1 == value2 )` where `valueX` is a number, Boolean value, or string. Examples: ( 1145 == 1145 ) returns: True ( "string1" = "string2" ) returns: False
>=	This operator compares two values and returns True if the first value is greater than or equal to the second value, and False if the first value is less than the second.	`( value1 >= value2 )` where `valueX` is a number, Boolean value, or string. Examples: ( -179 >= -183 ) returns: True ( "abc" >= "def" ) returns: False
<=	This operator compares two values and returns True if the first value is less than or equal to the second value and False if the first value is greater.	`( value1 <= value2 )` where `valueX` is a number, Boolean value, or string. Examples: ( 179 <= 183 ) returns: True ( "def" <= "abc" ) returns: False
>	This operator compares two values and returns True if the first value is greater than the second value and False if it is equal or less than.	`( value1 > value2 )` where `valueX` is a number, Boolean value, or string.
<	This operator compares two values and returns True if the first value is less than the second value and False if it is equal or greater than.	`( value1 < value2 )` where `value` is a number, Boolean value, or string.
!= or <>	This operator compares two values and returns True if they are not equal and False if they are equal.	`( value1 != value2 )` where `valueX` is a number, Boolean value, or string.

Logical operators


Operator	Description	Syntax/Examples
AND or &&	This operator logically ANDs two Boolean values.	`( BooleanValue1 AND BooleanValue2 )` `( BooleanValue1 && BooleanValue2 )`
NOT or !	This operator tests whether a value in an expression is negative or a "NOT" value.	`NOT( BooleanValue )` `!( BooleanValue )` Example: `IF( !ISEMPTY(P( 'SerNum' )), P('SerNum'), P('Description') )` This example checks to see whether the Serial Number property value is empty before using it. If it is not empty, it uses the Serial Number value. If the Serial Number property value IS empty, it uses the value of the Description.
OR or \|\|	This operator logically ORs two Boolean values.	`( BooleanValue1 OR BooleanValue2 )` `( BooleanValue1 \|\| BooleanValue2 )`

Logical functions


Operator	Description	Syntax/Examples
CONTAINS	If the string being sought is found within the string to be searched, this function returns True. If the string is not found, this functions returns False.	`CONTAINS( string1, string2 )` where: `string1` is the string to be searched. `string2` is the string being sought.
IF	This function evaluates a condition and returns a value based on the 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 the condition returns True. `value3` is the result if the condition returns False.
ISEMPTY	If the string is empty, this functions returns True. If the string is not empty, this function returns False.	`ISEMPTY (string)`

Text functions


Operator	Description	Syntax/Examples
CONCAT	This function concatenates two strings and returns the result as a single string.	`CONCAT( string1, string2 )` Example: CONCAT( "My", "example" ) returns: Myexample.
INSTR or INDEXOF	Using a 1-based index, this function returns the starting index number of a search string within the string being searched. If the search string is not found within the value, returns 0.	`INSTR( string1, string2 )` `INDEXOF( string1, string2 )` where: `string1` is the string being searched. `string2` is the search string. Examples: INSTR( “abcdef”, “cde” ) returns: 3. INDEXOF( “abcdef”, “efg” ) returns: 0 (not found).
LEFT	This function returns the first part of a string, determined by the number of characters to count from the beginning of the string.	`LEFT( string, number )` where: `string` is the specified value. `number` is the number of characters. Example: LEFT( "abcdefghijkl", 6 ) returns: abcdef.
LEN or LENGTH	This function returns the length of a string, expressed as the number of characters.	`LEN( string )` `LENGTH( string )` Example: LEN( "What a fine day!" ) returns: 16.
LOWER	This function converts all characters in a string to lower case.	`LOWER( string )` Example: LOWER( "My name is Charlie Chan." ) returns: my name is charlie chan.
LTRIM	This function returns a string in which any "white space" is removed from the start of the string. Note: Quotation marks are inserted to indicate that the "white space" was removed.	`LTRIM( string )` Example: LTRIM( " Hello, world!" ) returns: ""Hello world!
REPLACE	This function returns a string in which all occurrences of a search string are replaced by the replacement string.	`REPLACE( string1, string2, string3 )` where: `string1` is the string being searched. `string2` is the search string. `string3` is the replacement string. Example: REPLACE( "Today is a miserable day!", "miserable", "wonderful" ) returns: Today is a wonderful day!
RIGHT	This function returns a part of a string, determined by the number of characters from the end of the string and counting backwards.	`RIGHT( string, number )` where: `string` is the original string. `number` is the number of characters to include in the returned string. Example: RIGHT( "abcdefghijklm", 5 ) returns: ijklm.
RTRIM	This function returns a string in which any "white space" is removed from the end of the string. Note: Quotation marks are inserted to indicate that the "white space" was removed.	`RTRIM( string )` Example: RTRIM( "Hello, world! " ) returns: Hello, world!
SUBSTRING	This function returns a part of a string, using a 1-based starting index and optionally the number of characters.	`SUBSTRING( string, number1, number2 )` where: `string` is the original string. `number1` is the 1-based starting index number (the point at which the substring is to start). `number2` is the number of characters to include in the substring (optional). If this is not included in the function statement, the entire string from the starting index on is included. Examples: SUBSTRING( "My name is Charlie Chan.", 11, 7 ) returns: Charlie SUBSTRING( "My name is Charlie Chan.", 11 ) returns: Charlie Chan.
TRIM	This function returns a string in which any "white space" is removed from both the start and the end of the string. Note: Quotation marks are inserted to indicate that the "white space" was removed.	`TRIM( string )` Example: TRIM( " Hello, world! " ) returns: Hello, world!
UPPER	This function converts all characters in a string to upper case.	`UPPER( string )` Example: UPPER( "My name is Charlie Chan." ) returns: MY NAME IS CHARLIE CHAN.

Accumulator functions


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

Aggregate functions


Operator	Description	Syntax/Examples
AVG	This function returns the average of values for a property, optionally grouped or filtered by a property group.	`AVG (string1, string2)` where: `string1` is the name of the property. `string2` is the name of the property group (optional).
AVGEXP	This function returns the average of values for an expression, optionally grouped of filtered by a property group.	`AVGEXP (string1, string2, string3)` where: `string1` is the collectionID of the collection in which the expression is to be evaluated. `string2` is the expression to be evaluated for each row in the collection. `string3` is the name of the property group (optional).
CNT	This function returns a count of the non-null or non-blank values for a property, optionally grouped or filtered by a property group.	`CNT (string1, string2)` where: `string1` is the name of the property. `string2` is the name of the property group (optional).
CNTEXP	This function returns a count of the non-null or non-blank values for an expression, optionally grouped or filtered by a property group.	`CNTEXP (string1, string2, string3)` where: `string1` is the collectionID of the collection in which the expression is to be evaluated. `string2` is the expression to be evaluated for each row in the collection. `string3` is the name of the property group (optional).
MAXAGG	This function returns the aggregated maximum value of a property, optionally grouped or filtered by a property group. 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 name of the property. `string2` is the name of the property group (optional).
MAXEXP	This function returns the aggregated maximum value of an expression, optionally grouped or filtered by a property group. 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 in which the expression is to be evaluated. `string2` is the expression to be evaluated for each row in the collection. `string3` is the name of the property group (optional).
MINAGG	This function returns the aggregated minimum value of a property, optionally grouped or filtered by a property group. 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 name of the property. `string2` is the name of the property group (optional).
MINEXP	This function returns the aggregated minimum value of an expression, optionally grouped or filtered by a property group. 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 in which the expression is to be evaluated. `string2` is the expression to be evaluated for each row in the collection. `string3` is the name of the property group (optional).
SUM	This function returns a summation of the values for a property, optionally grouped or filtered by a property group.	`SUM (string1, string2)` where: `string1` is the name of the property. `string2` is the name of the property group (optional).
SUMEXP	This function returns a summation of the values for an expression, optionally grouped or filtered by a property group.	`SUMEXP (string1, string2, string3)` where: `string1` is the collectionID of the collection in which the expression is to be evaluated. `string2` is the expression to be evaluated for each row in the collection. `string3` is the name of the property group (optional).

Date functions


Operator	Description	Syntax/Examples
CURDATE	This function returns the current date, formatted according to the culture being used in the client.	`CURDATE ( )`
CURDATETIME	This function returns the current date and time, formatted according to the culture being used in the client.	`CURDATETIME ( )`
DATE	This functions returns a date that contains the year, month, and day.	`DATE (year, month, day)` All three date parameters are required. Example: `DATE(V(YearVar), C(MonthEdit), V(DayVar))` where: `YearVar` is a variable containing the year. `MonthEdit` is the value in the Edit Month component. `DarVar` is a variable containing the number of the day. This example might return something like: 10/24/2023 08:26:00 AM
DATEADD	This function returns a new date based on an interval that is added to the date.	`DATEADD (string, number, date)` where: `string` is the interval to be added to the date. This value must be enclosed in single quotation marks. `number` is the number of intervals to add to the date. This can be a positive value (for future dates) or a negative value (for past dates). `date` is the date that the interval is being applied to. This can be a literal value enclosed in single quotation marks. It could also be a Mongoose substitution such as P(CoDate), the property containing a date, or C(ComponentName), V(VariableName). All parameters must be enclosed in single quotation marks; for example: 'year' These are the valid date/time intervals you can use (for the `string`): Year: year, yyyy, yy Month: month, mm, m Day: day, dd, d Quarter: quarter, qq, q Day of the year: dayofyear, dy, y Week: week, ww, wk Day of the week: weekday, dw, w Hour: hour, hh Minute: minute, mi, n Second: second, ss, s Millisecond: millisecond, ms Example: This example adds two days to the value of a date in the CoDate property: `DATEADD('day', 2, P(CoDate))`
DATEDIFF	This function returns the difference between two dates, based on an interval.	`DATEDIFF (string, date1, date2)` where: `string` is the date interval. For valid values for this parameter, see the previous entry in this table. This value must be enclosed in single quotation marks. `date1` is the starting date. `date2` is the date being compared and treated. Dates can be literal values enclosed in single quotation marks. They could also be a Mongoose substitution such as P(CoDate), the property containing a date, or C(ComponentName), V(VariableName). Example: This example gets the difference, in days, between an order date and the due date: `DATEDIFF('day', P(CoDate), P(DueDate))`
DATEPART	This function returns a part of the date, based on a specified date/time part.	`DATEPART (string, date)` where: `string` is the part of the date to retrieve. The "parts" you can retrieve are the same as the date intervals, as described in the DATEADD entry. `date` is the date from which to extract the part's value. This can be a literal date in single quotes or a substituted value like those described in the DATEADD entry. Example: In this example, if the CoDate property had a value of 01/15/2023, the return value would be 15. `DATEPART('day', P(CoDate))`
ISOWEEKNUM	This function returns the ISO 8601 week number of the date. Note: The ISO 8691 standard uses the week containing the first Thursday of the year as the first week of the year.	`ISOWEEKNUM (date)`

Mongoose-specific functions


Operator	Description	Syntax/Examples
C	This function returns the value of a component.	`C (string)` where `string` is the name of the component.
FORMAT	This function returns the formatted or translated value of a string by using substitution strings.	`FORMAT (string1, string2, string3)` where: `string1` is the format to use. `string2` and `string3` are substitute strings (optional)
P	This function returns the value of a property	`P (string)` where `string` is the name of the property.
STRINGS	This function returns the translated value of a string.	`STRINGS (string)` where `string` is the identifier or source of the string. Example: `STRINGS(sItem)` might return Artículo based on a User Preferences language setting of Spanish and a Strings table for Spanish existing in the database.
USERNAME	This function returns the username of the current user.	`USERNAME ( )`
V	This function returns the value of a variable.	`V (string)` where `string` is the name of the variable.

DataView-specific functions


Operator	Description	Syntax/Examples
AVG	This function returns the average value of a property where, optionally, all rows in the group are aggregated. If the group is ignored and all rows are aggregated, this function returns the average of all rows.	`AVG (string1, string2)` where: `string1` is the name of the property. `string2` (optional) is the name of a property group. If `string2` is not specified, all rows are used.
AVGEXP	This function returns the average value of an expression where, optionally, all rows in the group are aggregated If the group is ignored and all rows are aggregated, this function returns the average of all rows.	`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 an instruction to Use All Rows - Ignore Groups (Optional).
CNT	This function returns a count of non-null or non-blank values for a property where, optionally, all rows in the group are aggregated. If the group is ignored and all rows are aggregated, this function returns a count all rows.	`CNT (string1, string2)` where: `string1` is the name of the property. `string3` is an instruction to Use All Rows - Ignore Groups (Optional).
CNTEXP	This function returns a count of non-null or non-blank values for an expression where, optionally, all rows in the group are aggregated. If the group is ignored and all rows are aggregated, this function returns a count of all rows.	`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 an instruction to Use All Rows - Ignore Groups (Optional).
DATAVIEWPROP	This function returns the value of a DataView property at the same level of the DataView as the expression.	`DATAVIEWPROP (string)` where `string` is the name of the property.
DATAVIEWEXP	This function 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 the other expression column.
MAXAGG	This function returns the maximum value of a property where, optionally, all rows in the group are aggregated. If the group is ignored and all rows are aggregated, this function returns the maximum value from all rows. 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 name of the property. `string3` is an instruction to Use All Rows - Ignore Groups (Optional).
MAXEXP	This function returns the maximum value of an expression where, optionally, all rows in the group are aggregated. If the group is ignored and all rows are aggregated, this function returns the maximum value from all rows. 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 an instruction to Use All Rows - Ignore Groups (Optional).
MINAGG	This function returns the minimum value of a property where, optionally, all rows in the group are aggregated. If the group is ignored and all rows are aggregated, this function returns the maximum value from all rows. 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 name of the property. `string3` is an instruction to Use All Rows - Ignore Groups (Optional).
MINEXP	This function returns the minimum value of an expression where, optionally, all rows in the group are aggregated. If the group is ignored and all rows are aggregated, this function returns the maximum value from all rows. 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 an instruction to Use All Rows - Ignore Groups (Optional).
SUM	This function returns a summation of values for a property where, optionally, all rows in the group are aggregated. If the group is ignored and all rows are aggregated, this function returns a summation of all rows.	`SUM (string1, string2)` where: `string1` is the name of the property. `string3` is an instruction to Use All Rows - Ignore Groups (Optional).
SUMEXP	This function returns a summation of values for an expression where, optionally, all rows in the group are aggregated. If the group is ignored and all rows are aggregated, this function returns a summation of all rows.	`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 an instruction to Use All Rows - Ignore Groups (Optional).