How to create and use datasets

Dataset and key column: Definitions

In Shared Email Templates, a dataset is a set of values organized into a table from which you can retrieve data for your email messages. An essential category of a dataset is a key column.

The key column is always the leftmost column of your dataset. It can also be called an 'identifier' column because there you'll keep values that help you identify the rows from which data will be taken.

It's very important that all values in your key column are unique, otherwise the app will always take the first row with the value you indicate, even if there are several duplicates of this value in other rows of the key column.

Create a dataset

In this section, you'll learn how to create a dataset from scratch (or import a TXT or CSV file) and how to connect an Excel table from OneDrive or SharePoint.

To start creating a dataset, right-click a folder and select New Dataset:

In your default browser, a new tab with the Shared Email Templates web app will open:

Give your dataset a name and select one of the three options:

• Simple lets you create a dataset manually in the add-in or import a dataset from a TXT or CSV file. The maximum size of a simple dataset is 32 rows, 32 columns, and 512 symbols in each cell.
• Excel Table from OneDrive lets you connect an existing Excel table located on OneDrive to the add-in and use it in templates.
• Excel Table from SharePoint lets you connect an existing Excel table located in SharePoint to the add-in and use it in templates.

Edit datasets

On the Shared Email Templates pane in your Outlook, select a dataset that you want to edit and click the Edit in browser icon:

To edit the name of your dataset or change any value the dataset contains, simply click it and start typing. To add columns or rows, use the plus sign:

As soon as the necessary changes are made, don't forget to click Save.

Set default rows

If you use a template with a table bound to a dataset, you can get some rows of the dataset selected automatically when you insert the template into an email message. You just need to set those rows as default ones:

1. Open a dataset for editing in the web app.
2. Click the plus sign next to the rightmost column.
3. Type in Default Row as a title for the new column:
   
   Note. The title is case-insensitive. It can be entered in any letter case, but the text should be unchanged. Otherwise, default rows won't be selected automatically.
4. Enter any symbol or text into the necessary cells in the Default Row column. When you paste the template into an email message, the key values from the rows you marked will be selected automatically.
   
5. Save the changes.

When inserting a template with a table bound to the dataset into an email message, you'll see that the default key values are checked in the dialog window:

In case you use the ~%WhatToEnter macro to paste a specific value from the dataset, the key value from the first default row will be preset in the macro dialog window:

Use datasets in templates

There are two ways to add a value from a dataset to a template: either put a placeholder and select a value while pasting a template into an email message or insert a fixed value from a dataset into a template. In the first case, make use of the ~%WhatToEnter macro. In the second case, rely on the ~%DatasetValue macro.

Bind a table to a dataset

If you bind a table in your template to a dataset, the add-in will use specified key values to pull rows and fill in the table when you paste the template into an email message.

To add a table to a template, open the template in the Edit mode and click the Table icon on the toolbar:

You can learn more about tables in our How to create and format tables blog post.

1. Put the cursor in any cell of the row that you want to fill in with values from a dataset, right-click and select the Bind to dataset option:
   
   Note. Reserve just one row for the values to be retrieved. After you bind a table to the dataset, you'll be able to insert as many rows as you need when pasting the template into an email message.
2. Enter the title for the window that will appear when you paste your template and choose the necessary dataset from the dropdown menu:
   
   Click OK.
3. As soon as the Select dataset dialog window closes, the add-in fills in empty cells in the row with the names of columns from the specified dataset:
   
   The ~%[ ] parameters with the column names populate the cells in the same order the columns are present in the dataset. In case of mismatching order, you can edit the ~%[ ] parameter by typing in the correct column name in the square brackets or you can copy the ~%[ ] parameter and paste it into the necessary cell.
   Note. If there is some text in cells of the selected row, the ~%[ ] parameter won't be inserted automatically into these cells. You can add the ~%[ ] parameter with a column name manually.
4. Save the template.

When you paste such a template into an email message, you'll see the dialog window with the specified title and the list of key values from the selected dataset:

Select the necessary checkboxes and click OK.

If your dataset has default rows, their key values are already selected in the dialog window:

A table filled in with the values from the selected rows of the dataset will appear in your email message:

Bind a list to a dataset

Follow the steps below if you want any of your templates to contain a list bound to a dataset. This is an example of such a dataset:

1. Open your template in the Edit mode.
2. Decide whether you want to get a bulleted list or a numbered one and click the corresponding icon on the toolbar (or click the arrow that is to the right of the icon of interest to choose from the options available).
3. Right-click the line where the first list item is supposed to appear (to the right of the symbol that you already have) and select the Bind to dataset option:
   
4. In the dialog window, type the necessary title and pick the dataset your list has to be bound to:
   
5. A list item appears in your template. The header of the dataset column is in square brackets:
   
6. Click Save.

When pasting this template into an email message, you'll see a dialog window showing the values that the key column contains:

Pick any you want and click OK:

The list inserted into the message body along with the template will contain the items you have selected: