In order to use a table or view for personalization, you must select which fields will be used as reference fields. Each table or view can only be used with one set of reference fields (used together). Relational Data uses reference fields as a key to populate content into placeholders in your messages. Typically, reference fields contain information that can be tied to a contact and it is also available in the Emarsys platform.

For segmentation the reference field is not required, but If you want to use your data for personalization, you need to set at least one reference field (in our example these are marked with red color). 

Reference fields are the means by which you determine which records are made available for personalization. To use the reference fields you have to specify how the personalization service should use them during token creation.

When you connect an external database to Emarsys, we will automatically pull all the tables and views from the database and display them on the Relational Data page, Tables & Views tab.

In our use case example, you need the following data tables and views:

Table bookings

In our example, we can connect the bookings table to the Emarsys database. In the bookings table we use contact_id and destination_city as reference fields. We use these two fields, because in our example we want to use data which can be matched to a specific contact and their destination.

View upsell_services

This view returns services, availability and related information for each city.

Emarsys database

Reference fields are used to find relevant records from your table or view. These records can have many more fields, all of which can be displayed. You can display any field of your table or view, including, but not limited to the reference fields. The content of the reference fields are often not displayed though, as they typically contain some kind of identifiers.

Example: In our use case, it is the contact_id, which helps us find the relevant content for that exact contact we will send out in our message. By using the contact_id and the destination_city as reference fields together, we can determine which value of the date_of_arrival field we have to use in the message.

By setting multiple reference fields, we will have more criteria to filter so we can get more information to decide and send back as personalized content.

Example: In our use case, we need two reference fields to filter data based on the contact_id  and the destination_city values. This way, we can find the specific contact-related field (date_of_arrival) according to the contact’s identifier and the destination city.

Imagine, if we set only one reference field, and we did not apply the destination_city field as a reference field, we would not be able to filter the destination_city to the “Vienna” value, only for the contact_id. In this case, we would get multiple matches, multiple destination cities (e.g: Berlin, Vienna, London) for the filtered contact_id and we could get the date_of_arrival for the Berlin or London, not for Vienna.

For information how to set up in the Emarsys Platform, see Setting up reference fields.

You can define a default value for the reference fields, in case the original value cannot be found in the reference field of your table or view. You can think of this as using a constant, the same value for all requests with a missing value. Tick the check box to define the default value to be used if the original value cannot be set in the reference field.

Example: Imagine sending an email to Los Angeles instead of Vienna. We also have a contact who is going there recently, but there is no discounted service available in Los Angeles. 

So in the upsell_services view there is no row for the city of Los Angeles. If you do not use default handling, the system cannot find data to display for Los Angeles. We won't be able to populate for example the service_name, so it will be left empty. Of course, there is a way to ensure that we do not send an empty email. What if we want to recommend something to those contacts who cannot be offered a service for their specific city? In this case, you should use default handling.

 If you add a row to the table with, for example, written “General” in the city field, you can set it to use it as a default value. If a default value is set and the reference field does not have a match in our table then it will return the default value.

According to our example, if you set a default value to the city field, so instead of an empty result we return the service_name and all others corresponding to the “General” city. For information how to set up a default value, see Setting a default value.

In some cases, the filtering based on the reference field returns not only one, but more records as a result. In such cases you have to define by what field to sort the records. By what field we can decide which is the first row, the second, and so on. It is worth setting up multiple fields for sorting when there are several fields that can have the same values. In this case, we can sort by the value of the second field and so on.

It is worth setting up multiple fields for sorting when one field can have the same value in multiple records, like in the bookings table, the destination_city can have the Vienna value for multiple records. In this case, we can sort by the value of the second field and, so on.

Example: In our use case, we need the nearest date to the date of arrival, so we have to sort the fields by the date of arrival in ascending order. For information how to set up in the Emarsys Platform, see Setting fields to sort.

Currently, due to technical limitations, it is not possible to apply variables for reference fields.

Example: In our use case example, the destination city is a constant value (Vienna) of the bookings table.
If we want to have a dynamic connection between the city field of upsell_services view and destination_city of the Bookings table, we should set up a variable for the city value. At the moment, this is only possible with Emarsys Scripting Language (ESL) codes.

4.    In your table or view, for the previously set reference fields add a default value, and click Save when finished.

Example:  In our use case, you need to select the upsell_services view and set a default value (General) for the reference field:

• city

For more information, see Default value.

5.    To sort your table or view you can select the field from the drop-down list and click Save when finished. In case of equal values, the next field in the list is used.

Example: In our use case, we need the nearest date to the date of arrival, so we have to sort the date_of_arrival values in ascending order.

Relational Data presets are preconfigured sets of values for the reference fields of a particular table or view. They determine which data records are referenced by the placeholder that you add to your content and in what way.

1. Go to Content -> Personalization and click Create Token.
2. Select Relational Data as the type of token to be created in the Personalization capability window.
3. In the Create Token window, click New in the Relational Data preset line, which opens the Create RDS Preset window. First, add a meaningful name to your preset.
4. Name your Relational Data preset.
   NOTE: You will have to use this name to reference your preset during token creation, so it is worth using a descriptive name.
5. Select the database connection. It is internal_dummy in our example.
6. Select the desired table or view. We select the bookings table here.
7. Set the available parameters, by editing their Type and Value.

After you have successfully created your preset, you can continue creating your token.

1. Give a descriptive name to your token to make it easier to find it while using a content editor.
2. Click Create Category in the token creation window.  Add a descriptive name to your category and click Create.
   NOTE: This is important, since you will be using these tokens in many categories, so giving descriptive names will be beneficial later while searching for your token.

Select the category you want to add the token to. A single token can be used in more than one category. You can also create a new category, for more information, see Creating a token category.
NOTE: Your tokens can be reused across campaigns. Your token category will be listed in the Personalization sidebar within the Content Creation step of the Email content creation process.

3.   Select the Relational Data preset that will be used for the token. You can select and Edit one of your previous presets or even create a New one.
4.   Select the field that you want to display in your message. In our example we used date_of_arrival.

5.   This line presents a preview of the token and the Emarsys Scripting Language (ESL) code that can be copied and inserted into blocks, too. For more information on ESL, see Emarsys Scripting Language.

The correct ESL syntax is as follows:

{{ rds.connection.table(ref1,ref2)[0].field_to_display }}

Example:

6.   The item index selects the item to be used from the array. Please note that the list starts with "0", so this means the first item, "1" denotes the second, "2" the third and so on. Use this option to choose from the values, when your database return multiple results to query. If you want to use an index, you should check that sorting is set up properly, as it defines which row will be the first, second, and so on.
7.   Check this box, if you want to make sure that the message will not be sent out, if we were not able to populate the personalization token. In our example if we had not set the default value for the city field in the upsell_service view, we could not have populated the personalization token. In that case, if you check this box, the message will not  be sent.
8.   Add a fallback text that will be displayed if the value of the field cannot be obtained for some reason. Using a fallback text and marking the field as a requirement for sending are mutually exclusive.
9.  You can add formatting details of the token.