Table of Contents Previous Next Index

Appendix A: Using Formulas in Hosted Recipient List Target Groups

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 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.
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.
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.
argNumber type: The argument whose absolute value is to be determined.
Abs(-10), Abs(&VALUE;), Abs(30 - &ACCOUNT; * 2)
 
Returns the current time, in milliseconds since
“Jan. 1st 1970, 00:00:00 GMT”.
 
Searches for an appearance of the text “searchtext” in the given “text”. Returns the zero-based index of the first appearance of “searchtext”, or “1” if none was found. Optionally, a zero-based “startindex” can be specified with the effect that the search in “text” will not start at the beginning of “text” but at the character position specified by “startindex”.
text – Text type: The text string to search in.
searchtextText type: The string to search for.
startindexNumber type: The character position at which the search shall be started. If not given, the search will always start at the first character (index “0”).
IndexOf("a longer text", "lo"),
IndexOf("abc def abc def", "abc", 4)
 
textText type: The text whose length shall be determined.
Length("some text"), Length(&FULL_NAME;)
 
arg1Number type: The first argument to find the maximum of.
arg2Number type: The second argument to find the maximum of.
arg3Number type: The third argument to find the maximum of.
argNNumber type: The Nth argument to find the maximum of.
Max(3, -4), Max(17, 22, 4), Max(82, &VALUE; * 7)
 
Min(arg1, arg2 [, arg3, ... , argN])
arg1Number type: The first argument to find the minimum of.
arg2Number type: The second argument to find the minimum of.
arg3Number type: The third argument to find the minimum of.
argNNumber type: The Nth argument to find the minimum of.
Min(13, 2), Min(17, -&VALUE;, 4 * -17)
 
Pow(base, exp)
baseNumber type: The base argument.
expNumber type: The exponent argument.
Pow(2, 8), Pow(&BASE;, &EXPONENT;)
 
 
No argument given: The range is from 0 to the largest possible number value.
One argument given: The range is from 0 to the value of the given argument minus one (i.e. “0” to “threshold-1”).
thresholdNumber type: The threshold value for the random number. The random number will range from “0” to “threshold – 1”.
Random, Random(6), Random(&MAX_RANDOM;),
Random(&VALUE; - 17 * &MAX;)
 
Returns the time at which the subscriber subscribed to the hosted recipient list referenced by the target group in whose context the formula is used, represented in milliseconds since “Jan. 1st 1970, 00:00:00 GMT”.
Can only be used in formulas 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. Will not be accepted in formulas for a different recipient type.
 
 
 
 
 
 
The substring begins with the character in “originaltext” at the position specified by “startindex”. If no “endindex” is given, the substring extends to the end of the original string. If “endindex” is given, the substring extends to the character at position “endindex – 1”, i.e. the character at “endindex” is the first character that is not part of the substring.
Note: all indices are zero-based, i.e. the index of the last character in the original string is at the position “length of original string minus 1”.
originaltextText type: The original text string from which the substring will be extracted.
startindexNumber type: The beginning index, inclusive, zero-based.
endindexNumber type: The ending index, exclusive, zero-based.
Substring("original text", 5),
Substring("original text", 3, 3 + &VALUE;)
 
Returns a formatted representation of a numerical date/time value. The formatted representation is usually returned as text, but may also be returned as a number, if applicable. The format that is used to create the representation is specified with the given format argument.
Note: For formatting purposes, LISTSERV Maestro uses the U.S. locale and the time zone of the server where the LUI component is running by default. Locale text, like weekday names, names of months, etc., will be formatted using the U.S. locale. Similarly, times will be formatted using the server’s time zone.
To specify a different locale, use the optional parameter “localename” to choose a predefined locale or the optional parameters “langcode” and “countrycode” to specify your own locale. See Date and Time Patterns for details.
Usually Text. However, may be Number, if the result can be expressed as a number.
datevalue – Type Number: The date/time value to format. Must contain the desired date/time represented as milliseconds since “Jan. 1st 1970, 00:00:00 GMT” (for example from the output of the functions “CurrentMillis”, “SubscribeTimeMillis” and “ToMillis”).
formatpattern – Type Text: Specifies the format pattern to use to convert the milliseconds value in “datevalue” into the formatted representation.
Or you supply only the following argument:
localenameText type: A name of a predefined locale. See section The following tables describe the functions that are available for use in LISTSERV Maestro formulas. for a list of available locale names. If specified, any locale specific text in the formatted date/time representation will be given according to this locale.
Or you supply both the following two arguments:
langcodeText type: A lowercase two-letter ISO-639 language code specifying the language for the locale. See for example http://ftp.ics.uci.edu/pub/ietf/http/related/iso639.txt for a list of the ISO-639 language codes.
countrycodeText type: An uppercase two-letter ISO-3166 country code specifying the country for the locale. See for example http://userpage.chemie.fu-berlin.de/diverse/doc/ISO_3166.html for a list of the ISO-3316 country codes.
If “langcode” and “countrycode” are specified, they are used to create a locale for the given language and country, and any locale specific text in the formatted date/time representation will be given according to this locale.
 
textText type: The text string to convert to lowercase.
 
Returns the numerical value representing the date/time as parsed from a text string argument, where the numerical value specifies the date/time in milliseconds since “Jan. 1st 1970, 00:00:00 GMT” (can then be compared to the output of the functions “CurrentMillis” and “SubscribeTimeMillis” or be used as input to “ToDate”). The format used to parse the given date/time text string is specified with the given format argument.
Note: For parsing purposes, LISTSERV Maestro will by default assume the U.S. locale and the time zone of the server where the LUI component is running, i.e. if locale specific texts, like weekday names, names of months, etc., are contained in the date/time text string, they must appear with the correct names of the U.S. locale. Similarly, times given in the date/time text string will be interpreted as relative to the server’s time zone.
To specify a different locale, use the optional parameter “localename” to choose a predefined locale or with the optional parameters “langcode” and “countrycode” to specify your own locale. See Date and Time Patterns for details.
datetextText type: The date/time text string to parse. Must contain the desired date/time in a textual format which can be parsed by applying the given format pattern.
formatpatternText type: specifies the format pattern to use to parse the “datetext”. See Date and Time Patterns for details.
Or you supply only the following argument:
localenameText type: A name of a predefined locale. See Date and Time Patterns for a list of available locale names. If specified, any locale specific text in “datetext” will be expected according to this locale.
Or you supply both the following two arguments:
langcodeText type: A lowercase two-letter ISO-639 language code specifying the language for the locale. See for example http://ftp.ics.uci.edu/pub/ietf/http/related/iso639.txt for a list of the ISO-639 language codes.
countrycodeText type: An uppercase two-letter ISO-3166 country code specifying the country for the locale. See for example http://userpage.chemie.fu-berlin.de/diverse/doc/ISO_3166.html for a list of the ISO-3316 country codes.
If “langcode” and “countrycode” are specified, they are used to create a locale for the given language and country, and any locale specific text in “datetext” will be expected according to this locale.
(parses the given date using a custom locale for language “de” =German and country “AT” =Austria)
 
 
 
 
 
 
 
Tries to parse a number from the given text string and returns that number. Generates an error if the provided text string does not contain a valid number (for example letters).
Can be used to convert a profile field which is of type text but still only contains numbers to the type number, which can then be used in contexts that require the type number (such as number operators and functions requiring a number argument).
textText type: The text string to convert to a number.
ToNum("12345"), ToNum(&AGE;), ToNum("123" + "456")
Note: The result of the last example will not be the number 579, but will instead be the number 123456, i.e. first the string concatenation operator “+” is applied to the two strings and then the resulting string is converted into a number.
 
textText type: The text string to convert to uppercase.
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. 1st 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
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:
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.