Placeholders & functions
Placeholders let you inject the value of a field inside an e-mail message or inside the computed value of an auto field.
For instance, here's how you would use placeholders for an e-mail task:
Dear {customer.name} {customer.lastname},
thank you for your order. The total amount is {total} and the order ID is {id}.
Best regards
In this example, the placeholders {total} and {id} will be replaced with the values taken from the current record.
The placeholders {customer.name} and {customer.lastname} will be replaced with the values of the record referenced by the customer field of the current record.
And here's how you would use placeholders for an auto field:
{name} {lastname}
This auto field will contain the concatenated values of the name and lastname fields of the current record.
Functions
Sometimes you need to transform the referenced values. For example, you might want to make a string uppercase. It's easy: just type {UPPER(name)}. You can also combine multiple functions, as in {UPPER(TRIM(lastname))} (this one will remove whitespace from the value before converting it to upper case).
Functions can also reference multiple fields. For instance, supposing you have two date fields called created and completed date, you can compute the number of days between them using {DAYS_TO(created, completed)}
Functions to be used on text fields:
| Function | Example | Description |
|---|---|---|
LOWER(<text>) | LOWER(name) | Turns a given string to lower case. |
UPPER(<text>) | UPPER(name) | Turns a given string to upper case. |
LEFT(<text>, <num>) | LEFT(name, 3) | Returns the first num characters from the left of the string. |
RIGHT(<text>, <num>) | RIGHT(name, 3) | Returns the last num characters from the right of the string. |
SUBSTR(<text>, <from>, <num>) | SUBSTR(name, 2, 3) | Returns characters from the middle of the string, starting at position from and including num characters. The first character is numbered 1. |
TRIM(<text>) | TRIM(name) | Removes whitespace from the beginning and the end of the string. |
IFEMPTY(<text>, <default>) | IFEMPTY(name, 'n/a') | Returns the first string if not empty, otherwise the string supplied as second argument. This is useful when you want to define a default value. |
REGEXP_EXTRACT(<text>, <regex>) | REGEXP_EXTRACT(code, '(..)$') | Applies the given regular expression to the input value and returns the first parenthesised capture. The pattern used in the example will return the last two chars. |
REGEXP_REPLACE(<text>, <regex>, <replace>) | REGEXP_EXTRACT(code, ' ', '_') | Looks for the given regular expression and applies the given replacement string. |
LPAD(<text>, <len>, <char>) | LPAD(code, 10, '0') | Left-pads the given string in order to make it at least len characters long by prepending the given char. |
MD5(<text>) | MD5(code) | Computes the MD5 of the given string (in hex representation). |
Functions to be used on date/time fields:
| Function | Example | Description |
|---|---|---|
FORMAT(<date>, <format>) | FORMAT(created, 'd/m/Y') | Render the given date/time as a string using the supplied format. Valid format specifiers are Y, m, d, H, i, s. |
ADD_DAYS(<date>, <num>) | ADD_DAYS(created, 5) | Adds the given number of days to the input date. |
SUB_DAYS(<date>, <num>) | SUB_DAYS(created, 5) | Subtracts the given number of days from the input date. |
DAYS_TO(<date>, <date>) | DAYS_TO(created, completed) | Calculates the difference in days between the supplied dates. |
Do you need a function that is not implemented yet? Tell us ;-)