Create a relational table query

Relational table queries are used to add relational table markup in emails.

A relational table query must be built before the markup HTML can be inserted into an email.

1. Create and name the query.
   1. Go to Relational Tables.
   2. Select the relational table that you want to use.
   3. Click Create Table Query.
   4. Enter the name of your query in the Save Query As field.
   5. Click Browse to choose where to save the query.
   6. Click OK. The Query dialog box displays for editing the new query.
2. Define your query data.
   1. Build your query using the editor. Keep in mind that the personalization system essentially appends a WHERE clause to your query that say Email = current user to execute personalization in mailings. This means in most cases, the query you build needs to be a query that returns all rows present in the relational table.
3. When you’re done constructing your query, you can click Save or click Save and Calculate to run the query. If you choose to Save and Calculate, take the following steps.
   1. (Optional) Review the query summary information. If you want to revise your query, click the Query Summary breadcrumb.
   2. (Optional) Check Upon completion of the data job, send an email notification to the address below and enter your email to be notified when the query calculation data job is complete.
   3. Click the Submit button.
   4. Click the Data Job ID link to view the data job’s progress. When the data job is complete, you find the number of contacts in your relational table.
4. Insert relational table markup into your email template.
   1. Create a new template or go to an existing one.
   2. Click Settings > Contacts to assign a contact source to the email template. The contact source must have an association with the relational table where you built your query.
   3. You may now proceed with adding personalization to your email using the personalization icon helper on the email text formatting toolbar.

Note: If you use a relational table query to retrieve a URL value for use as a link in an email, the entire anchor <a> element for your link must be created using a Dynamic Link Leader (DLL). You cannot reference the href value for your link, for example: href="%%MY_URL_VALUE%%". If you do, Acoustic Campaign will rebuild and redirect the link with tracking information, and the resulting link will be incorrect in the actual email that is sent. If you format the href value in your link as href="%%MY_URL_VALUE%%" and use the Personalization function to test, the correct URL value is pulled in from the relational table but you receive a false positive where it appears to work correctly. However, after the email is sent, the link is incorrect.

Include relational table fields in a database query

Note: Relational table expressions in both database and program queries are only available to organizations that have relational tables enabled.

To include relational table fields in a database query, you must Associate the relational table with the database. Databases that have relational tables associated with them allow you to include relational table fields in your database and program queries.

In order to Associate a relational table with a database, you must first have at least one corresponding field in both the relational table and the database that contains the same data. This could be a customer ID or purchase order number.

Create a query using standard or custom zip code relational table

You can create a query using the standard or custom zip code relational table using query.

1. From the Data menu on the main toolbar, select Create under Queries.

   This opens a Save Query As dialog box where you select which database to query, as well as a name for your query. Make sure you select Query from the Type drop-down.

2. Click Relational Table and choose a relational table from your Shared or Private folder and then click Done.
3. Select Standard Zip Codes or Custom Zip Codes and then click Done.
4. Save your query.

Rename a relational table query

Campaign allows users to change the name of a relational table query (not a database query). However, there is a risk comes with this name change.

The main purpose of a relational table query, is to insert relational table data into a email, if the contact source has been associated to the relational table. When relational table data has been inserted into the email, the source code of the relational table data reference is using relational table query name.

For example, here is the source code of relational table data in email:

%%RT_LOOKUP_BEGIN query='/Private/Order Information order_by='' max_rows='10' %%
%%RT_HEADER_BEGIN%% <table> <tr>
<td>Order Number</td>
<td>Price</td></tr>
%%RT_HEADER_END%%
%%RT_ROW_BEGIN%% <tr>
<td>%%Order Number%%</td>
<td>%%Price%%</td></tr>
%%RT_ROW_END%%
%%RT_FOOTER_BEGIN%% <tr><td colspan='2'></td></tr> </table> %%RT_FOOTER_END%%
%%RT_LOOKUP_END%%

Note: the above example is using relational table query named 'Order Information'. If you change the relational table query name 'Order Information' to something else, this email will fail if it is sent.

Capture specific dates in relational table queries

Relational table queries can be used to harvest data to populate emails. Relational table queries are universally based on the classic query structure, and as such lack some of the tools and functionality new queries offer.

One such criteria is the ability to capture specific dates, such as yesterday.

A workaround criteria can be used to capture such date metrics. You can use the Is Between operator, as follows:

[DATE_FIELD] is between [Current Date] -1 and [Current Date] -1

This captures contacts that have a date field with a value 1 day behind the current date.