Inserting Data From a Related Data Set Into a Message

Prerequisites

The function Related Data is ready to be used for message creation when the following steps have been successfully completed:

A related data set has been created (see Create Related Data Set)
Data columns have been created in the related data set (see Import Data: Create Columns of Related Data Sets During Import)
Data has been imported into the related data set.
For more information, see Related Data: Import Data.

The name of the related data set is case-sensitive.

Inserting data into a message using a placeholder

To insert data from a related data set into a message, a placeholder is inserted into the body of the message. When the message is sent, the placeholder is replaced by data from the related data set.

The symbols <% %> define the beginning and end of the placeholder within the message text.

The elements of the placeholder are determined by two factors: (1) whether or not the related data set is linked to an attribute, and (2) whether the related data set is unique or non-unique.

(1) The placeholder contains either a related attribute or a related function, depending on whether the related data set is linked to an attribute or not. For more information see Introduction to Related Data Sets.

The placeholder contains:

a related attribute if the data is to be taken from a related data set that is linked to an attribute
the function ecx:related if the data is to be taken from a related data set that is not linked to an attribute

(2) The second decisive factor is whether the data set is unique (that is, each data set exists only once in the related data set) or non-unique (that is, a data set can exist more than once within the same related data set).

If the related data set is non-unique, a ForEach-loop is added to the placeholder.

In the following, we will take a closer look at each of the following cases:

Data is inserted from a linked related data set (unique).
Data is inserted from an unlinked related data set (unique).
Data is inserted from a linked related data set (non-unique).
Data is inserted from an unlinked related data set (non-unique).

Sorting Data Sets

The display of data from the related data sets is random and does not follow any particular order. If you would like to display the data in a particular order, there is a function for sorting data available (see ecx:sort).

It is furthermore often necessary not only to select data by using the key, but also to identify data by using additional criteria saved in other data columns. Filters for selecting data are defined with functions (see Functions for Filtering Related Data Sets).

Related Data and the CMS

If you are using Related Data in the context of a CMS, the data is inserted using a data retrieval operation that is pre-defined for each CMS template. Which paragraphs of the CMS access which data columns is preset (by your account manager)for your particular CMS template. If you are using the CMS, you only need to enter the agreed-upon key in the appropriate placeholder; it is not necessary for you to use the manually-created placeholders described below.

If it is not clear how Related Data is being used in your Mapp Engage system, contact your account manager. How related data can be inserted into a CMS message depends on the individual templates and can only be described in detail for a specific system.

Select and Import Data from a Unique Related Data Set

Data is inserted into a message using a placeholder defined by the tags <% %>. This placeholder represents the value being replaced.

Inside the placeholder, the related data set and the data set are identified by two commands.

Insert Data from a Linked Related Data Set (Unique)
The related attribute user.relatedAttribute['dataset']['column'] is used when the related data set is linked to an attribute.
The full placeholder and function reads: <%${user.relatedAttribute['dataset']['column']}%>
The data relevant for the customer is identified when the linked attribute matches the key in the related data set. Thus, data is only selected from the related data set and inserted into the message when the individual entry in the attribute is identical to the key value.
- user.relatedAttribute: Indicates that the related data set is linked to an attribute.
- dataset: This is the name of the related data set from which the data should be taken. (Multiple related data sets may be linked to one single attribute. It is, therefore, necessary to specify the exact related data set.)
- column: This is the name of the column of the related data set from which the data should be taken.
Insert Data from an Unlinked Related Data Set (Unique)
The function ecx:related('dataset', 'key')['column'] is added to the placeholder when the related data set is not linked to any particular recipient attribute. This normally means that the related data set contains data that does not pertain to any particular recipient.
The full placeholder then reads: <%${ecx:related('dataset', 'key')['column']}%>
- <% %>: Defines the beginning and end of the placeholder within the message text.
- ${ }: Defines the start and end of a function that selects the values to be displayed in the placeholder.
- ecx:related: Function to select and import data.
- dataset: The name of the related data set from which you want to take the data.
- key: The name of the key for the data set from which you want to take the data. The key can be a defined value, a value saved in an attribute or another related data set
  It is possible to create a nest of functions to access the values stored in other related data sets, instead of specifying a defined value.
- column: The name of the column within the data set from which the data to be inserted will be taken (For example, in a product catalog, the column might be the product price).

Example uses of the Key in an Unlinked Related Data Set

The different uses of the key can be demonstrated by using the example of a product catalog.

All data to be inserted into a message is stored in a related data set with the name Product Catalogue. The related data set contains numerous data sets (one for each product), and which data set is to be inserted into the message is specified by a key. In this example, we want to select and insert the column Description, which contains information about the products, into our message.

The key is a defined value

The product, and therefore the key, is the same for all recipients. The placeholder should in this case contain the specific value that identifies the data set to be inserted. The description of the product with product number 1234 is inserted in the message. The product number is the key of the data set (see Structure of XML and CSV Files (Related Data)).

In this case, the placeholder to be inserted would be: <%${ecx:related('ProductCatalogue', ‘1234’)['Description']}%>. As a defined value, 1234 must be set in single quotation marks.

The key is selected from an attribute:

The product, and therefore the key, will vary depending on the recipient. Which product and description are shown in the message depends on the recipient's last purchase. This purchase is saved in an attribute with the name Purchase. In the function, the key is therefore an attribute.

In this case, the placeholder to be inserted would be: <%${ecx:related('ProductCatalogue', user.CustomAttribute['Purchase'])['Description']}%>

The key is taken from a different related data set with a unique data set

There is another related data set with the name Shop. This related data set contains the current offers from different shops.

We want to insert the offers from the Paris shop in the message. The key in the related data set Shop is the name of the shop. The only other data stored in this related data is the Product ID, saved in the column Product. The description of the product is saved in another related data set with the name Product Catalogue.

In this case, the placeholder to be inserted would be: <%${ecx:related('ProductCatalogue', ecx:related('Shop', 'Paris')['Product'])['Description']}%>.

Instead of a key, we added a second function. The key in this second function is Paris: ecx:related('Shop', 'Paris')['Product'].

The key is taken from a different related data set which contains an attribute value

It is also possible to use the value from an attribute as the key in a related data set instead of inserting a defined value ('Paris' in the previous example). This makes is possible to automatically select a current offer in the shop most relevant to each recipient.

In this example, the shop most relevant to the recipient is saved in a custom attribute with the name PreferredShop. The related data set CurrentOfferPerShop holds one record per shop. The records in this data set contain two values - the shop name (key column) and the special offer for each shop, represented by the ProductID in the Product column. This related data set is not linked to an attribute value (note the difference in syntax).

To get the ProductID for the special offer in the shop most relevant to each recipient, we use the function ecx:related('CurrentOfferPerShop', user.CustomAttribute['PreferredShop'])['Product']. This information is used to look up the product description (or any other information about that product) from the related data set ProductCatalogue, which uses ProductID as the key column.

The placeholder in this case reads: <%${ecx:related('ProductCatalogue', ecx:related('CurrentOfferPerShop', user.CustomAttribute['PreferredShop'])['Product'])['Description']}%>.

Import Data from a Related Data Set with Non-unique Data Sets

If you have a related data set whose data sets are not unique (that is, multiple data sets are saved with the same key), you must use a ForEach-loop in the placeholder.

A ForEach-loop does not just select a single data set; rather, it selects all data sets that fulfill the defined condition. The resulting data is not displayed in any particular order. If you would like to sort data or search for special data, there are special functions available for these purposes (see Functions for Filtering Related Data Sets).

Select and insert non-unique data sets from a linked related data set

Data from a linked, non-unique related data set can be inserted in a message by using user.relatedAttribute. As more than one data set is saved in the related data set, use a ForEach-loop, as follows:

<%ForEach var="count" items="${user.relatedAttribute['dataset']}" max="5"%> <%${count['column']}%> <%/ForEach%>

The ForEach-loop is constructed as follows:

<%ForEach%> </%ForEach%>: These tags define the beginning and end of the loop, respectively.
var="count": Creates a variable that will later be used for the output.
items="{ }": Indicates which values should be considered (in this case, a linked related data set).
user.relatedAttribute['dataset']: Indicates what data should be selected; that is, the records from a related data set. The parameter dataset is replaced by the name of the respective related data set.
max="5": indicates that a maximum of 5 data sets should be selected and inserted in the message. The selection of data sets is random. This data set limit is optional and can be left out. It is furthermore possible to use time filters or topic filters to define which data sets should be displayed (see Functions for Filtering Related Data Sets).
<%${count['column']}%>: is the placeholder into which the data will be entered. The column indicates the column from which the data will be taken.

Hint:

The ForEach loop cannot be added directly to a link. The following syntax is NOT correct and produces an error:

The correct syntax for this example is:

<%ForEach var="count" items="${user.RelatedAttribute['example']}" max="1"%> <a href="">linkhttp://www.domain.com?column1=<%${count['column1']}%>">link</a> <%/ForEach%>

Example:

There is a related data set with the name Purchase in which all customer purchases are stored. This related data set is linked to the attribute Customer Number. The row key of the related data set is the customer number. There are multiple columns of data: the product ID of the purchase is saved in the column Product; the purchase price is saved in the column Purchase; and the purchase date is saved in the column Date. Since a customer can make unlimited purchases, the data set is not unique (that is, each key (customer number) may be used for an unlimited number of data sets).

You would like to create a newsletter in which the purchase date and price are inserted in the message as a table with a maximum of 3 entries. Shown with the necessary HTML tags, the ForEach-loop is constructed as follows:

<table>
    <tbody>
        <%ForEach var="count" items="${user.relatedAttribute['Purchase']}" max="3"%>
            <tr>
                <td><% ${count['Price']}%></td>
                <td> <% ${count['Date']}%></td>
            </tr>
        <%/ForEach%>
    </tbody>
</table>

CODE

Select and insert non-unique data sets from an unlinked related data set

Selecting and inserting data from a non-unique, unlinked related data set takes place using the function ecx:related. Since more than one data set is stored in the related data set, the following ForEach-loop is used:

<%ForEach var="count" items="${ecx:related('dataset', 'key')}" max="5"%> <%${count['column']}%> <%/ForEach%>

The ForEach-loop is constructed as follows:

<%ForEach%> </%ForEach%>: These tags define the beginning and end of the loop, respectively.
var="count": Creates a variable that will later be used for the output.
items="{ }": Indicates which values should be considered (in this case, an unlinked related data set).
ecx:related('dataset', 'key'): Indicates what data should be read; that is, a data set from an unlinked related data set (ecx:related ('dataset', 'key'). Here the corresponding names must be entered.
max="5": Indicates that a maximum of 5 data sets should be selected and inserted in the message. The selection of data sets is random.
<% ${count['column']}%>: The placeholder into which the data will be entered. The column indicates the column from which the data will be taken.

Example:

The tourist attractions of various different cities are saved in a related data set with the name City. You would like to add four attractions in Paris in your newsletter. The name of the attraction is saved in the column Name; a short description of the attraction is saved in the column Description. The key is the name of the city. Both the name and the description should appear in a table in the newsletter.

<table>
    <tbody>
        <%ForEach var="count" items="${ecx:related('City', 'Paris')}" max="3"%>
            <tr>
                <td><% ${count['Name']}%></td>
                <td><% ${count['Description']}%></td>
            </tr>
        <%/ForEach%>
    </tbody>
</table>

CODE

Sort Displayed Data

When using a ForEach-loop, multiple data sets will be inserted and displayed in random order. You can use the function ecx:sort to sort the displayed data (see ecx:sort).

Prerequisites​

Inserting data into a message using a placeholder​