Appendix A: Using Formulas in Hosted Recipient List Target Groups 

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.

Examples for formulas are:

15 + 4

27 * Max(17, 4, 24/8) / (19 + 22)

&NAME; + "@lsoft.com"

(ToNum(&AGE;) – 2004) * 10

ToDate(CurrentTimeMillis, "MM/dd/yyyy HH:mm")

The following sub-sections explain all aspects of formulas in detail.

Expressions

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

Floating point numbers are not possible in LISTSERV Maestro formulas.

The text can be any character string for text type expressions.

Constant Number Expressions

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 “-“.

Examples:

1
0
537
-17
007

Constant Text Literal Expressions

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.

Since the text literal must be quoted, it must also follow the rules for quote escaping.

Examples:

"example"
"this is a text string"
"This string contains ""quotes"" which are therefore escaped"
"   this string has three spaces at the beginning and end   "

Standard Merge Field Expressions

Restriction: Standard merge field expressions are only allowed if the formula is used in one of the following contexts:

·         In the condition tree of a “Hosted Recipient List” target group

·         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 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.

Examples:

&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:

·         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 the type of each merge field is already defined by the profile field’s type, which was defined during creation of the associated hosted recipient list:

°   Profile fields of the number type have the number expression type.

°   Profile fields of the text type have the text expression type.

°   Profile fields of the Single Selection type have the text expression type.

°   Profile fields of the Boolean and Multiple Selection types are not allowed to be used in formulas at all.

This determination of the type is already done during parsing of the formula.

·         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:

°   If the field’s content can be interpreted as a number, the type “number” will be used. If it cannot be interpreted as a number, (or is empty) the type “text” will be used (empty content will become an empty text string).

°   Consequently, to use the merge field in a location of the formula where a “number” type is required, make sure that the field’s content can be interpreted as a number for all recipients. Otherwise, the field will have the type “text” for some recipients, which would cause the calculation of the formula to fail, so the job itself would fail as well.

°   This determination of the type is done during delivery, when the merge-values of all recipients are known.

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.

Optional Merge Field Expressions

Restriction: Optional merge field expressions are only allowed if the formula is used in one of the following contexts:

·         In the condition tree of a “Hosted Recipient List” target group

·         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.

An optional merge field expression is written in the following way:

[&FIELD_NAME; DEFAULT]

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.

Examples:

[&NAME; "no name"]
[&NAME; ""]
[&STATE; "n/a"]
[&ACCOUNT_BALANCE; 0]

(Note: the second example defines an empty text as the default for &NAME;.)

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).

Function Expressions

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.

Examples:

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

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.

Operators for Number Operands

The following operators require both operand expressions to be of the number 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.

Operators for Text Operands

The following operators require both operand expressions to be of the text type:

+ 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.

Operator Precedence and Parenthesis

Formulas are processed by LISTSERV Maestro following the usual mathematical conventions:

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.

Examples:

15 + 3 * 4                                      Result: 27

8 * (7 – 3)                                   Result: 32

17 * 22 / 2 % 5                          Result: 2

17 * (22 / 2 % 5)                      Result: 17

17 * (22 / (2 % 5))                 Result: 187

Automatic Type-Conversion

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".

Formula Functions

The following tables describe the functions that are available for use in LISTSERV Maestro formulas.

Date and Time Patterns

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 Patterns

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.

Commonly Used Pattern Letters

Special Pattern Letters

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.

Presentation Description

Pattern letters are usually repeated, as their number determines the exact presentation:

·         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.

For parsing, both forms are accepted, independent of the number of pattern letters.

·         Number: For formatting, the number of pattern letters is the minimum number of digits, and shorter numbers are zero-padded to this amount.

For parsing, the number of pattern letters is ignored unless it is needed to separate two adjacent fields.

·         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, if the number of pattern letters is more than two, the year is interpreted literally, regardless of the number of digits. So using the pattern “MM/dd/yyyy”, the text “01/11/12” parses to Jan. 11, 12 AD.

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”.

For parsing, see “Time zone parsing” below.

·         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).

For parsing, see “Time zone parsing” below.

·         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:

°   Time zone given as “Text” (if given with a name recognized by LISTSERV Maestro).

°   Time zone given as GMT offset (see “General time zone”).

°   Time zone given as RFC 882 4-digit notation (see RFC 822 time zone).

Examples

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.