How to create and use datasets

In Shared Email Templates for Outlook

Datasets may be of great help to you if you have tables with data that you would like to use in your templates.

To make use of a dataset, you need to create it in Shared Email Templates (TXT and CSV files import is supported) or to connect an Excel table that you have on OneDrive or in SharePoint.

Click the button below to open or download a quick How to use datasets cheat sheet:

Download cheat sheet

Video: Using datasets in Outlook email templates

See the full video transcript on the Webinar 4: Using datasets in Shared Email Templates page.

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:
Click New Dataset.
In your default browser, a new tab with the Shared Email Templates web app will open:
Select the type of your dataset.
Give your dataset a name and select one of the three options:

Simple dataset

  1. The leftmost column of your dataset is always the key column. Start creating a table by typing the first value there:
    Click and enter the first value.

    Tip. You can rename the key column by entering a new name:
    Click and enter the new key column name.
  2. To add rows, click the plus sign as many times as many rows you need:
    Add rows.
  3. To add columns, click the plus sign as many times as many columns you need:
    Add columns.
  4. Enter the necessary values.

    Note. To insert a macro into a cell, click the icon shown in the screenshot below:
    Insert a macro.
  5. To save your dataset, click the Save icon:
    Save your dataset.
  6. On the Shared Email Templates pane in your Outlook, datasets are marked with a special icon:
    This is a saved dataset.

Import datasets (TXT or CSV file)

You can import datasets to Shared Email Templates in TXT or CSV format. If your table is in another format, you can easily convert it to one of the required ones.

  1. Open the file that contains an Excel table that you want to use as a dataset for your templates, click Save As, and pick one of the following formats: Text (Tab delimited), Unicode Text, or CSV (Comma delimited).
    Save As
  2. Right-click a folder and select New Dataset:
    Click New Dataset.
  3. In the web app, click Simple:
    Click Simple.
  4. Then click the Import icon:
    Click Import.
  5. Select your file and click Open:
    Select your file and click Open.
  6. Your table will be imported to Shared Email Templates. Don't forget to give it a name.
Note. If your original table exceeds the maximum size of a dataset in Shared Email Templates (which is 32 rows, 32 columns, and 512 symbols in each cell), the app will cut your data while importing.
Note. If you import a CSV file, the delimiters between cells must be commas. In a TXT file, the delimiter is a tab character.

Connect an Excel table

You can link an Excel table to a dataset. An Excel workbook can be located on OneDrive or in SharePoint. Before linking an Excel table, take the following points into consideration:

  • You can connect only datasets formatted as Excel tables. To format an Excel range as a table, select it and press Ctrl+T.
  • As soon as the workbook is connected, do not remove or move it, otherwise the add-in won't be able to find it and use in templates.
  • If you use a connected Excel table as a dataset for a team template, make sure that all the team members have access to the connected Excel workbook on OneDrive or in SharePoint.
  • If you connect an Excel table with more than 1,000 rows, it may slow down the add-in performance.

To connect an Excel table, take the following steps:

  1. Depending on the location of your file, click Excel Table from OneDrive or Excel Table from SharePoint:
    Connect an Excel workbook from OneDrive or SharePoint.
  2. Pick the necessary workbook and click Open:
    Select your file.
  3. Choose a table and click Select:
    Select a table.
  4. There are two icons next to the name of the selected workbook: Open Workbook and Change Workbook. Use them if needed. On making sure that the right workbook and table are selected, name your dataset and click Save:
    The selected file and table
  5. On the Shared Email Templates pane, a connected Excel dataset is marked with a small Excel icon:
    A connected Excel dataset

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:
Here is 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:
Edit your dataset.

Note. The maximum size of a dataset is 32 rows, 32 columns, and 512 symbols in each cell.
Tip. To change the order of columns, you can drag and drop them. The only exception is the key column, it will always be the first one and can't be moved.

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:
    The Default Row 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.
    Mark default rows.
  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:
The default key values are automatically selected.
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:
A preset value

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.

WhatToEnter: Add placeholders

  1. In the template editor, put the cursor where you want to see a value from a dataset and click the Insert macro icon:
    The Insert macro icon
  2. In the Search field, start typing "what to enter" to find the macro in the list. Select What to Enter:
    What to Enter
  3. Choose Dataset from the dropdown menu:
    Pick Dataset.
  4. You'll see several fields to fill in:
    Fill in these fields.

    • Window title
      It will be displayed in the What to enter dialog window when you paste a template into an email message. Enter something clear and understandable.
    • Select dataset
      Choose the dataset that contains the necessary values.
    • Select column
      Specify the column from which the required value will be retrieved.

    Click the Insert button.

    In the template, the macro placeholder will look like in the screenshot below:
    The macro placeholder

  5. Let's get some other data from the same sample dataset with the ~%WhatToEnter macro. The color of a bag is needed:
    The ~%WhatToEnter macro should be inserted once again.

    The size has to be specified as well:
    One more macro placeholder is going to be added.

    Finally, the template will look like this:
    The template is ready.

  6. Save the template and insert it into an email message. In the macro dialog window having the title that was specified as Window title, select the necessary value from the key column of the dataset and click OK:
    Select a value from the key column.

    Note. If some rows in your dataset are marked as default ones, the key value from the first default row is preset in the macro dialog window.
  7. With the values retrieved from the dataset, your email message will look like this:
    Values from the dataset in the message body

DatasetValue: Add fixed values

The ~%DatasetValue macro retrieves any value you need from the dataset that you specify.

  1. Click the Insert macro icon and type in "dataset value" in the Search field. Select Insert Dataset Value:
    Insert Dataset Value
  2. Choose the necessary items from the dropdown menus that you'll see in the dialog window:
    Select items.

    • Select dataset
      Choose the dataset that the add-in will pull the necessary value from.
    • Select column
      Choose the column from which the necessary value is going to be extracted.
    • Key value (value from the key column)
      It indicates the row from which the necessary value has to be taken.
  3. You can add several ~%DatasetValue macros to the template, and it will look like this:
    The ~%DatasetValue macro in a template
  4. When you paste such a template into the message body, the add-in doesn't ask to select anything and simply retrieves the values you specified:
    The ~%DatasetValue macro in action

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:
Insert a table into a template.
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:
    Bind a table to a dataset.

    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:
    Enter a title and pick a dataset.
    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 column names have appeared in the table.
    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:
Choose key values.
Select the necessary checkboxes and click OK.

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

Tip. To quickly find the value of interest, start typing its name in the Search field.
Tip. If you want to add all rows from the dataset to your table, click the topmost checkbox and get all the list items selected:

Select all the list items.

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

Unbind a table from a dataset

To unbind a table from a dataset, right-click the bound row when editing your template and choose the Unbind from dataset option from the menu:
Unbind a table from a dataset.

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:
An example of a dataset that a list can be bound to

  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:
    Bind a list to a dataset.
  4. In the dialog window, type the necessary title and pick the dataset your list has to be bound to:
    This is a dialog window.
  5. A list item appears in your template. The header of the dataset column is in square brackets:
    A bound list in a template
  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:
This is an example of the dialog.
Pick any you want and click OK:
Select the necessary checkboxes.

Tip. If all the values are needed, click the very first checkbox (next to the Search field). All the checkboxes will be selected:

Select all the checkboxes.

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

Unbind a list from a dataset

To unbind a list from the dataset it's bound to, open your template in the Edit mode, right-click the list item, and select the Unbind from dataset option:
Unbind a list from a dataset.

Responses

Is it possible to connect to a table in Google sheets, as opposed to one in Excel? If not, would you please consider adding this functionality?

We use a google form to collect onboarding information from new clients which feeds into a master client list in Google sheets. If we could connect directly to this file it would improve the functionality ten-fold. (as it currently stands we will have to manually transcribe the new customer info from the google sheet over to the "simple" format data set in Shared Email Templates every time we get a new sign up, which is way too tedious and time consuming)

Is it possible to access a value from a dataset without asking the user for the key column value? This would be helpful in constructing templates from a common data source. For example:

~%WhatToEnter[{dataset:"Bags",column:"Bag name",value:"Frida-01"}]

Hi Mark,

Sorry, your idea is not quite clear. Is my understanding correct that you want, say, to have two templates with different data from the same dataset?

Hi Eugene,

I'm specifically referring to including data from a dataset in a template *without asking the user to choose the specific record/key column value*.

In my example above, assume that I am creating a template to specifically showcase the Frida bag. I want to pull in the bag name and colour from the dataset, but I don't need to ask the user which bag as I know, for this template.

I had added a new parameter to the WTE example above, *value*, the intent of which would be to grab the data for that Bag without asking the user to specify which bag. It would be as if the user had answered "Frida-01" to the question that would usually pop up when using a WTE with a dataset.

Post a comment

Seen by everyone, do not publish license keys and sensitive personal info!

If you have any questions or issues with this add-in, please feel free to post your concerns in the comments area. As soon as we answer, a notification message will be sent to your e-mail. If you do not want to share your thoughts in public, please contact us at support@ablebits.com.