In LISTSERV Maestro, calculation formulas are available in the condition tree of target groups of the
Hosted Recipient List type and in the context of the special “
*Calc” system drop-in. This appendix describes the syntax and semantics of these calculation formulas.
A formula is a sequence of expressions that are combined with operators into more complex expressions. Expressions can be optionally nested with parenthesis and make use of a few pre-defined functions.
Operands can be of the number type or of the
text type. They can appear in different “shapes”, which are described in the following sub-sections.
For number type expressions, the number can be any integer number in the range
-9223372036854775808 to
9223372036854775807
Restriction: None. Constant number expressions can be used in any formula.
Type: Constant number expressions are, as the name implies, always of the
number type.
Usage: To include a constant number in a formula, simply type the number using only the digits “
0“-“
9” (do not use “
,” as a thousands-separator or “
.” as a decimal point). Negative numbers must have a leading minus sign “
-“.
Restriction: None. Constant text literal expressions can be used in any formula.
Type: Constant text literal expressions are, as the name implies, always of the
text type.
Usage: To include a text literal in a formula, type the characters desired and denote the beginning and the end of the text literal by enclosing the whole string in quotation marks <
">. There must not be any line breaks in the text string.
"example"
"this is a text string"
"This string contains ""quotes"" which are therefore escaped"
" this string has three spaces at the beginning and end "
Restriction: Standard merge field expressions are only allowed if the formula is used in one of the following contexts:
|
•
|
In a “*Calc” system drop-in of a job with the recipients type
|
|
•
|
Target Group (if the target group is based on either of the two above or on a Hosted Recipient List).
|
If the system drop-in is used in a job with a different recipient type, the formula must not contain any merge field expressions (if it does, an error message will appear during test delivery).
Type: Standard merge field expressions can either be of the
number or
text type, depending on their context or content (see the Type
Determination below for details).
Usage: To include a standard merge field in a formula, type the merge field name enclosed in “
&” and “
;”. Only merge field names that are actually defined in the recipient list can be used.
&NAME;
&STATE;
&ACCOUNT_BALANCE;
Type Determination: Whenever a merge field appears in a formula, it will first be replaced with the field value for the current recipient before the result of the formula is calculated for that recipient. To be able to do this calculation, the expression’s type is determined as follows:
|
•
|
Profile fields of the Boolean and Multiple Selection types are not allowed to be used in formulas at all.
|
|
•
|
If the formula is used in a “*Calc” system drop-in with the recipients types “uploaded CSV text file” or “LISTSERV Maestro selects from a database”, then the type of the field is determined by the field’s content as follows:
|
Special Note: If the formula is used in the condition tree of a “Hosted Recipient List” target group or
in a “*Calc” system drop-in together with recipients based on such a target group, then only profile fields which are defined in the hosted recipient list as “mandatory” are allowed as standard merge field expressions. Fields defined as “optional” must use the optional merge field expression instead.
Restriction: Optional merge field expressions are only allowed if the formula is used in one of the following contexts:
|
•
|
In a “*Calc” system drop-in of a job with the recipients type
|
|
•
|
Uploaded CSV text file, or
|
|
•
|
LISTSERV Maestro selects from a Database, or
|
|
•
|
Target Group (if the target group is based on either of the two above or on a Hosted Recipient List).
|
If the system drop-in is used in a job with a different recipient type, the formula must not contain any merge field expressions (if it does, an error message will display during test delivery).
Type: Optional merge field expressions can be of the
number or
text type, depending on their context or content (see the Type Determination below for details).
Usage: Optional merge fields are fields that may be undefined (empty) for at least some recipients in the recipient list. Recipients with a blank field would not have the result of the calculation formula displayed in their content.
The whole expression is enclosed in brackets “[“ and “
]”. Between the brackets, type the name of the merge field being addressed and enclose it with “
&” and “
;”. Then, type the default that will be used for all recipients where the merge field itself is undefined.
The default that is replaced by “DEFAULT” as shown above must be a constant number or a constant text literal.
Type Determination: Whenever any such merge field appears in a formula, it will first be replaced with the field value for the current recipient, or if that value is undefined or empty, it will be replaced with the given default. Then the result of the formula is calculated for that recipient. To be able to do this calculation, the expression’s type is determined just like for
standard merge field expressions.
In addition, if the field’s value is empty or undefined, the type will be determined by the type of the given default. Make sure that the type of the default matches the field’s type or is at least convertible into that type (see
Automatic Type-Conversion).
Restriction: Functions can usually be used in any formula. However, there may be individual restrictions that apply only to certain functions. See the function descriptions for details.
Type: The type depends on each function. See the function descriptions for details.
Usage: To include a function in a formula, simply type the function name possibly followed by an argument list in parenthesis. See the function description for details concerning whether arguments are required by a certain function and what they mean. Note that function names are case sensitive and that any parenthesis enclosing the argument list must immediately follow the function name, without any space in between.
Abs(-20)
Max(15, &AGE;)
ToLower("Convert this string to ALL Lowercase")
Min(-10, -11, &SIZE_A;, &SIZE_B;)
Available Functions: The currently available functions are described in
Formula Functions.
Operators always work on two operands, which may be any valid expressions. Some operators require their operand expressions to be of the
number type while others require them to be of the
text type.
+ Addition operator (sum of both operands)
- Subtraction operator (difference of both operands)
* Multiplication operator (product of both operands)
/ Integer-Division operator (integer-quotient of both operands)
% Modulo operator (remainder of integer-division of both operands)
Note: In LISTSERV Maestro, the division is strictly an integer division. Any decimal places in the result are discarded; they are
not rounded off, but simply ignored.
+ Concatenation operator (appends both operands)
The concatenation operator takes the two text operands and simply appends the text string on the right-hand to the end of the text string on the left-hand. The result is one string that contains the two strings concatenated together.
The character “+” is used both to denote the addition operator for number operands as well as the concatenation operator for text operands. So if a “
+” appears anywhere in the formula, LISTSERV Maestro must first determine if in this context the addition or the concatenation operator is meant. It simply does so by looking at the operands. If both operands are of the
number type, then the “
+” is interpreted as the addition operator and the result will be of the
number type too.
If at least one of the operands is of the text type, then the “
+” is interpreted as the concatenation operator and the result will be of
text type. If the other operand is of the
number type, it is automatically converted to
text first, see
Automatic Type-Conversion.
The multiplication/division type operators (“
*”, “
/” and “
%”) have higher precedence than the
addition/subtraction type operators (“
+” and “
–”).
Operators with higher precedence are processed first, if several operators with the same precedence level are encountered. They are processed from left to right. Parenthesis can be set freely to influence precedence: Inner parenthesis will be processed before outer parenthesis.
Operators and functions usually require their operands and arguments to be of a certain type. However, under one circumstance it is possible to supply an expression of a different type as an operand or argument, which will then automatically be converted to the required type:
Whenever an operand or argument of the text type is required, an expression of the
text type or of the
number type can be used. In the latter case, the number will first be converted into text before the operator or function is applied. For this conversion, the number-value is simply converted into the corresponding string representation, for example the number 157 becomes the text string
"157".
The format of the date and time patterns must be specified for the functions “ToDate” and “
ToMillis” to convert a numerical date/time value (represented in milliseconds since “Jan. 1
st 1970, 00:00:00 GMT”) into a formatted output string or vice versa.
The pattern format described here applies to the formatting process, when a numerical date/time value is converted into a formatted text string, and to the parsing process, when a text string is parsed to convert it back into the numerical date/time value.
Important: For date/time formatting and parsing, by default the U.S. locale and the current time zone of the server where the Maestro User Interface (LUI) component is running is used. This means that if locale specific values (names of months, weekdays, era-designators, and the like) are required, they will be given as the U.S. locale values by default. Similarly, if a time is given, it will be formatted (or interpreted) as relative to the time zone of the server (although for parsing a specific time zone can be supplied). If the default U.S. locale is not desired, specify a locale in the “
ToDate” or “
ToMillis” function.
Date and time formats are specified by pattern strings. Within pattern strings, unquoted letters from ‘
A’ to ‘
Z’ and from ‘
a’ to ‘
z’ are interpreted as pattern letters representing the components of a date or time string (see below for details). Text can be quoted using single quotes <
'> to avoid interpretation. In a quoted text, the “double” single quote <
''> represents a single quote. All other characters are not interpreted; they are simply copied into the output string during formatting or matched against the input string during parsing.
The following pattern letters are defined (all other characters from ‘A’ to ‘
Z’ and from ‘
a’ to ‘
z’ are reserved). The letters are case-sensitive. The same letter has different meanings in its uppercase or lowercase version. Each pattern letter has a specific “presentation” in the created final string (during formatting) or in the parsed string (during parsing). For example, presentation types may be
Text,
Number,
Year or similar. More details about the presentations and their meanings follow below.
Notes: The value of “calendar week in year” and “calendar week in month” depends on the locale that is used. The locale determines the conventions about which weekday is interpreted as the first day of the week (usually “Monday” or “Sunday”) and under which circumstances a week that falls partially into one year (or month) and partially into the next, is interpreted as belonging to the one year (or month) or the other. The “weekday ordinal in month” indicates the ordinal number of the weekday of the given date/time in the given month. For the first Monday in a month, the ordinal is “1”, as it is for the first Tuesday, Wednesday and so on. For the second Monday in a month, the ordinal is “2”, and so on.
|
•
|
Text: For formatting, if the number of pattern letters is four or more, the full form is used; otherwise, a short or abbreviated form is used if available.
|
|
•
|
Number: For formatting, the number of pattern letters is the minimum number of digits, and shorter numbers are zero-padded to this amount.
|
|
•
|
Year: For formatting, if the number of pattern letters is two, the year is truncated to 2 digits; otherwise it is interpreted as a “Number” (see above).
|
For parsing with the abbreviated year pattern (“y” or “
yy”), LISTSERV Maestro must interpret the abbreviated year relative to some century. It does this by adjusting dates to be within 80 years before and 20 years after the current time. For example, using the pattern “
MM/dd/yy” on Jan. 1, 1997, the text “
01/11/12” would be interpreted as Jan. 11, 2012, while the text “
05/04/64” would be interpreted as May 4, 1964. During parsing, only strings consisting of exactly two digits will be parsed into the default century. Any other numeric string, such as a one digit string, a three or more digit string, or a two digit string that is not all digits (for example “
-1”), is interpreted literally. Therefore, “
01/02/3” or “
01/02/003” are parsed, using the same pattern, as Jan. 2, 3 AD. Likewise, “
01/02/-3” is parsed as Jan. 2, 4 BC.
|
•
|
Month: If the number of pattern letters is one or two, the month is interpreted as “Number”, if it is 3 or more, it is interpreted as “Text”. Therefore, if the month is interpreted as “Number” or “Text”, the applicable “Number”/ “Text” interpretation rules apply (see above). For example: 1 letter will be a “Number” that is not padded, 2 letters will be a “Number” that is padded, 3 letters will be a “Text” using the abbreviated form and 4 or more letters will be a “Text” using the long form.
|
|
•
|
General time zone: For formatting, the time zone is handled as “Text” if it has a name. If not, it is given as a GMT offset value in the format “ GMT[+|-]HH:MM”, where “ HH” is the hours between 0 and 23 (one or two digits, may be zero-padded to the left) and “ MM” is the minutes between 00 and 59 (always two digits, zero-padded to the left if necessary). For example, “ GMT+8:00”, “ GMT+08:00”, “ GMT-12:45”.
|
|
•
|
RFC 822 time zone: For formatting, the RFC 882 4-digit time zone format is used: “ [+|-]HHMM”, where “ HH” is the hours as two digits, between 00 and 23 (zero-padded to the left if necessary) and “ MM” is the minutes as two digits, between 00 and 59 (zero-padded to the left if necessary).
|
|
•
|
Time zone parsing: For parsing of a time zone, it does not matter if the format pattern specifies a “General time zone” or a “RFC 822 time zone”: In both cases, all three types of time zone specifications are accepted:
|
The following examples show how date and time patterns are interpreted in the default U.S. locale with the “U.S. Pacific Time” time zone. The given date and time are “
2001-07-04 12:08:56” local time in that time zone.
