Page History
Table of contents
Children Display | ||
---|---|---|
|
- Text Substitution Language: TSL reference
Visual overview of Expression concepts
Tip | ||
---|---|---|
| ||
Basic documentation about functions is available in the expression editor in Blueriq Encore. Press CTRL + Shift + Space when the cursor is between the brackets of a function. This will show:
For example: |
Reference guide: functions and expressions
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 |
Statistics
AVERAGE
Use this function to determine the average (arithmetic mean) of a list or collection.
The AVG function has two syntax forms: the collection form and the list form. You can use the collection form to determine the average value of all occurances of an attribute. The list form is used to determine the average value of a set of different attributes.
Collection form syntax
AVG(attribute)
- attribute - An attribute of any type of number or date of which multiple instances can occur at runtime.
List form syntax
AVG([value1, value2, ...])
- [value1, value2, …] - A list of attributes, expressions or fixed values of any type of number or date. Use the square brackets to indicate that the arguments form a list of values.
Return types
- number
- integer
- currency
- percentage
- date
The function returns UNKNOWN if one or more values are UNKNOWN.
Examples
Suppose the following domain model.
...
If you want to determine the average bidding on the current residence:
- AVG(Residence.hasBiddings.amount)
If you want to determine the average amount of the appraisal value, the asking price and the mortgage of the current residence:
- AVG([Residence.appraisalValue, Residence.askingPrice, Residence.mortgage])
MIN
Use this function to determine the minimum value of a list or collection.
The MIN function has two syntax forms: the collection form and the list form. You can use the collection form to determine the minimum value of all occurrences of an attribute. The list form is used to determine the minimum value of a set of different attributes.
Collection form syntax
MIN(attribute)
- attribute - An attribute of any type of number or date of which multiple instances can occur at runtime.
List form syntax
MIN([value1, value2, ...])
- [value 1, value 2, …] - A list of attributes, expressions or fixed values of any type of number or date. Use the square brackets to indicate that the arguments form a list of values.
Examples
Suppose the following domain model.
...
If you want to determine the lowest bidding on the current residence:
- MIN(Residence.hasBiddings.amount)
If you want to determine the smallest amount within the appraisal value, the asking price and the mortgage:
- MIN([Residence.appraisalValue, Residence.askingPrice, Residence.mortgage])
MAX
Use this function to determine the maximum value of a list or collection.
The MAX function has two syntax forms: the collection form and the list form. You can use the collection form to determine the maximum value of all occurrences of an attribute. The list form is used to determine the maximum value of a set of different attributes.
Collection form syntax
MAX(attribute)
- attribute - An attribute of any type of number or date of which multiple instances can occur at runtime.
List form syntax
MAX([value1, value2, ...])
- [value 1, value 2, …] - A list of attributes, expressions or fixed values of any type of number or date. Use the square brackets to indicate that the arguments form a list of values.
Examples
Suppose the following domain model.
...
If you want to determine the highest bidding on the current residence:
- MAX(Residence.hasBiddings.amount)
If you want to determine the highest amount within the appraisal value, the asking price and the mortgage:
- MAX([Residence.appraisalValue, Residence.askingPrice, Residence.mortgage])
COUNT
Use this function to retrieve the number of instances for the specified entities or attributes.
Syntax
COUNT(collection)
- collection - A collection of attribute or entity instances.
Return type
- integer
Examples
Suppose the following data model.
...
...
- COUNT(ALL Person) results in 4
- COUNT(House.rooms) results in 3
- COUNT(ALL Person + ALL House) results in 5
- COUNT(COLLECT Person FROM ALL Person WHERE(Person.name = “Lisa”)) results in 1
- COUNT(? (= UNKNOWN)) results in UNKNOWN 1)
Date and Time
DATETIME
You can use this function to create2) a dateTime value out of integers or numbers 3).
Syntax
DATETIME(year, month, day, hour, minute, second)
- year - An integer value between and including 1 and 9999 that represents the year in the constructed date.
- month - An integer value between and including 1 and 12 that represents the month in the constructed date.
- day - An integer value between and including 1 and 31 that represents the day in the constructed date.
- hour - An integer value between and including 0 and 23 that represents the hours in the constructed time.
- minute - An integer value between and including 0 and 59 that represents the minutes in the constructed time.
- second - An integer value between and including 0 and 59 that represents the seconds in the constructed time.
Return type
- dateTime
Examples
- DATETIME(2010, 5, 25, 23, 00, 00) returns 25-5-2010 23:00:00
- DATETIME(-2010, 5, 25, 23, 00, 00) results in UNKNOWN
- DATETIME(2010, 5, 25, 45, 00, 00) results in UNKNOWN
DATE
You can use this function to create1) a date value out of integers or numbers2).
Syntax
DATE(year, month, day)
- year - An integer value between and including 1 and 9999 that represents the year in the constructed date.
- month - An integer value between and including 1 and 12 that represents the month in the constructed date.
- day - An integer value between and including 1 and 31 that represents the day in the constructed date.
Return type
- date
Examples
- DATE(2010, 5, 25) returns 25-5-2010
- DATE(-2010, 5, 25) results in UNKNOWN
- DATE(2010, 2, 29) results in an error
ADD PERIOD TO DATE
This function calculates a new date by adding a specified period to a specified date.
Syntax
date + number period
- date - The date that you want to add the specified period to.
- number - Specifies how may periods you want to add to the date.
- period - A keyword that specifies which time unit to add. Valid units are DAYS, WEEKS, MONTHS and YEARS.
Return type
- date
Examples
- 01-01-2015 + 1 DAYS equals 02-01-2015
- 01-01-2015 + 1 WEEKS equals 08-01-2015
- 01-01-2015 + 1 MONTHS equals 01-02-2015
- 01-01-2015 + 1 YEARS equals 01-01-2015
ADD PERIOD TO DATETIME
This function calculates a new dateTime by adding a specified period to a specified dateTime.
Syntax
dateTime + number period
- dateTime - The dateTime that you want to add the specified period to.
- number - Specifies how may periods you want to add to the dateTime.
- period - A keyword that specifies which time unit to add. Valid units are DAYS, WEEKS, MONTHS, YEARS, HOURS, MINUTES and SECONDS.
Return type
- dateTime
Examples
- 01-01-2015 13:23:04 + 1 DAYS equals 02-01-2015 13:23:04
- 01-01-2015 13:23:04 + 25 HOURS equals 02-01-2015 14:23:04
- 01-01-2015 13:23:04 + 60 SECONDS equals 01-01-2015 13:24:04
- 01-01-2015 13:23:04 + 1 MINUTES equals 01-01-2015 13:24:04
SUBTRACT PERIOD FROM DATE
This function calculates a new date by subtracting a specified period from a specified date.
Syntax
date – number period
- date - The date that you want to subtract the specified period from.
- number - Specifies how many periods you want to subtract from the date.
- period - A keyword that specifies which time unit to subtract. Valid units are DAYS, WEEKS, MONTHS and YEARS.
Return type
- date
Examples
- 01-01-2015 – 1 DAYS equals 31-12-2014
- 01-01-2015 – 1 WEEKS equals 25-12-2014
- 01-01-2015 – 1 MONTHS equals 01-12-2014
- 01-01-2015 – 1 YEARS equals 01-01-2014
SUBTRACT PERIOD FROM DATETIME
This function calculates a new dateTime by subtracting a specified period from a specified dateTime.
Syntax
dateTime – number period
- dateTime - The dateTime that you want to subtract the specified period from.
- number - Specifies how many periods you want to subtract from the dateTime.
- period - A keyword that specifies which time unit to subtract. Valid units are DAYS, WEEKS, MONTHS, YEARS, HOURS, MINUTES and SECONDS.
Return type
- dateTime
Examples
- 01-01-2015 13:23:04 – 1 HOURS equals 01-01-2015 12:23:04
- 01-01-2015 13:23:04 – 25 HOURS equals 31-12-2014 12:23:04
- 01-01-2015 13:23:04 – 60 SECONDS equals 01-01-2015 13:22:04
- 01-01-2015 13:23:04 – 1 MINUTES equals 01-01-2015 13:22:04
PERIOD BETWEEN
This function calculates the period between two date or dateTime objects. This function returns the number of full periods of the specified type between two dates or dateTimes.
Syntax 1)
period BETWEEN date1 AND date2
- period 2) - A keyword that specifies which time unit to return. Valid units are DAYS, WEEKS, MONTHS, YEARS, HOURS, MINUTES and SECONDS.
- date1 - The date or dateTime value that represents the start date.
- date2 - The date or dateTime value that represents the end date.
Return type
- integer
Examples
- DAYS BETWEEN DATE(2010, 1, 1) AND DATE(2010, 2, 1) equals 31
- DAYS BETWEEN DATE(2010, 5, 31) AND DATE(2010, 6, 1) equals 1
- DAYS BETWEEN DATE(2010, 5, 31) AND DATETIME(2010, 6, 1, 12, 0, 0) equals 1, since 1 day and 12 hours have elapsed
- DAYS BETWEEN DATETIME(2010, 1, 1, 14, 0, 0) AND DATETIME(2010, 1, 2, 13, 0, 0) equals 0, for only 23 hours have elapsed
- YEARS BETWEEN DATE(2009, 1, 1) AND DATE(2010, 1, 1) equals 1
- YEARS BETWEEN DATE(2010, 1, 1) AND DATE(2020, 1, 1) equals 10
- YEARS BETWEEN DATE(2010, 1, 1) AND DATE(2011, 5, 1) equals 1
- YEARS BETWEEN DATETIME(2010, 1, 1, 12, 0, 0) AND DATE(2011,1,1) equals 0, since 364 days and 12 hours have elapsed
YEAR
YEAR returns the year of a date or dateTime value. The year is returned as a number in the range 1-9999.
Syntax
YEAR(date | dateTime)
- date or dateTime - The date or dateTime value of which the year must be returned.
Return type
- integer
Examples
- YEAR(DATE(2015, 01, 01)) equals 2015
- YEAR(DATE(1978, 04, 11)) equals 1978
MONTH
MONTH returns the month of a date or dateTime value. The month is returned as a number in the range 1-12.
Syntax
MONTH(date | dateTime)
- date or dateTime - The date or dateTime value of which the month must be returned.
Return type
- integer
Examples
- MONTH(DATE(2015, 01, 01)) equals 1
- MONTH(DATE(1978, 04, 11)) equals 4
DAY
DAY returns the day of a date or dateTime value. The day is returned as a number in the range 1-31.
Syntax
DAY(date | dateTime)
- date or dateTime - The date or dateTime value of which the day must be returned.
Return type
- integer
Examples
- DAY(DATE(2015, 01, 01)) equals 1
- DAY(DATE(1978, 04, 11)) equals 11
HOUR
HOUR returns the hour of a dateTime value. The hour is returned as a number in the range 0-23.
Syntax
HOUR(dateTime)
- dateTime - The dateTime value of which the hour must be returned.
Return type
- integer
Examples
- HOUR(DATETIME(2015, 5, 25, 00, 00, 00)) equals 0
- HOUR(DATETIME(2015, 5, 25, 23, 00, 00)) equals 23
MINUTE
MINUTE returns the minute of a dateTime value. The minute is returned as a number in the range 0-59.
Syntax
MINUTE(dateTime)
- dateTime - The dateTime value of which the minute must be returned.
Return type
- integer
Examples
- MINUTE(DATETIME(2015, 5, 25, 00, 00, 00)) equals 0
- MINUTE(DATETIME(2015, 5, 25, 23, 45, 00)) equals 45
SECOND
SECOND returns the second of a dateTime value. The second is returned as a number in the range 0-59.
Syntax
SECOND(dateTime)
- dateTime - The dateTime value of which the second must be returned.
Return type
- integer
Examples
- SECOND(DATETIME(2015, 5, 25, 00, 00, 00)) equals 0
- SECOND(DATETIME(2015, 5, 25, 23, 45, 59)) equals 59
TODAY
This function returns the current date of the server. The date is given as base type date, formatted as dd-mm-yyyy.
Syntax
TODAY
Return type 1)
- date
NOW
This function returns the current date and time of the server. The date and time are given as base type dateTime, formatted as dd-mm-yyyy HH:mm:ss.
Syntax
NOW
Return type 1)
- dateTime
Collection
SIZE collection
This function determines the size of a collection.
Syntax
SIZE(collection)
- collection - A collection of attribute or entity instances.
Return type
- integer
Example
Suppose you created a Parent and a Child entity. Parent has a multivalued relation with Child via the relation Parent.has_Children.
If the following instances where created:
...
Then:
- without selecting a parent SIZE(Parent.has_children) results in an error
- for Parent_1 SIZE(Parent.has_children) results in 3
- for Parent_2 SIZE(Parent.has_children) results in 1
- for Child_1 SIZE(Child.hobbies) results in 2
- for Child_3 SIZE(Child.hobbies) results in 3
- for Child_4 SIZE(Child.hobbies) results in 1
- SIZE(? (= UNKNOWN)) results in 01)
UNIQUE collection
The UNIQUE function filters duplicate items from a collection. An expression resulting in a collection, never contains duplicate values. A subexpression however, can contain duplicates.
Syntax
UNIQUE(collection)
- collection - A collection of attribute or entity instances.
Examples
Suppose the following model.
...
- COLLECT Person.name FROM ALL Person results in “Kim”, “Rick”, “Bob” (a result never contains duplicate values)
- SIZE(COLLECT Person.name FROM ALL Person) results in 4 (a subexpression can contain duplicate values)
- SIZE(UNIQUE(COLLECT Person.name FROM ALL Person)) results in 3 (the collection holds three unique values)
SUBSET OF
This function returns TRUE if the items in a collection are all present in another collection.
Syntax
collection1 SUBSET OF collection2
- collection1 - The collection that is tested to be a subset of the second collection.
- collection2 - The collection that is tested to hold all the items in the first collection.
Return type
- boolean
Examples
- ('a', 'b', 'c') SUBSET OF ('a', 'b', 'c', 'd') results in TRUE
- ('a', 'b', 'c', 'd') SUBSET OF ('a', 'b', 'c') results in FALSE
- (Person.hobbies) SUBSET OF ([“Tennis”, “Soccer”, “Music”]) results in TRUE if Person.hobbies = “Tennis”, “Soccer”
- (Person.hobbies) SUBSET OF ([“Tennis”, “Soccer”, “Music”]) results in UNKNOWN if Person.hobbies = ?
- (Person.hobbies) SUBSET OF ([“Tennis”, “Soccer”, ?]) results in FALSE if Person.hobbies = “Tennis”, “Soccer” 1)
- ('a', 'b', 'c') SUBSET OF (['a', 'b', 'c']) will result in an error.2)
UNION
Adds two collections of the same base type to a new collection.
Syntax
UNION(collection1, collection2)
- collection1 - First collection to be added to the new collection.
- collection2 - Second collection to be added to the new collection.
Return type
- collection
Examples
Suppose the following model. Entity Parent has a multivalued relation with entity Child via the relation Parent.has_Children.
...
- UNION(Parent[Parent_1].has_Children, Parent[Parent_2].Has_Children) results in a collection of all child instances
- UNION(Parent[Parent_1].has_Children.name, Parent[Parent_2].Has_Children.name) results in “Kim”, “Rick”, “Bob”, “Mary”
- after selecting Child_2, UNION(Child.hobbies, “Reading”) results in “Tennis”, “Dancing”, “Reading”
- UNION(Child[Child_1].hobbies, Child[Child_2].hobbies) results in “Reading”, “Dancing”, “Tennis”
INTERSECTION
This function determines the intersection of two collections. It returns a collection containing the items that are present in both specified collections.
Syntax
INTERSECTION(collection1, collection2)
- collection1, collection2 - Collections to be intersected. These collections must be of the same base type.
Return type
- collection
Examples
Suppose the following model. Entity Teacher has a multivalued relation with entity Child via the relation Teacher.teaches_Children.
...
- INTERSECTION(Teacher[Teacher_1].teaches_Children, Teacher[Teacher_2].teaches_Children) results in a collection of child instances Child_1 and Child_3
- INTERSECTION(Teacher[Teacher_1].teaches_Children.name, Teacher[Teacher_2].teaches_Children.name) results in a “Kim”, “Bob”
- INTERSECTION(Child[Child_1].hobbies, Child[Child_3].hobbies) results in “Reading”
- INTERSECTION(Child[Child_2].hobbies, Child[Child_3].hobbies) results in an empty list
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.
...
- EXISTS Person returns TRUE
- EXISTS Person WHERE (Person.age < 18) returns FALSE
- EXISTS Person WHERE (Person.gender = “m” AND Person.age > 35) returns TRUE
ALL
Use this function to create a collection of all instances of a specified entity.
Syntax
ALL entity
- entity - Entity of which to collect all its instances.
Return type
- collection of entity instances
Examples
Suppose the following data model. The Parent and Child entities both have Person as base entity.
...
- ALL Parent returns a collection of Parent_1 and Parent_2
- ALL Child returns a collection of Child_1, Child_2, Child_3 and Child_4
- ALL Person returns a collection of Parent_1, Parent_2, Child_1, Child_2, Child_3 and Child_4
COLLECT FROM [WHERE]
Use this function to create a collection of entity or attribute instances (meeting certain criteria).
Syntax
COLLECT entity | attribute FROM collection [WHERE expression]
- entity or attribute - Entity or attribute to collect.
- collection - A collection of entity instances.
- expression - Boolean expression that represents the criterion the instance has to meet.
Return type
- collection of entity instances
- collection of attribute values
Examples
Suppose the following model. Entity Teacher has a multivalued relation with entity Child via the relation Teacher.teaches_Children.
...
- COLLECT Child.name FROM ALL Child results in a collection of the values “Kim”, “Rick”, “Bob” and “Mary”
- COLLECT Child FROM Teacher[Teacher_2].teaches_Children results in a collection of the instances Child_1, Child_3 and Child_4
- COLLECT Child.hobbies FROM Teacher[Teacher_1].teaches_Children results in a collection of the values “Reading”, “Dancing”, “Tennis”, “Painting” and “Basketball”
- COLLECT Child.name FROM ALL Child WHERE (Child.hobbies = “Reading”) results in a collection of the values “Kim” and “Bob”
- COLLECT Child.hobbies FROM ALL Child WHERE (Child.name = “Mary”) results in a collection of the value “Football”
COLLECT FROM NAMED [WHERE]
Use this version of the COLLECT FROM [WHERE] function for complex nested selections in which it is necessary to use an alias.
Syntax
COLLECT entity | attribute FROM collection NAMED alias [WHERE expression]
- entity or attribute - Entity or attribute to collect.
- collection - A collection of entity instances.
- alias - A name for the collection.
- expression - Boolean expression that represents the criterion the instance has to meet.
Return type
- collection of entity instances
- collection of attribute values
Examples
Suppose the following model with only instances of entity Person. Person.hasChild is a relation from Person to Person.
...
To retrieve all the parent names, we use this expression:
COLLECT Parent.name
FROM (
COLLECT Person
FROM ALL Person
WHERE (Person.hasChild != ?)) NAMED Parent
}
This expression results in “Rick”, “Julia” and “Joan”.
To retrieve all children names, we use this expression:
COLLECT Child
FROM (
COLLECT Person.hasChildren
FROM ALL Person
WHERE (Person.hasChildren != ?)) NAMED Child
This expression results in a collection of “Kim”, “Bob” and “Sam”.
To retrieve all children younger than 15, we use this expression:
COLLECT Child
FROM (
COLLECT Person.hasChildren
FROM ALL Person
WHERE (Person.hasChildren != ?)) NAMED Child
WHERE (Child.Age < 15)
This expression results in a collection of the instances “Bob” and “Sam”.
To retrieve the children’s names with a parent older than 40 years:
COLLECT Child.Name
FROM (
COLLECT Person.hasChildren
FROM ALL Person
WHERE (Person.hasChildren != ? AND Person.Age > 40)) NAMED Child
This expression results in a collection of “Kim”.
You can make it as complex as you like, for example:
COLLECT Child.Name
FROM (
COLLECT Parent.hasChildren
FROM (
COLLECT Person
FROM ALL Person
WHERE (Person.hasChildren != ?)) NAMED Parent
WHERE (Parent.Age > 40 )
) NAMED Child
WHERE (Child.Age < 18)
This expression results in a collection of “Kim”.
UNPACK
Use this function to get the content of a single-valued collection.
Syntax
UNPACK collection
- collection - A collection of one entity or attribute instance.
Return type
- entity instance
- attribute value of any type
Examples
Suppose the following data model.
...
- UNPACK(COLLECT Person.name FROM ALL Person WHERE (Person.SequenceNumber = MIN(COLLECT Person.SequenceNumber FROM ALL Person))) results in “Ron”
- UNPACK(COLLECT Person.name FROM ALL Person WHERE (Person.SequenceNumber = MAX(COLLECT Person.SequenceNumber FROM ALL Person))) results in “Jenny”
Conversion
Convert a value of one data type into a value of another data type using conversion functions. The following conversion functions are available:
- from string to integer, number, currency, percentage, date, dateTime or boolean
- from integer to string, number, currency or percentage
- from number to string, integer, currency or percentage
- from currency to string, integer or number
- from percentage to string, integer or number
- from date to string or dateTime
- from dateTime to string or date
- from boolean to string, integer or number
Syntax
TEXT(integer, [format], [locale])
TEXT(number, [format], [locale])
TEXT(currency, [format], [locale])
TEXT(percentage, [format], [locale])
TEXT(date, [format])
TEXT(dateTime, [format])
TEXT(boolean, [format])
INTEGER(string, [format], [locale])
INTEGER(number)
INTEGER(currency)
INTEGER(percentage)
INTEGER(boolean)
NUMBER(string, [format], [locale])
NUMBER(integer)
NUMBER(currency)
NUMBER(percentage)
NUMBER(boolean)
CURRENCY(string, [format], [locale]
CURRENCY(integer)
CURRENCY(number)
PERCENTAGE(string, [format], [locale]
PERCENTAGE(integer)
PERCENTAGE(number)
DATE(string, [format])
DATE(dateTime)
DATETIME(string, [format])
DATETIME(date)
BOOLEAN(string, [format])
- value - The value of the date type that is converted.
Examples
- TEXT(PERCENTAGE(1.2), “00.00”, “nl-NL”) results in a string value “01,20”
- INTEGER(“1,0”, ”#0.0”, “nl-NL”) results in an integer value 1
- NUMBER(TRUE) results in a number value 1.0000
- CURRENCY(“1.2”, “invalidPattern”) results in a currency value 1.20 (an unknown pattern will be ignored)
- DATETIME(“19861221 12:02:56”, “yyyyMMdd HH:mm:ss”) results in a dateTime value 1986-12-21T12:02:56
- BOOLEAN(“1”,”1;0”) results in a boolean value TRUE
Formats in the runtime
Each data type is defined in the runtime as follows:
- INTEGER #0
- NUMBER #0.0000
- CURRENCY #0.00
- PERCENTAGE #0.00
- BOOLEAN true,1;false,0
E.g.: numbers will always be shown with 4 decimals.
More functions
ISUNKNOWN
This function replaces a value if that value is unkown.
Syntax
ISUNKNOWN(attribute, value)
- attribute - Attribute of which the value has to be returned if that value is known.
- value - Value to return if the attribute value is unknown.
Return type
- any type, equals the attribute type
Examples
Suppose the following data model.
...
- for Jane, ISUNKNOWN(Person.SequenceNumber, 999) returns 999
- for Mary, ISUNKNOWN(Person.SequenceNumber, 999) returns 667
IS A
Use this function to check the type of an instance. The function returns TRUE if the selected instance is of the specified entity.
Syntax
instance IS A entity_name
- instance - Instance to check.
- entity_name - Name of the entity as string.
Return type
- boolean
Examples
Suppose a domain model in which there is a singleton entity Residence that has a multivalued relation hasRooms with Room. Room is the base entity for singleton Kitchen and not singletons Bedroom and Bathroom.
If you want to determine the number of bedrooms is the residence:
- COUNT(COLLECT Room FROM Residence.hasRooms WHERE (Room IS A “Bedroom”))
If you want to determine the number of bathrooms is the residence:
- COUNT(COLLECT Room FROM Residence.hasRooms WHERE (Room IS A “Bathroom”))
TYPE OF
Use this function to determine the type of an instance. The function returns the name of the entity.
Syntax
TYPE OF base_entity
- base_entity - Name of the instance's base entity.
Return type
- string
Examples
Suppose a domain model in which there is a singleton entity Residence that has a multivalued relation hasRooms with Room. Room is the base entity for singleton Kitchen and not singletons Bedroom and Bathroom.
- for the Kitchen instance the expression TYPE OF Room results in “Kitchen”
- for the Bedroom instances the expression TYPE OF Room = “Bedroom” results in TRUE
AUTHORIZATIONROLES
This system attribute is obsolete since studio version 7.0. With the introduction of processes a new concept 'privilege' was introduced to replace the authorization concept. Therefor the system attribute was also replaced. For backwards compatibility reasons, after logging on to the runtime, the system.authorizationroles attribute will be filled with the user's privileges.
See also: Privileges
CHANNEL
You can get the name of the current channel by using the system attribute system.channel. This is a predefined system attribute, so you don’t have to create it yourself.
At run-time the system returns a string value with the channel you choose while logging on. This value is assigned to the attribute system.channel.
LANGUAGE
You can get the name of the current language by using the system attribute system.language. This is a predefined system attribute, so you don’t have to create it yourself.
At run-time the system returns a string value with the language that you choose while logging on. This value is assigned to the attribute system.language.
PAGE
You can get the name of the current page by using the system attribute system.currentpage. This is a predefined system attribute, so you don’t have to create it yourself.
At run-time the system returns a string value with the page name of the page you are on, this value is assigned to the attribute system.currentpage.
PRIVILEGES
You can get the name of the current privileges by using the system attribute system.privileges. This is a predefined system attribute, so you don’t have to create it yourself.
At run-time the system returns a list of string values of the privileges that you choose while logging on. These values are assigned to the attribute system.privileges.
EVENT
You can capture the parameters associated with an event by using the system attribute system.event. This is a predefined system attribute, so you don’t have to create it yourself.
At run-time the system returns the string values with the parameters associated with the event on the current page. This value is assigned to the attribute system.event.
Beside these user defined event parameters, there are some container types that come with their own built-in buttons. Some older1) containertypes can throw a 'default event' with associated event parameters. Standard container types with this behaviour are:
...
Even though some action labels are the same, the system handles them in a different way, this depends on the context. For instance ‘Edit’ will open an existing instance, ‘Add’ will create a new instance, however the action ‘_edit_instance’ is the same.
Special behaviour of the AQ_InstanceSelector
The AQ_InstanceSelector adds the following actions to system.event, depending on the action that was taken in the instance selector:
- _add_instance: this action is added when a new instance is created
- _edit_instance: this action is added when an existing instance has been edited
- _delete_instance: this action is added when an instance has been deleted
- _update_instance: this action is added when an instance has been updated
instanceType<entity name>: this action describes the entity of the instance that has been edited. For example, adding an instance of type Person puts the following actions in system.event: “_add_instance, instanceType_Person”
...