Page History

...

Category	Function	Description
Logic	AND	Returns TRUE if all of its arguments are TRUE
OR	Returns TRUE if any argument is TRUE
TRUE	Returns the boolean value TRUE
FALSE	Returns the boolean value FALSE
NOT	Reverses the boolean value of its argument
UNKNOWN	Returns the logical value unknown
Text	CONCATENATE	Joins several text items into one text item
JOIN	Joins several text items into one text item, while placing a separator character between them
MATCH	Checks if a string completely matches a regular expression
FIND	Returns found characters if a pattern is found within text value
FIRST	Returns the first position where one text value is found within another
LAST	Returns the last position where one text value is found within another
Statistics	AVERAGE	Returns the average of its arguments
MIN	Returns the minimum of a specified value in a list or collection
MAX	Returns the maximum of a specified value in a list or collection
COUNT	Counts the number of instances in a specified collection
Date and Time	DATETIME	Converts a date in the form of text or a date object to a dateTime object
DATE	Converts a date in the form of text or a dateTime object to a date object
ADD PERIOD TO DATE	Adds a specified period (day, month etc.) to a date
ADD PERIOD TO DATETIME	Adds a specified period (day, month etc.) to a date
SUBTRACT PERIOD FROM DATE	Subtracts a specified period (day, month etc.) from a date and time
SUBTRACT PERIOD FROM DATETIME	Subtracts a specified period (day, month etc.) from a date and time
PERIOD BETWEEN	Returns the period (days, months etc.) between two dates, two dateTimes or a date-dateTime combination
YEAR	Returns the number of the year
MONTH	Returns the number of the month
DAY	Returns the number of the day in the month
HOUR	Returns the number of hours in a dateTime object
MINUTE	Returns the number of minutes in a dateTime object
SECOND	Returns the number of seconds in a dateTime object
TODAY	Returns today’s date
NOW	Returns the current date and time
Collection	SIZE collection	Returns the size of a collection
UNIQUE collection	Filters duplicate instances in a collection
SUBSET OF	Returns a subset of a collection
UNION	Combines two collections into one new collection
INTERSECTION	Returns the intersection of two collections
Math	ADD	Adds two numbers
SUM	Sums the specified values in a collection
SUBTRACT	Subtracts two numbers
DIVIDE	Divides two numbers
DIV	Returns the integer portion of a division
MOD	Returns the remainder from division
ROUND	Rounds a number to a specified number of digits
ROUND TO MULTIPLE	Rounds a number to the nearest integer or to the nearest multiple of significance
MULTIPLY	Multiplies two numbers
PRODUCT	Multiplies the specified values in a collection
POWER	Returns the result of a number raised to a power
LESS	Compares two number values and returns TRUE when one is smaller than the other
LESS OR EQUAL	Compares two number values and returns TRUE when one is equal or smaller than the other
GREATER	Compares two number values and returns TRUE when one is greater than the other
GREATER OR EQUAL	Compares two number values and returns TRUE when one is equal to or greater than the other
EQUAL	Compares two values and returns TRUE when they are equal
NOT EQUAL	Compares two number values and returns TRUE when they are not equal
List	EXISTS	Returns TRUE if there is an instance that matches the specifications
ALL	Returns a collection of all instances of a specified entity
COLLECT FROM	Returns a collection for a specified entity
COLLECT FROM WHERE	Returns a collection for a specified entity that matches the specified conditions
COLLECT FROM NAMED	Returns a collection for a specified entity
COLLECT FROM NAMED WHERE	Returns a collection for a specified entity that matches the specified conditions
UNPACK	Returns the content of a single-valued list
Conversion	TEXT	Converts a value type to string
INTEGER	Converts a value type to integer
NUMBER	Converts a value type to number
CURRENCY	Converts a value type to currency
PERCENTAGE	Converts a value type to percentage
DATE	Converts a value type to date
DATETIME	Converts a value type to datetime
BOOLEAN	Converts a value type to boolean
More functions	ISUNKNOWN	Replaces an unknown value with a specified value
IS A
TYPE OF
AUTHORIZATIONROLES
CHANNEL
LANGUAGE
PAGE
PRIVILEGES
EVENT

Math

ADD

Use the '+' (plus) sign to add values of some type of number.

Syntax

number1 + number2 + ...

number1 - First number or attribute of some type of number to add.
number2 - Second number or attribute of some type of number to add.

Return types

number + number = number
integer + integer = integer
currency + currency = currency
percentage + percentage = percentage
number + integer = number
number + currency = currency
number + percentage = percentage
integer + currency = currency
integer + percentage = percentage

Examples

Suppose the following data model. Attribute Child.pocketMoney is of type currency and has a value of 2.50. Attribute Bonus.standard is of type percentage and has a value of 5.00.

Child.pocketMoney + 3.00 = 5.50 of type currency
Child.pocketMoney + 3 = 5.50 of type currency
2.50 + 3 = 5.5000 of type number
100 + Bonus.standard = 105.00 of type percentage
Child.pocketMoney + Bonus.standard results in an error

SUM

This function adds the values of a collection of numbers.

Syntax

SUM(collection)

collection - A collection of number values.

Return types

number
integer
currency
percentage

Examples

Suppose the following data model.

...

SUM(COLLECT Person.age FROM ALL Person) results in 152
SUM(COLLECT Person.age FROM ALL Person WHERE (Person.gender = “m”)) results in 96
SUM([1,2,3,4,5,6,7,8,9,10]) results in 55

SUBTRACT

Use the '-' (minus) sign to subtract values of some type of number.

Syntax

number1 - number2 - ...

number1 - Number or attribute of some type of number to subtract other number values from.
number2 - Number or attribute of some type of number to subtract.

Return types

number - number = number
integer - integer = integer
currency - currency = currency
percentage - percentage = percentage
number - integer = number
number - currency = currency
number - percentage = percentage
integer - number = number
integer - currency = currency
integer - percentage = percentage
currency - number = currency
currency - integer = currency
percentage - number = percentage
percentage - integer = percentage

Examples

Suppose the following data model. Attribute Child.pocketMoney is of type currency and has a value of 2.50. Attribute Bonus.standard is of type percentage and has a value of 5.00.

Child.pocketMoney - 1.00 = 1.50 of type currency
Child.pocketMoney - 3 = -0.50 of type currency
5.00 - 3 = 2.0000 of type number
100 - Bonus.standard = 95.00 of type percentage
Child.pocketMoney - Bonus.standard results in an error

DIVIDE

Use the '/' (forward slash) sign to divide values of some type of number.

Syntax

number1 / number2 / ...

number1 - Dividend; number or attribute of some type of number to be divided by other number values.
number2 - Divisor; number or attribute of some type of number to divide by.

Return types

number / number = number
integer / integer = number
currency / currency = number
percentage / percentage = number
number / integer = number
integer / number = number
currency / number = currency
currency / integer = currency
percentage / number = percentage
percentage / integer = percentage

Examples

Suppose the following data model. Attribute Child.pocketMoney is of type currency and has a value of 2.50. Attribute Bonus.standard is of type percentage and has a value of 5.00.

Child.pocketMoney / 1.00 = 2.50 of type currency
Child.pocketMoney / 2 = 1.25 of type currency
5.00 / 3 = 1.6667 of type number
Bonus.standard / 5 = 1.00 of type percentage

DIV

This function returns the integer portion of a division.

Syntax

number1 DIV number2

number1 - Dividend; number or attribute of some type of number to be divided by another number value.
number2 - Divisor; number or attribute of some type of number to divide by.

Return type

integer

Examples

Suppose the following data model. Attribute Child.pocketMoney is of type currency and has a value of 2.50. Attribute Bonus.standard is of type percentage and has a value of 5.00.

Child.pocketMoney DIV 1.00 = 2 of type integer
Bonus.standard DIV 5 = 1 of type integer
6 DIV 4.50 = 1 of type integer

MOD

This function returns the remainder of a division.

Syntax

number1 % number2

number1 - Dividend; number or attribute of some type of number to be divided by another number value.
number2 - Divisor; number or attribute of some type of number to divide by.

Return types

number % number = number
integer % integer = integer
currency % currency = currency
percentage % percentage = percentage
number % integer = number
integer % number = number
currency % number = currency
currency % integer = currency
percentage % number = percentage
percentage % integer = percentage

Examples

Suppose the following data model. Attribute Child.pocketMoney is of type currency and has a value of 2.50. Attribute Bonus.standard is of type percentage and has a value of 5.00.

Child.pocketMoney % 0.20 = 0.10 of type currency
Child.pocketMoney % 1 = 0.50 of type currency
Bonus.standard % 3 = 2.00 of type percentage
9 % 2.50 = 1.5000 of type number

ROUND

The ROUND function rounds a number value to a specified number of decimal places.

Syntax

ROUND number [UP | DOWN] TO POSITION position

number - Number or attribute of some type of number to be rounded.
UP or DOWN - The direction of rounding. The direction DOWN points to zero for both positive and negative values, UP points away from zero. If no direction is specified, rounding will be done to the nearest integer (half-way values will be rounded up).
position - The rounding position as integer. A positive integer represents the place value of the digit after the decimal point. Zero and negative integers represent the place value of the digit before the decimal point. Place values: [-n]..[-3][-2][-1][0].[1][2][3]..[n]

Return types

number
integer
currency
percentage

Examples

ROUND 12345.23 TO POSITION -2 = 12300.0000
ROUND 12345.23 TO POSITION 1 = 12345.2000
ROUND 12345.23 UP TO POSITION -2 = 12400
ROUND 12345.23 UP TO POSITION 1 = 12345.3000
ROUND 12345.23 DOWN TO POSITION -2 = 12300.0000
ROUND 12345.23 DOWN TO POSITION 1 = 12345.2000
ROUND 12345.23 UP TO POSITION -5 = 100000.0000
ROUND 12345.23 TO POSITION -5 = 0.0000

ROUND TO MULTIPLE

The ROUND TO MULTIPLE function rounds a number value to a specified multiple.

Syntax

ROUND number [UP | DOWN] TO MULTIPLE multiple

number - Number or attribute of some type of number to be rounded.
UP or DOWN - The direction of rounding. The direction DOWN points to zero for both positive and negative values, UP points away from zero. If no direction is specified, rounding will be done to the nearest multiple (half-way values will be rounded up).
multiple - The multiple to be rounded to. This can be both a positive and negative value of any number type.

Return types

number
integer
currency
percentage

Examples

ROUND 12345.23 TO MULTIPLE 500 = 12500.0000 of type number
ROUND 12345.23 TO MULTIPLE 5 = 12345.0000 of type number
ROUND 12345.23 TO MULTIPLE 0.50 = 12345.0000 of type number

MULTIPLY

Use the '*' (asterisk) sign to multiply values of some type of number.

Syntax

number1 * number2 * ...

number1 - First factor; number or attribute of some type of number.
number2 - Second factor; number or attribute of some type of number.

Return types

number * number = number
integer * integer = number
currency * currency = number
percentage * percentage = number
number * integer = number
integer * number = number
currency * number = currency
currency * integer = currency
percentage * number = percentage
percentage * integer = percentage

Examples

Suppose the following data model. Attribute Child.pocketMoney is of type currency and has a value of 2.50. Attribute Bonus.standard is of type percentage and has a value of 5.00.

Child.pocketMoney * 1.00 = 2.50 of type currency
Child.pocketMoney * 2 = 5.00 of type currency
5.00 * 3 = 15.0000 of type number
Bonus.standard * 5 = 25.00 of type percentage

PRODUCT

This function returns the product of the values of a collection of numbers.

Syntax

PRODUCT(collection)

collection - A collection of number values.

Return types

number
integer
currency
percentage

Examples

Suppose the following data model.

...

PRODUCT(COLLECT Person.score FROM ALL Person) results in 10327.5000
PRODUCT(COLLECT Person.score FROM ALL Person WHERE (Person.score < 8)) results in 135.0000
PRODUCT([3.5, 2.2]) results in 7.7000 of type number

POWER

Use the '**' operator to raise a number to a power.

Syntax

base ** exponent

base - Base; number or attribute of some type of number.
exponent - Exponent, power; number or attribute of some type of number.

Return types

number ** number = number
integer ** integer = number
number ** integer = number
integer ** number = number
currency ** number = currency
currency ** integer = currency
percentage ** number = percentage
percentage ** integer = percentage

Examples

Suppose the following data model. Attribute Child.pocketMoney is of type currency and has a value of 2.50. Attribute Bonus.standard is of type percentage and has a value of 5.00.

Child.pocketMoney ** 1.00 = 2.50 of type currency
Child.pocketMoney ** 2 = 6.25 of type currency
5.00 ** 3 = 125.0000 of type number
Bonus.standard ** 5 = 3125.00 of type percentage

LESS

The operator '<' compares two values and returns the boolean value TRUE if the left value is smaller than the right value and FALSE otherwise.

Syntax

value1 < value2

value1 - Number, date or attribute of some type of number or date.
value2 - Number, date or attribute of some type of number or date.

Return type

boolean

Examples

1 < 2 returns TRUE
1.0 < 1.0 returns FALSE
DATE(2012,1,1) < TODAY returns TRUE
2000 < YEAR(TODAY) returns TRUE

LESS OR EQUAL

The operator '<=' compares two values and returns the boolean value TRUE if the left value is smaller than or equal to the right value and FALSE otherwise.

Syntax

value1 <= value2

value1 - Number, date or attribute of some type of number or date.
value2 - Number, date or attribute of some type of number or date.

Return type

boolean

Examples

1 <= 2 returns TRUE
1.0 <= 1.0 returns TRUE
DATE(2012,1,1) <= TODAY returns TRUE
4000 <= YEAR(TODAY) returns FALSE

GREATER

The operator '>' compares two values and returns the boolean value TRUE if the left value is greater than the right value and FALSE otherwise.

Syntax

value1 > value2

value1 - Number, date or attribute of some type of number or date.
value2 - Number, date or attribute of some type of number or date.

Return type

boolean

Examples

1 > 2 returns FALSE
1.0 > 1.0 returns FALSE
DATE(2012,1,1) > TODAY returns FALSE
4000 > YEAR(TODAY) returns TRUE

GREATER OR EQUAL

The operator '>=' compares two values and returns the boolean value TRUE if the left value is greater than or equal to the right value and FALSE otherwise.

Syntax

value1 >= value2

value1 - Number, date or attribute of some type of number or date.
value2 - Number, date or attribute of some type of number or date.

Return type

boolean

Examples

1 >= 2 returns FALSE
1.0 >= 1.0 returns TRUE
DATE(2012,1,1) >= TODAY returns FALSE
4000 >= YEAR(TODAY) returns TRUE

EQUAL

The operator '=' compares two values and returns the boolean value TRUE if the left value is equal to the right value and FALSE otherwise.

Syntax

value1 = value2

value1 - Number, date or attribute of some type of number or date.
value2 - Number, date or attribute of some type of number or date.

For value list, the '=' operator is implemented as SUBSET OF. To test if two value lists are equal you could use the expression:

L1 SUBSET OF L2 AND L2 SUBSET OF L1

Return type

boolean

Examples

1 = 2 returns FALSE
1.0 = 1.0 returns TRUE
DATE(2012,1,1) = TODAY returns FALSE
5 + 5 = 10 returns TRUE

NOT EQUAL

The operator '!=' compares two values and returns the boolean value TRUE if the left value is not equal to the right value and FALSE otherwise.

Syntax

value1 != value2

value1 - Number, date or attribute of some type of number or date.
value2 - Number, date or attribute of some type of number or date.

Return type

boolean

Examples

1 != 2 returns TRUE
1.0 != 1.0 returns FALSE
DATE(2012,1,1) != TODAY returns TRUE
5 + 5 != 10 returns FALSE

List

EXISTS

This function determines whether an instance of a specified entity exists meeting certain criteria.

Syntax

EXISTS entity [WHERE condition]

entity - Entity to search for.
condition - Boolean expression that represents the criterion the instance has to meet.

Return type

boolean

Examples

Suppose the following data model.

Person.name	Person.gender	Person.age
“Kim”	“f”	23
“Rick”	“m”	35
“Bob”	“m”	42
“John”	“m”	19
“Mary”	“f”	33

EXISTS Person returns TRUE
EXISTS Person WHERE (Person.age < 18) returns FALSE
EXISTS Person WHERE (Person.gender = “m” AND Person.age > 35) returns TRUE

...

Space shortcuts

Page tree

Versions Compared

Old Version 7

New Version 8

Key

Math

ADD

SUM

SUBTRACT

DIVIDE

DIV

MOD

ROUND

ROUND TO MULTIPLE

MULTIPLY

PRODUCT

POWER

LESS

LESS OR EQUAL

GREATER

GREATER OR EQUAL

EQUAL

NOT EQUAL

List

EXISTS