Calculation Formulas

Back to Calculation Formulas Overview
Back to Functions Overview

Function "SecondaryValue"

SecondaryValue(&MERGE_FIELD;, secondaryColumnName)

Function: This is an alternative method to retrieve the selected value of a single-select profile field or the selected values of a multiple-select profile field. If such a profile field is included in the formula as a standard merge field (simply by supplying its merge field name &NAME;), then what you get is the currently selected entry name (for single-select) or a set of the currently selected entry names (for multiple-select). While this is usually the desired behavior, in special cases a different behavior may be required:

If the lookup table on which the profile field is based has secondary columns defined, then you might want to retrieve the matching secondary values instead.

This is what is done with this function: It retrieves the currently selected secondary value as a return value of type Text (for single-select), or the set of currently selected secondary values as a return value of type Text Set (for multiple-select) of the specified single/multiple-select profile field. From which secondary column the values are retrieved is specified with the "secondaryColumnName" argument.

Note: If this function is used to retrieve the secondary values of a multiple-select profile field, then the resulting set may have less elements than there are currently selected values for the profile field. This is because secondary values are not necessarily unique; therefore, two or more of the selections of a certain subscriber could actually have the same secondary value. And, since the return value of the function is a set (for multiple-select), and a set contains each element only once, those selections will result in only one entry in the resulting set.

Restriction: Can only be used in the derivation rule formula of a derived profile field, or in a formula in the condition tree of a "Subscriber List" or "List Group" target group, or in a "*Calc" system drop-in in the message content together with recipients from on a subscriber list or list group. Will not be accepted in formulas for a different recipients type, or on a landing page!

Also, the specified profile field must be a single or multiple select field. Fields of other types will not be accepted. In addition, the specified "secondaryColumnName" must be the name of a secondary column that is defined in the lookup table on which the profile field is based.

Also, the specified secondaryColumnName must be specified as a single string literal. It cannot be specified as a merge field or nested formula (for example to concatenate the name out of multiple parts). This can either be a single quoted string literal like "columnNameLiteral", or you can specify a single instance of the ToString function using the special unquoted string literal feature of that function, like ToString(columnNameLiteral).

Important: Please remember that the secondary column is referenced in this function by name only. So if you use this function in a formula anywhere, and then later edit the name of the secondary column in the lookup table definition (or delete the secondary column from the lookup table), then this will not automatically change the formula too. The effect will be that the formula becomes invalid as it now references a non-existant secondary column. This in turn could cause mail job delivery errors (if the function is used in a *Calc formula or in a target group condition tree), or prevent the re-calculation of a derived profile field value (if the function is used in the derivation rule formula of a derived field). Therefore, take care when renaming or deleting secondary lookup table columns and, if necessary, also edit/repair any formulas that make use of such columns.

Return-Type: Text or Text Set

Arguments:
&MERGE_FIELD; - (single or multiple select profile field): This argument must be the name of a merge field (complete with the starting "&" and the closing ";") that corresponds with a single/multiple select profile field in the subscriber list or group. It can not be a constant value (a number, text, boolean, or set).
secondaryColumnName - Type Text: Must specify the name of a secondary column in the lookup table that is associated with the specified &MERGE_FIELD;.

Examples:
SecondaryValue(&FIELD_NAME;, "Phone Number")
SecondaryValue(&FIELD_NAME;, ToString(Phone Number))