ESL examples using relational data

The data that these ESL examples make use of comes from the Relational Data Service (RDS). Access to RDS details enables you to build highly complex personalization rules.

Product-recommendation is a table view which relates RDS data from different tables. Its parameter is a field on the basis of how you want to identify the contact. This identifier field is the email address in this example.

Note:

The reference field for this campaign is id_othercampaign, and the records are sorted by the value of the position field, in an ascending order:

• Display the title of the sale where position=1
• Display the discount associated with this first sale
• Add a comma
• Display the title for the 2^nd sale
• Add a comma
• Display the title for the 3^rd sale
• Add a comma
• Display the title for the 4^th sale 

In the ideal scenario that no field is ever empty. This can be achieved using the following code:

{#Going through the first 4 records in the view#}
{%foreach sale in rds.Connection_name.em_othercampaign('1') limit 4%}
	{#Displaying the title#}
		{{sale.title}}
	{#If we’re in the first iteration, show the discount too#}
		{%if loop.first and sale.max_discount_fmt != ''%}
				{{sale.max_discount_fmt}}
		{%endif%}
	{#If we’re not in the last iteration, add a coma#}
		{%if not loop.last%}

		{%endif%}

{%endforeach%}

• All client-specific data is in bold
• The (‘1’) here is the reference field, so we’re looking in the view for records where id_othercampaign is equal to 1.

However, the title is sometimes empty as you can see in the screenshot at the top of this email.

One way to fix that is to simply perform a check to see of the title is empty:

{#Going through the first 4 records in the view#}
{%foreach sale in rds.Connection_name.em_othercampaign('1') limit 4%}
	{%if sale.title != ''%}
			{{sale.title}}
			{%if loop.first and sale.max_discount_fmt != ''%}
					{{sale.max_discount_fmt}}
			{%endif%}
			{%if not loop.last%}

			{%endif%}
    	{%endif%}
{%endforeach%}

• All client-specific data is in bold
• added check whether the title is empty is in italics 

However, this is not perfect because:

• If the title of the first sale is empty, we will not show any discount amount - the subject line will be a simple list of brands names.
• If all the titles for the first 4 sales are empty, there will be no data at all.

We can deal with this and build two separate foreach loops. One to create an array that will store all non-empty titles, and then the second to iterate through the created array.

{#Creating 2 arrays to store the titles and discounts#}
{%set salesNames=[]%}
{%set salesDiscounts=[]%}

{#looping through all the sales in the table, and storing the non empty titles and discounts in the arrays#}
{% foreach sale in rds.Connection_name.em_mothercampaign_today_fr('1') %}
			{%if sale.title != '' %}
  {% set salesNames = salesNames|merge([sale.title]|raw) %}
  {% set salesDiscounts = salesDiscounts|merge([sale.max_discount_fmt]|raw) %}
  {%endif%}
{%endforeach%}

{#Displaying the first 4 sales that do have a title#}
{% foreach saleWithTitle in salesNames limit 4 %}
				{{saleWithTitle|title}}
       {%if loop.first and salesDiscounts[0] != ''%}
       {{ salesDiscounts[0]|lower }}
       {%endif%}
       {%if not loop.last%}
       ,
       {%endif%}
{%endforeach%}