You can insert relational table data into your email template by editing the mark up. It's important to note that the relational table classic query structure doesn't honor daylight saving time and cannot be modified to do so.
Overview
When you insert relational table markup, you can remove or rearrange the data to better suit your email template's needs or formatting.
Important: Do not delete the %%RT_ elements, such as %%RT_HEADER_BEGIN%%. Removing these elements breaks formatting and causes the email to render improperly.
What changes can be made?
You can make a number of changes to the mark up to obtain your desired results and layout.
The following code example shows how you can manipulate the mark up:
%%RT_LOOKUP_BEGIN query="/Private/BestsalesQuery1" order_by="" max_rows="10" %%
%%RT_HEADER_BEGIN%% <table> <tr>
<td>BestSales Representative</td>
<td>Phone Number</td>
<td>Email Address</td>
</tr> %%RT_HEADER_END%%
%%RT_ROW_BEGIN%% <tr>
<td>%%Sales Rep Name%%</td>
<td>%%Phone%%</td>
<td>%%salesrep email%%</td>
</tr> %%RT_ROW_END%%
%%RT_FOOTER_BEGIN%% <tr><td colspan="3"></td></tr> </table> %%RT_FOOTER_END%%
%%RT_NOT_FOUND_BEGIN%%No Rows Found%%RT_NOT_FOUND_END%%
%%RT_LOOKUP_END%%<br />RT_LOOKUP_BEGIN |
This is where you can place the relational table personalization into the template. It references the relational table query that you created prior to being able to insert it into the template (query=). |
order_by |
Controls the order of the records that are returned by the query that is referenced above. For example, if you pull the recipient’s last purchases into the template, you can state that you want them to be sorted by the “Purchase Date” field in the relational table. Otherwise, they would be returned in the order that they appear in the relational table. This gives much more structure and order to the records returned. It also places much more emphasis on the records you deem more important (as evidenced by the next parameter). |
max_rows |
This field controls the maximum number of records that are returned by the relational table query. For example, you might want the last 5 purchases to show up only. To do that, you need to set the max_rows parameter to 5. Additionally, you must order by the Purchase_Date field so that the 5 most recent orders are returned. |
RT_HEADER_BEGIN and RT_HEADER_END |
These are the bookends for the table Headers. If you don't want headers, simply delete everything in between %%RT_HEADER_BEGIN%% and %%RT_HEADER_END%% |
%%RT_HEADER_END%% |
By default, all of the relational table fields that you selected to be available in the relational table query (default is all fields) appears here in this field. You can delete the fields that you do not wish to display. |
RT_ROW_BEGIN and RT_ROW_END |
This field dynamically creates a table row for each record that is returned for the query (restricted by the max_rows parameter and the order controlled by the order_by parameter. You can even create a separate table structure inside this section to more tightly control placement of the fields. |
RT_FOOTER_BEGIN and RT_FOOTER_END |
Do not modify this parameter. |
RT_NOT_FOUND_BEGIN and RT_NOT_FOUND_END |
This parameter controls what is displayed for the contact if they have no records returned by the RELATIONAL TABLE query, but were included in the list/query being sent to. Though there can be some scenarios where you would want to include this, best practice is to use a query that only includes individuals who also have matches in the Relational Table. |
Edit the Font
<td><font size="1" face="Arial" colo r =” red”>Flight
Number</font></td> - Change the font size, face and font color, just add the string highlighted in both in front and behind each Table Field.
Example: If you wanted to target the most recent entry for a contact who has multiple rows of data within the Relational table, you could use:
order_by='your date
field DESC' max_rows='1' %%
Setting Max_Rows as 1 means you retrieve only 1 record.
Order by 'your date field' would relate to something like purchase date.
By adding DESC to this you are requesting that the field is put into Descending date order with the most recent at the top. This combined with the Max rows means you will retrieve the most recent record.
Remove personalization rows in relational table data markup
When inserting relational table markup in your template, you want to exclude a few rows. How can you do this without removing important markup?
When you insert relational table markup into your email template, Acoustic Campaign adds HTML and SQL query markup that includes header rows and personalization fields from the relational table. You might want to remove some of these fields, but you must be mindful not to remove important markup used to render the RT information (ie. %%RT_LOOKUP_BEGIN, %%RT_HEADER_BEGIN%%).
To remove personalization markup without removing RT markup, follow this example.
You'd like to remove 'reservenum' and 'modelnum':
Make changes as shown below. Markup in bold should not be removed.
%%RT_LOOKUP_BEGIN query="/Private/rt3 - dump" order_by="" max_rows="10" %%
%%RT_HEADER_BEGIN%%
<table>
<tr>
<td>modelnum</td> <-- DELETE THIS
<d>reservenum</td> <-- DELETE THIS
<td>supernumber</td>
</tr>
%%RT_HEADER_END%%
%%RT_ROW_BEGIN%%
<tr>
<td>%%modelnum%%</td> <--DELETE THIS
<td>%%reservenum%%</td> <-- DELETE THIS
<td>%%supernumber%%</td>
</tr>
%%RT_ROW_END%%
%%RT_FOOTER_BEGIN%%
<tr><td colspan="3"></td></t>
</table>
%%RT_FOOTER_END%%
%%RT_NOT_FOUND_BEGIN%%No Rows Found%%RT_NOT_FOUND_END%%
%%RT_LOOKUP_END%%
Your revised RT markup looks like this.
%%RT_LOOKUP_BEGIN query="/Private/rt3 - dump" order_by="" max_rows="10" %%
%%RT_HEADER_BEGIN%%
<table> <tr>
<td>supernumber</td>
</tr>
%%RT_HEADER_END%%
%%RT_ROW_BEGIN%%
<tr>
<td>%%supernumber%%</td>
</tr>
%%RT_ROW_END%%
%%RT_FOOTER_BEGIN%%
<tr><td colspan="3"></td></tr>
</table>
%%RT_FOOTER_END%%
%%RT_NOT_FOUND_BEGIN%%No Rows Found%%RT_NOT_FOUND_END%%
%%RT_LOOKUP_END%%
Only 'supernumber' renders in the template when the email deploys.
Relational table query returns multiple rows
It is possible that a relational table query might return multiple rows of data. OrderBy can be used to make sure the correct row is displayed.
OrderBy is optional. If it’s not included, the result of the query can be indeterminate. When you use OrderBy, the format is %%RT::path to
query::field name::order by fields%% Multiple OrderBy fields are supported if they are comma-separated. The default is ascending order. However, asc (ascending) or desc (descending) can be specified for each OrderBy field.
Example:
Amazing Savings on Your %%RT::/Shared/All_Services_New::Service::cost asc, service date desc%%
Result:
Amazing Savings on Your Oil Change
Use table queries with child table criteria to add personalization in emails
- Navigate to Content Builder by going to Original email.
- Select a contact source and click the Insert Relational Table Data icon.
- Select the query you wish to use and click Select. Queries are listed by the name of the relational table on which the query was performed.
- Click Insert Markup and Submit to create a table listing all Parent Fields. To display a Child Field, select the field name from the Table Fields drop down and click Submit.
- Click Insert Markup. Use the guidelines below to edit the generic HTML that is produced.
You can change the font size, face, and font color by adding the highlighted string in front and behind each table field:
<td><font size='1' face='Arial' color='red'>Flight Number</font></td>
- Click Insert Markup. Use the guidelines below to edit the generic HTML that is produced.
Note: You must add the table name into the code. For example %%Child_Table_Name.FieldName%% instead of just %%FieldName%% should be used for inserting personalization into emails when using child relational tables.
| Attribute | Description |
|---|---|
| RT_LOOKUP_BEGIN | Language indicating where to insert the Relational Table personalization into the template. It is references the Relational Table query you created earlier (i.e., query=/Private/Query Name'). |
| order_by | Allows you to control the display order of records returned by the query referenced above. For example, if you were pulling the recipient's last purchases into the template, you could state that you wanted them to be sorted by the "Purchase Date" field. Otherwise, records would be returned in the order that they appear in the Relational Table. This gives much more structure and order to the records returned. It also places emphasis on the records you deem more important (as evidenced by the next parameter). Insert ASC after your order_by field to sort in ascending order or DESC after your order_by field to sort in descending order. You can use multiple order_by fields as long as they're separated by a comma. |
| max_rows | Controls the maximum number of records you want inserted from the Relational Table query. If we only want the last 5 purchases to appear, we would set the max_rows parameter to 5 AND order_by to the 'Purchase Date' field. |
| RT_HEADER_BEGIN and RT_HEADER_END | These are the bookends for the table Headers. If you don't want headers, delete everything between the %%RT_HEADER_BEGIN%% and the %%RT_HEADER_END%% tags. By default, all Parent Relational Table fields will appear here. If you want to add a Child Relational Table field, add <td>Child Field Name </td> to the RT_HEADER section. If you don't want a field to be displayed, delete the row for that field from the RT_HEADER section and the RT_ROW section. |
| RT_ROW_BEGIN and RT_ROW_END | This creates a table row for each record returned for the query (restricted by the max_rows parameter and ordered by the order_by parameter). If you want to add a Child Relational Table field, select the child field name from the Table Fields drop down menu. You can even create a separate table structure inside this section to more tightly control placement of the fields. |
| RT_FOOTER_BEGIN and RT_FOOTER_END | Do not edit this attribute. |
| RT_NOT_FOUND_ BEGIN and RT_NOT_FOUND_END |
Controls what is displayed for the contact if they have no records returned by the Relational Table query, but were included in the Contact Source being sent to. There may be scenarios where you would want to include this but the smarter design is to use a query that only includes individuals who have matches in the Relational Table. |
Sort a relational table by field and order in an email edit section
Add ASC or DESC to the value to change whether the ordering is ascending or descending.
Examples:
Using a single Order_by field.
Suppose that your field is named 'Data1.
%%RT_LOOKUP_BEGIN query="/Shared/QueryName" order_by="Data1 DESC" max_rows="10" %%
Using multiple Order_by fields:
Suppose that you have a field named 'item name' and a field named 'item price', you can effectively order them as follows.
%%RT_LOOKUP_BEGIN query="/Shared/QueryName" order_by="item name DESC, item price ASC" max_rows="10" %%
Note Field names must be separated with a comma.
This logic displays in the list, first alphabetically by item name (starting with Z) and if two items had the same name, it the item of the highest price is listed first.
Edit relational table data displayed in an email
Follow this procedure to modify the way your data appears in an email.
You must be familiar with HTML to modify the markup language associated with relational tables.
- Click the Relational Table Data icon on the top left of the table.
- Click Edit to modify the data.
The Markup page displays. Here you can edit the HTML code that controls the way your data appears in an email.