Lookup Table Properties

Defines the general settings of a lookup table:

• Name: The name of the table.

• Encoding: The encoding used to store the lookup values in the table. Choose an encoding that matches the language in which you want to specify the values (see below).

• Description: The description of the table. Enter a meaningful description that will later assist you in choosing between several different lookup tables.

If this screen is used to edit an existing table, then you are restricted in the way how you can edit the encoding settings: You can only change the encoding to another encoding if the new encoding is a superset of the original encoding. This means you can "widen" the encoding from ASCII to ISO and from ISO to Unicode, but not "tighten" it in the reverse.

Note: When you use a lookup table by assigning it to a list group or subscriber list profile field of the Single Select type or Multiple Select type, then you will only be able to use lookup tables that use the ASCII encoding or the same ISO encoding as is specified for the list group (or in case you are working on a profile field of a list, the same ISO encoding as is specified for the group the list belongs to).

Advanced Settings

In addition to the standard settings, a lookup table can have additional secondary columns. The standard column of a lookup table holds the lookup table entries' names (the main values), which are used to create the entries of either a single- or multiple-select list for the user and must therefore be unique. Contrary to this, a secondary column of a lookup table holds additional entry information that is associated with the main entry.

In contrast to the main values, the values in a secondary column do not have to be unique, i.e. two different lookup table entries may have the same value in one of the secondary columns. The number of secondary columns per lookup table is generally not limited (although system limits may apply).

The values in a secondary column can be accessed in different ways:

• In formulas: With the special calculation formula function SecondaryValue.

  For this function, the name of a profile-field (either a single- or multiple-select field) and the name of the secondary column you want to access must be specified. The function then looks up the entry (or entries) that a given subscriber currently has selected in the specified profile field. For this entry, the matching value from the specified secondary column (in the lookup table that is associated with the specified profile field) is then returned by the function.

  The function can be employed in any formula that also allows normal profile field access. This includes formulas:

  • In a *Calc system drop-in (to merge a value into the email content).

  • In a condition tree of a subscriber dataset or list target group.

  • In a derivation rule calculation formula of a derived profile field (although for derived profile fields there is also a second method of accessing secondary columns, see below).

  See calculation formulas for more details.

• In a derived profile field: Either as part of a calculation formula (see above) or with the special derivation rule type "Mirror secondary lookup table value of a single select field" (see derived profile fields and their derivation rules for more details).

• In a demographic breakdown report: Of a subscriber dataset or list. For such a report, you select which of the profile fields you want to include in the profile structure that you are examining. If one of the profile fields is based on a lookup table that in turn has secondary columns defined, than you can actually include this profile field several times in the profile structure: Once for each available lookup table column. I.e. you can include the profile field in a way where it displays the normal lookup table value, and/or you can include it to display one or several of the secondary lookup table values.

Important: Please remember that if a secondary column is referenced in a calculation formula (using the SecondaryValue function), then this reference is by name only. Therefore, if you reference a secondary column 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-existent secondary column. This in turn could cause mail job delivery errors (if the formula 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 formula is used in the derivation rule formula of a derived field).

Example for using secondary columns:

An organization with customers worldwide wants to create a dataset which in its profile shall contain a single-select field called "COUNTRY" that shall allow each subscriber to select the country he lives in. For this, a lookup table "Countries" is created, with each country as an entry in the standard column of the lookup table. This lookup table is assigned to the single-select profile field.

In the organization, each country also has a service phone number that customers of that country can call for service requests. Most countries have their own number, but sometimes smaller countries share a number. For this, a secondary column called "Service Number" is added to the "Countries" lookup table, which is filled with the associated service number for each country. And since values in the secondary columns do not have to be unique, it is no problem to assign the same service number to different countries, where necessary.

The organization has also divided the world into sales regions, where each country belongs to exactly one sales region (but usually most regions consist of several countries, for example "North America", "Europe", "South Pacific", etc.). For this, another secondary column called "Sales Region" is added to the "Countries" lookup table, where the sales region for each country entry is defined (i.e. it defines to which region each country belongs). Again, this takes advantage of the fact that entries in secondary columns do not have to be unique, which makes it possible to define the same region for different countries.

With this setup, the secondary columns in the lookup table Countries can now be used in the following ways (these are examples only; there are many other additional usages):

• Include the service phone number in all mailings, so that each recipient sees the correct service number for the country he lives in. This can be done by embedding the following system drop-in into the email:

  {{*Calc SecondaryValue(&COUNTRY;, "Service Number")}}

• Display the service phone number as part of the subscription profile (read-only), inside of the subscriber area. This can be done by creating a read-only derived field, where the derivation rule is set to "Mirror secondary lookup table value of a single select field", with the settings: "Mirror value of field: COUNTRY" and "Use secondary value: Service Number".

• Create a report that tells you how many subscribers there are from each sales region.

  This can be done by performing a demographic breakdown report. In this report, hide all profile columns except for the "COUNTRY:Sales Region" column.

• Create a tracking report that tells you how many recipients from each sales region clicked on a certain link (or viewed the email, or similar). This can be done in two steps: First, add a hidden derived field "REGION" to the dataset, where the derivation rule is set to "Mirror secondary lookup table value of a single select field", with the settings: "Mirror value of field: COUNTRY" and "Use secondary value: Sales Region". Once this field has been added, send mail job with personal or anonymous tracking to the recipients (if anonymous tracking is selected, make sure to include the "REGION" field in the anonymous profile). After the job has been delivered and the tracking events have been collected, you can create a tracking report that counts events for the tracked link in question (or for HTML display events, etc.). Execute the report and from the report chart, hide all profile fields except the "REGION" field. The result shows the number of clicks for the tracked link in question, separated by sales regions, as desired.