Operators and Functions for Expressions

Operator/Function

Comments

Numerical Operators

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

Comparison Operators

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

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

Logical Operators

&&,

These are the logical operators: AND, OR, NOT

Other Operators

?:

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

String Functions

BooleanValue (String) Boolean

Turns a String into Boolean. The String has to be true or false.

Concat (String, String) String

It concatenates the second String after the first one.

DoubleValue (String) Double

Turns a String into Double.

Extract (String, Integer, Integer ) String

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.

IntValue (String) Integer

Turns a String into Integer.

Length (String) Integer

Number of characters in the String.

Text (Integer

Double

Date

Time

String

Boolean) String

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

false.

Date Functions

The representation of Date type as String is: dd/mm/yyyy or dd/mm/yy. The Text function retrieves: dd/mm/yyyy.

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.

Time Functions

The representation of Time type as String is: hh:mm:ss (Note: hh:mm is not supported)The Time type is for internal use in the expressions as intermediate results. You cannot have Time type Virtual Fields, so the final result of an expression that defines a Virtual Field must be of another type.

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.

Mathematical Functions

Average (Integer , Integer, ....) Double

This calculates the average of the given numbers.

Sqrt (Double) Double

This calculates the square root of the given numbers.

Local Tables Functions

The following functions apply only to local tables.

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.

Others

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.

Parameters

Parameter (String)  ParameterValue

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

Constants

"", true, false, pi, e

Elements

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.