Calculation Formulas

Expressions

Operands and function arguments can appear in different "expressions", which are described in the following sub-sections.

Constant Number Expressions
Constant Text Literal Expressions
Constant Boolean Expressions
Standard Merge Field Expressions
Optional Merge Field Expressions
Function Expressions
Target Group Parameter 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 type Number.

Usage: To include a constant number in a formula, simply type the number using only the digits "0-9" (do not use "," [comma] as a thousands-separator or "." [period] as a decimal point). Negative numbers are denoted by using 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 type Text.

Usage: To include a text literal in a formula, simply type the characters required and denote the beginning and the end of the text literal by enclosing the whole string in quotes ("). There must not be any line breaks in the text string (or if line breaks are included, they will be ignored). Since the text literal must be quoted, it must also follow the rules for quote escaping. See here for details.

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

Constant Boolean Expressions

Restriction: None. Constant boolean expressions can be used in any formula.

Type: Constant boolean expressions are, as the name implies, always of type Boolean.

Usage: To include a constant boolean in a formula, simply type either "true" or "false" (without the quotes).

Examples:
true
false

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 "Subscriber List" or "List Group" target group
In the derivation rule of a derived profile field of a subscriber list or group
In a "*Calc" system drop-in of a job with the following recipient types:
- Send to Recpients in the Subscriber Warehouse
- Upload Recipients
- Select Recipients from a Database or LDAP Directory
- Send to a Recipient Target Group (if the target group is based on either of the types above).

If the system drop-in is used in a job with a different recipients type, the formula must not contain any merge field expressions. If it does, an error message will be displayed about this during test delivery.

Type: Standard merge field expressions can be of type Number, Text, Boolean or Text Set, depending on their context and 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 use merge field names that are actually defined in the subscriber list.

Examples:
&NAME;
&STATE;
&ACCOUNT_BALANCE;

Type Determination: Whenever any such merge field appears in a formula, it will first be replaced with the field value for the current recipient. This occurs 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 "Subscriber List" or "List Group" target group, or in a "*Calc" system drop-in together with recipients from a subscriber list or list 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 subscriber list or group:
- Profile fields of type "Number" have the expression type Number.
- Profile fields of type "Text" have the expression type Text.
- Profile fields of type "Boolean" have the expression type Boolean.
- Profile fields of type "Single Selection" have the expression type Text.
- Profile fields of type "Multiple Selection" have the expression type Text Set.
This determination of the type is already done during parsing of the formula.

Restriction: Profile fields of type "Multiple Selection" can only be used as merge fields in the message itself, not on a landing page.
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 or LDAP directory", 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 (which for an empty content will then be an empty text string).

Consequently, if the merge field appears in a location of the formula where a Number type is expected, you need to ensure 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 usually cause the calculation of the formula, and the delivery of the job, to fail.

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

Special Note for Subscriber Lists and Groups: If the formula is used in the condition tree of a "Subscriber List" or "List Group" target group, or in a "*Calc" system drop-in together with recipients from a subscriber list or list group, then there are special rules for profile fields of type "Number" or "Text". If such a profile field is defined in the subscriber list or group as "optional", then this field can not be used in a "standard merge field" expression. Instead, use the "optional merge field" expression (see next sub-section.)

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 "Subscriber List" or "List Group" target group
In the derivation rule of a derived profile field of a subscriber list or group
In a "*Calc" system drop-in of a job with the following recipient types:
- Send to Recpients in the Subscriber Warehouse
- Upload Recipients
- Select Recipients from a Database or LDAP Directory
- Send to a Recipient Target Group (if the target group is based on either of the types above).

Type: Optional merge field expressions can either be of type Number or of type Text, depending on their context and 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 subscriber list. If such a field would be used normally in a formula, it would not be possible to calculate the result of the formula for those recipients. Therefore, such "possibly undefined" fields must be included as "optional merge field" expressions, not as "standard merge field" expressions. An optional merge field expression is written in the following way:

[&FIELD_NAME; DEFAULT]

The whole expression must be enclosed in brackets "[" and "]". Between the brackets the name of the merge field to be addressed appears first, enclosed with (&) and (;), then the default content that will be used for all recipients where the merge field itself is undefined.

The default that replaces "DEFAULT" as given above must either be a constant number or a constant text literal.

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

(Note that 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. Next, 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 (see "Type Determination" in that sub-section).

In addition, if the field's value is empty or undefined, the type will be determined by the type of the given default. Therefore, make sure that the type of the default matches the field's type or is at least convertible to that type (see here for details).

Special Note for Subscriber Lists and Groups: If the formula is used in the condition tree of a "Subscriber List" or "List Group" target group, or in a "*Calc" system drop-in together with recipients from a subscriber list or list group, then only profile fields of type "Number" or "Text" can be used in an "optional merge field" expression, and also only if that field is actually defined as "optional". For fields of other types, or for fields that are defined as "mandatory", use the "standard merge field" expression instead (see previous sub-section.)

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 if arguments are required by a certain function and what they mean. Note that function names are case sensitive and that the parenthesis enclosing the argument list (if any) must follow directly after 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 here.

Target Group Parameter Expressions

Restriction: Target group parameter expressions are only allowed if the formula is used in the condition tree of a "Subscriber List" or "List Group" target group. They must not be used in a formula of a "*Calc" system drop-in.

Type: Either Number or Text, depending on how the parameter is specified (see below).

Usage: A target group parameter is a parameter that is specified during target group definition, but whose value is not yet known at that time. The parameter will later be "filled out" with content by a user choice made in the recipients wizard, when the target group is used for recipient definition.

To include a target group parameter in a formula, simply type the name of the parameter enclosed in double curly brackets "{{" and "}}". The parameter name must only contain the characters "A" to "Z", "a" to "z", "0" to "9" and the underscore "_".

For the formula, it is also important to know the type (either Number or Text) with which the final content of the parameter will be interpreted:

If a parameter is enclosed with quotes (") like a text string, its content is interpreted as Text. If it is not enclosed in quotes, its content is interpreted as Number, so it is important to make sure that all possible parameter values are indeed valid numbers (for type "number").

Note: If you include the same parameter name several times in the same formula, then it will be interpreted as one parameter that simply appears several times. All appearances will have the same content value and must all appear in the same type context as Number or Text. In addition, they must either all be enclosed in quotes to be interpreted as Text parameters or none of them must be enclosed in quotes (to be interpreted as Number).

Similarly, if a parameter is used with the same name in the same target group but outside of a formula or in a different formula, then all these appearances of the parameter name (in the same target group) will reference the same parameter. All of them will be replaced with the same final content during usage in the recipients wizard.

Examples:
{{param}}
{{my_number_param}}
"{{a_text_param}}"
{{Param10}}