Operators and Functions for Expressions

+ , - , / , *, %, - unary, + unary

Simple Numeric Operators

== , != , < , > , >= , <=

Use these with types Integer, Double, Date, Time and String.

&&,||,!

Correspond to logical operators: AND, OR, NOT

? :, ; , =

Conditional (IF).
Example: ( (X>=10) ? (X+1) : (X-1) )

CASE, CASE_WHEN, CASE_ELSE

Conditional (CASE)

"", true, false, Pi, e

Char(Integer) : String
Char(Code)

Returns the character corresponding to the numeric code specified

Code(String) : Integer
Code(Text)

Returns the numeric for the character in text. The text should have a length of 1

StartsWith(String, String) : Boolean
StartsWith(Text1, Text2)

Returns TRUE if Text1 starts with Text2. FALSE otherwise

Concat(String, String, String, ...) : String
Concat(Text1, Text1, Text3, ...) : Text

Concatenates all the texts one after the other

Right(String, Integer)
Right(Text, N)

Returns a string with the N trailing characters in the text

Trim(String)
Trim(Text)

Removes all leading and trailing spaces in the text

Extract(String, Integer, Integer) : String
Extract(Text1, From, To) : Text2

It extracts a sub-String from a String. The first integer specifies the character to start the extraction from and the second one specifies the limit but it isn't included. Characters start counting at 0.

Left(String, Integer)
Left(Text, N)

Returns a string with the N leading characters in the text.

Length(String) : Integer
Length(Text) : N

Returns the number of characters in the text

Upper(String)
Upper(Text)

Converts all characters in the text to uppercase

Lower(String)
Lower(Text)

Convers all characters in the text to lowercase

EndsWith(String, String) : Boolean
EndsWith(Text1, Text2)

Returns TRUE if Text1 ends with Text2. FALSE otherwise

Text(Integer | Double | Date | Time | String | Boolean):String
Text(Value) : Text

Turns the argument into String. The formats for Date and Time are specified afterwards. The Boolean values have to be true or false

BooleanValue(String) : Boolean
BooleanValue(Text) : Value

Converts the Text into a boolean value. Text must be "TRUE" or "FALSE".

InValue(String) : Integer
IntValue(Text) : N

Converts the Text into an integer number. The text should have a number format. For instance "123".

DoubleValue(String) : Double
DoubleValue(Text) : N

Converts the Text into a Double number. The text should have a number format. For instance "123,10".

AddDay(Date, Integer) : Date

This adds the specified number of days to the given date.

AddMonth(Date, Integer) : Date

This adds the number of specified months to the given date.

AddYear(Date, Integer) : Date

This adds the specified number of years to the given date.

Date(Integer, Integer, Integer ) : Date

This creates a new date from day, month and year.

DateValue(String) : Date

This turns a String into Date. The String can support either of the two formats.

Day(Date) : Integer

This extracts the date's number of day.

DaysDiff(Date, Date) : Integer

The number of days between both dates.

Month(Date) : Integer

This extracts the date's number of month.

MonthName(Date) : String

This extracts the date's name of month.

MonthsDiff(Date, Date) : Integer

The number of months between both dates.

Quarter(Date) : Integer

This extracts the date's number of quarter.

QuarterName(Date) : String

This extracts the date's name of quarter.

Semester(Date) : Integer

This extracts the date's number of semester.

SemesterName(Date) : String

This extracts the date's name of semester.

Today() : Date

This retrieves the date of the moment it is executed.

WeekOfMonth(Date) : Integer

This extracts the date's number of week of the month.

WeekOfYear(Date) : Integer

This extracts the date's number of the week of the year.

Year(Date) : Integer

This extracts the date's year.

YearsDiff(Date, Date) : Integer

The number of years between both dates.

Hour(Time) : Integer

This extracts the time's number of hours.

Minute(Time) : Integer

This extracts the time's number of minutes.

Now() : Time

This retrieves the time of the moment it is executed.

Second(Time) : Integer

This extracts the time's number of seconds.

Time(Integer, Integer, Integer) : Time

This creates a new time from hours, minutes and seconds.

TimeValue(String) : Time

This turns a String into Time.

Average(Integer, Integer, ....) : Double

This calculates the average of the given numbers.

Sqrt(Double) : Double

This calculates the square root of the given numbers.

LookupField(TableName, FieldName, key) : value

This looks up in the 'TableName' table the field named 'FieldName' and retrieves its value for the 'key' key.
Enclose the Table and Field names in quotation marks (").
The names of fields used as keys should not have spaces.
Example: LookUpField ("Products", "Name", ProductCode) will retrieve from the Products Local Table the Name of the product which code is ProductCode.

Choose(Integer, Option1, ...,OptionN) : Value

It returns as a result (Value) the Option value that corresponds to the value of the function's first parameter (Integer).
For 1 the first value is returned (Option 1), the second for 2 (Option 2), etc. If the value is higher than the number of options it returns null.
It is possible to use the TN (Tuple Number) identifier as first parameter and field names as options to generate more than one record of source entry.

IsNull(FieldName) :  Boolean

This returns true when the field is null, otherwise returns false.

Parameter(String) : ParameterValue

This returns the value of the parameter indicated in the argument.

TN

This element's value indicates the tuple number that is being processed when the option Tuples by Records is being used and there's more than one tuple for each entry record. The first number is 1.