The following is a list of functions available for the operations of the Field Calculator.
| Field Name | Function | Example |
|---|---|---|
| fn.datetostr(date, pattern) | Converts a date into a string value |
fn.datetostr(contact.values.BIRTHDATE, 'dd/MM/yyyy') |
| fn.datetostr_tz(date, pattern, timezone) | Converts a date into a string value. This function is applied using a specific time zone |
fn.datetostr_tz(contact.values.BIRTHDATE, 'dd/MM/yyyy', 'Europe/Rome') |
| fn.strtodate(string, pattern) | Converts a string value into a date |
fn.strtodate(contact.values.MYFIELD, 'dd/MM/yyyy HH:mm') |
| fn.strtodate_tz(string, pattern, timezone) | Converts a string into a date value. This function is applied using a specific time zone |
fn.strtodate_tz(contact.values.MYFIELD, 'dd/MM/yyyy HH:mm', 'Europe/Rome') |
| fn.now() | Returns the current date and time | |
| fn.today() | Returns today's date with time set to 00:00:00 | |
| fn.today_tz(timezone) | Returns today's date with time set to 00:00:00 according to the specified time zone | |
| fn.midnight(date) | Returns an original date with time set to 00:00:00 | |
| fn.midnight_tz(date, timezone) | Returns an original date with time set to 00:00:00 according to the specified time zone | |
| fn.dateadd(date, offset, unit) | Adds a time quantity to a date in a specific time unit (years, months, days, hours, minutes, seconds, days of the week) | fn.dateadd(contact.values.EXPIREDATE, -2, 'day') |
| fn.dateadd_tz(date, offset, unit, timezone) | Adds a time quantity to a date in a specific time unit (years, months, days, hours, minutes, seconds, days of the week). This function is applied using a specific time zone |
fn.dateadd_tz(contact.values.EXPIREDATE, -2, 'day', 'Europe/Rome') |
| fn.datediff(date1,date2, unit) | Returns an int value representing the difference between two dates in a specific unit of measure (years, months, days, hours, minutes, seconds, days of the week) | fn.datediff(fn.now(), contact.values.EXPIREDATE, 'day') |
| fn.datepart(date1, unit) | Returns a string value representing the part of a date corresponding to the indicated time unit (years, months, days, hours, minutes, seconds, days of the week) |
fn.datepart(contact.values.EXPIREDATE, 'month') |
| fn.datepart_tz(date1, unit, timezone) | Returns a string value representing the part of a date corresponding to the indicated time unit (years, months, days, hours, minutes, seconds, days of the week).This function is applied using a specific time zone |
fn.datepart_tz(contact.values.EXPIREDATE, 'month', 'Europe/Rome') |
| fn.age(date) | Returns an init representing the age based on a date, in years. |
fn.age(contact.values.BIRTHDATE) |
| fn.age_tz(date, timezone) | Returns an init representing the age based on a date, in years. This function is applied using a specific time zone |
fn.age(contact.values.BIRTHDATE) |
| fn.round(var) | Returns a rounded numeric value | |
| fn.max(a,b) | Returns the higher of two numeric values or two dates | |
| fn.min(a,b) | Returns the lower of two values or two dates | |
| fn.abs(var) | Returns the absolute value of a numeric value | |
| fn.md5(var) | Returns a string representing the value calculated with the MD5 algorithm and printed in base 16 | |
| fn.sha1(var) | Returns a string representing the value calculated with the SHA-1 algorithm and printed in base 16 | |
| fn.sha256(var) | Returns a string representing the value calculated with the SHA-256 algorithm and printed in base 16 | |
| fn.random(min,max) | Returns an integer within the specified minimum and maximum values, inclusive | |
| fn.strtolist(string, separator) | Converts a string into a list using a specific separator | fn.strtolist('Italy,France,UK', ',') |
| fn.listtostr(list, separator) | Converts a list into a string using a specific separator | fn.listtostr(contact.values.COUNTRY, ',') |
| fn.randomelement(list) | Selects a random element from a list of type collection or string (strings should be comma-separated) |
fn.randomelement('Red,Yellow,Green') |
| fn.sort(list,direction) | Sorts a list of type collection using a natural sorting function (for strings, this is alphabetical order). Use direction='asc' for ascending order and direction="desc" for descending order.Any other value or list is treated as asc |
|
| fn.sortby(list,field,direction) | Sorts a list of records using the value of the field in each record. Use direction='asc' for ascending order and direction="desc" for descending order. Any other value or list is treated as asc |
|
| fn.filter(list,condition) | Filters a list of records by evaluating the "condition" for each record. If applied to lists of primitive values such as strings or numbers, you must use the special keyword this to refer to the value. |
Example with record: fn.filter(myqueryresultset,'name eq "xx"') Example with primitive values: fn.filter(fn.strtolist('Italy,France,UK', ','),'this contains "tal"') |
| fn.ucase(string) | Converts a string to uppercase. If applied to a list, the function is applied to all its elements | |
| fn.lcase(string) | Converts a string to lowercase. If applied to a list, the function is applied to all its elements | |
| fn.capitalcase(string) | Capitalizes the first letter of each word and makes all other letters lowercase. If applied to a list, the function is applied to all its elements | |
| fn.trim(string) | Removes all excessive spaces from a string | |
| fn.left(string, maxchars) | Returns the left portion of a string or list equal to the specified number of characters/elements | |
| fn.right(string, maxchars) | Returns the right portion of a string or list equal to the specified number of characters/elements | |
| fn.mid(string, from, to) | Extracts a portion of a string or list (from=0 is the first element, to is always required) |
|
| fn.len(var) | Returns the length of a string or list | |
| fn.concat(var1, var2) | Concatenates the string representation of two variables. null values are treated as empty strings. For date/timestamp values, it's better to format them using fn.strtodate_tz |
|
| fn.if(test condition, value if true, value if false) |
If the condition is true, it returns the specified value; otherwise, it returns the other value. Nested if statements and the use of other functions are also supported. The value can be:
|
fn.if(contact.values.field eq 'ita', "true" , "false");
fn.if(contact.values.field eq 'ita', "valore ita",
fn.if(contact.values.field eq 'ita', "valore ITA" , fn.ucase("altra lingua fn")) |
| fn.ifempty(string, defaultvalue) | If the string is empty or null, it returns the specified value; otherwise, it returns the original value | |
| fn.coalesce(var, defaultvalue) | If var is empty, it returns the specified value; otherwise, it returns the original value. | fn.coalesce(contact.values.ORDERS, 0) |
| fn.extract(list, fieldname) | Extracts the field defined in fieldname from each element in the list and returns a list of extracted fields (in the same order as the input list) |
fn.extract(listofperson, 'name') |
| fn.compose(list, fieldname) | Assembles a list of records (Maps), where each element will have a field named fieldname containing the corresponding value from the original list (in the same order as the input list) |
|
| fn.base64_encode(string) | Encodes a string into base64 representation using utf-8 encoding to read the string |
|
| fn.base64_decode(string) | Decodes a string from base64 using utf-8 encoding to write the resulting string |
|
| fn.url_encode(string) | Encodes a string into URL querystring parameter representation using utf-8 encoding (java URLEncoder.encode) |
|
| fn.url_decode(string) | Decodes a string from URL querystring parameter representation using utf-8 encoding (java URLDecoder.decode) |
|
| fn.url_appendparameter(url,name,value) | Treats a URL as a URL, and appends a parameter named name with value value to the querystring after the "?" symbol and before the "#" symbol.The value MUST already be encoded using the url_encode function |
Note date handling functions use the timezone of the contacts for which the code is executing
Examples
Boolean expression
contact.values.lingua EQ 'ITA'
(contact.values.field1 EQ 4 OR contact.values.field1 EQ 5+1) AND contact.values.field2 NEQ NULL
contact.subscribed and contact.values.DOMINIO EQ 'gmail.com'
String expression
'Value1'
contact.values.NAME & '-' & contact.values.SURNAME
Numeric expression
10
(contact.values.A + contact.values.B)/2