| Wiki Markup |
|---|
{scrollbar}
----
The following table contains operators and functions you can use when entering expressions in virtual fields:
h3. Operators
|| Numerical Operators || ||
| + , - , / , \*, %, - unary, + unary | Simple Numeric Operators |
|| Comparisson Operators || ||
| == , \!= , < , > , >= , <= | Use these with types Integer, Double, Date, Time and String. |
|| Logical Operators || ||
| &&,\|\|,\! | Correspond to logical operators: AND, OR, NOT |
|| Other Operators || ||
| ? :, ; , = | Conditional (IF). \\
Example: ( (X>=10) ? (X+1) : (X-1) ) |
| CASE, CASE_WHEN, CASE_ELSE | Conditional (CASE) |
|| Constants || ||
| "", true, false, Pi, e | |
h3. String Functions
| 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". |
\\
|h3. 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) : DateDate | This adds the specified number of days to the given date. |
| AddMonth (Date, Integer) : DateDate | This adds the number of specified months to the given date. |
| AddYear (Date, Integer) : DateDate | This adds the specified number of years to the given date. |
| Date (Integer, Integer , Integer ) : DateDate | This creates a new date from day, month and year. |
| DateValue (String) : DateDate | This turns a String into Date. The String can support either of the two formats. |
| Day (Date) Integer: Integer | This extracts the date's number of day. |
| DaysDiff (Date, Date) : IntegerInteger | The number of days between both dates. |
| Month (Date) : IntegerInteger | This extracts the date's number of month. |
| MonthName (Date) : StringString | This extracts the date's name of month. |
| MonthsDiff (Date, Date) Integer: Integer | The number of months between both dates. |
| Quarter (Date) Integer: Integer | This extracts the date's number of quarter. |
| QuarterName (Date) : StringString | This extracts the date's name of quarter. |
| Semester (Date) : IntegerInteger | This extracts the date's number of semester. |
| SemesterName (Date) : StringString | This extracts the date's name of semester. |
| Today ()Date : Date | This retrieves the date of the moment it is executed. |
| WeekOfMonth (Date) : IntegerInteger | This extracts the date's number of week of the month. |
| WeekOfYear (Date) : IntegerInteger | This extracts the date's number of the week of the year. |
| Year (Date) : IntegerInteger | This extracts the date's year. |
| YearsDiff (Date, Date) : IntegerInteger | The number of years between both dates. |
|h3. 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: Integer | This extracts the time's number of hours. |
| Minute (Time) Integer: Integer | This extracts the time's number of minutes. |
| Now ()Time : Time | This retrieves the time of the moment it is executed. |
| Second (Time) : IntegerInteger | This extracts the time's number of seconds. |
| Time (Integer , Integer , Integer) : TimeTime | This creates a new time from hours, minutes and seconds. |
| TimeValue (String) Time: Time | This turns a String into Time. |
|h3. Mathematical Functions |
|
| Average (Integer , Integer, ....) : DoubleDouble | This calculates the average of the given numbers. |
| Sqrt (Double) : DoubleDouble | This calculates the square root of the given numbers. |
|h3. Local Tables Functions | |
|
|
The following functions apply only to local tables. |
| LookupField (TableName, FieldName, key) value: 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. |
|h3. 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. ifIf 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. |
|h3. Parameters |
|
| Parameter (String) : ParameterValue | This returns the value of the parameter indicated in the argument. |
|h3. 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. \\ |
----
{scrollbar}
{children} |
Page Comparison
General
Content
Integrations