Ukraine flag War in Ukraine. Here's what Ablebits is doing to make sure our team and projects are safe.

How to create and use datasets

In Shared Email Templates for Outlook

Datasets may be of great use to you if you have tables with data that you would like to use in your templates. Datasets let you pick the necessary piece of information from the cell you indicate. To use a dataset, you need to create it in Shared Email Templates (or import a TXT or CSV file) and use the corresponding option of the ~%WhatToEnter macro. Let us see how.

Here is a quick How to use datasets cheat sheet, click on the button below to open or download it:

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 in a table from which you can bring data to your templates. 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 will keep values that help you identify the rows from which data for your templates will be taken.

It is 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 dataset

In this section, you will learn how to create a dataset directly in Shared Email Templates, import a dataset from a TXT or CSV file, or connect an Excel table located in OneDrive or SharePoint.

  1. To create 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 application will open:
    Create dataset in a browser.
    Here, you are to 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. To start creating a table, click on the cell and type in the first value in the key column:
    Click and enter the first value.

    Tip. For your convenience, we recommend that you use some understandable identifiers in your key column.
    Tip. You can rename the key column by clicking on it and entering a new name:
    Click and enter the new key column name.
  2. To add rows, click Plus as many times as many rows you need:
    Click Plus to add rows.
  3. To add columns, click Plus as many times as many columns you need:
    Click Plus to add columns.
  4. To save your dataset, click the Save button:
    Save your dataset.
  5. In the tree, you will identify a dataset by a specific icon near its name:
    Saved dataset in the app.

Import datasets (TXT or CSV file)

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

  1. If you have an Excel table with your data, open it, click Save As and pick one of the following formats: Text (Tab delimited), Unicode Text, or CSV (Comma delimited):
    Save Excel dataset.
  2. Right-click a folder and select New Dataset:
    Click New Dataset.
  3. Click Simple:
    Click Simple.
  4. In the web app, click on 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 255 symbols in each cell), the application 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 Excel table

You can link an Excel table to a dataset. The Excel workbook can be located in OneDrive or SharePoint. Before linking an Excel table, please 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 the Ctrl + T buttons.
  • As soon as the book 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 in a shared template (located in a team), make sure all the Shared Email Templates team members have access to the connected Excel workbook in OneDrive/SharePoint.
  • If you connect an Excel table with more that 1000 rows, it may slow down the add-in performance.

To connect an Excel table, take the following steps:

  1. Click on the corresponding button to select the book depending on its location:
    Connect Excel table from OneDrive or SharePoint.
  2. Pick the book with the table you want to connect and click Open:
    Select Excel table from OneDrive or SharePoint.
  3. Choose the table and click Select:
    Select Excel table.
  4. Near the workbook name, you will see two icons: Open Workbook and Change Workbook. When you are sure that the right book and table are selected, click Save:
    Selected Excel table.

  5. In the list, the connected Excel dataset is marked with a small Excel icon:
    Excel dataset in the tree.

Edit datasets

In the tree, stand on the dataset you want to edit and click the Edit in Browser option in the preview pane:
Click Edit in Browser.

To edit a name of a dataset or a value in any cell, simply click on it and start entering the new text. To add columns or rows, click the Plus signs below and on the right:
Edit your dataset.

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

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

Set default rows

If you use a template with a table bound to a dataset, you can mark some rows of the dataset to be selected automatically when inserting this template into a message. To do this, you need to set one or several rows as default ones:

  1. Open a dataset of interest for editing in the web app.
  2. Click the + sign next to the rightmost column.
  3. Type in Default Row as a title for the new column:
     Name a column as Default Row.

    Note. The title is case-insensitive so it can be entered in any letter case, but the text should be unchanged. Otherwise, the default rows will not be selected automatically.
  4. Enter any symbol or text to the necessary cells in the Default Row column. When you insert the template into a message, the key values from the rows you mark as non-empty in the Default Row column will be selected automatically.
     Mark default rows with a symbol.
  5. Save the changes to the dataset and paste the template into a message.

When inserting a template with a table bound to the dataset, the default key values are already 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 What to enter window:
 The default value is preset in the WhatToEnter 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 the message or insert a fixed value from a dataset into a template. In the first case you'll need the WhatToEnter macro, in the second—DatasetValue.

WhatToEnter: Add placeholders

  1. In the template editor, put the cursor where you want to insert a value from a dataset and click the Insert macro icon:
    Insert Macro.
  2. Double-click ~%WhatToEnter or select it and click the Select button:
    ~%WhatToEnter macro.

  3. Pick Dataset in the drop-down list:
    Pick Dataset.

  4. You will see several fields to fill in:
    Fill in the macro fields.

    • Window title will be displayed in the What to enter window when you insert a template. We recommend that you enter here something very clear and understandable, so you and your teammates immediately understand what this field is about.
    • Select a Dataset from which data will be taken to your message.
    • The column from which you need to bring a value.

    Click the Insert button.

    In the template, the macro will look like this:
    New macro.

  5. Now, we will create a couple of new macros to take data from the same dataset, but we will select other columns. The first one is for color....
    The second macro.

    ...and the second is for size:
    The third macro.

    The template, thus, will look like this:
    The template is ready.

  6. Save the template and insert it into the message. In the What to enter window (that will have a title that you indicated as Window title), select the value you need from the key column and click OK:
    Select a value from the key column.

    Note. On this step, you can select a value only from the key column, and the app will take corresponding data from the row with this value in the column that you indicated as Target column.

    If there are some rows in your dataset marked as default ones, the key value from the first default row will be preset in the What to enter window.

  7. With all the values pasted from the selected key column and indicated rows, the message will look like this:

    Values from dataset in your message.

DatasetValue: Add fixed values

  1. The ~%DatasetValue macro inserts any value you need from a certain dataset. Pick the macro from the list:
    Select ~%DatasetValue macro.

  2. Select correct items from the drop-down menus that you will see in the dialog window:
    ~%DatasetValue macro.

    • Dataset
      The dataset that the add-in will pull the value from.
    • Target column
      The column from which the value is to be extracted.
    • Key value (value from key column)
      Defines the row from which the value will be taken.
  3. You can add several DatasetValue macros to the template, and they will look like this:
    ~%DatasetValue macro.
  4. When you insert such a template into the message body, the add-in does not ask to select anything and simply pulls the values you indicated. The result message is going to be like this:
    ~%DatasetValue macros in template.

Bind a table to dataset

You can use datasets for filling in tables in your templates. If you bind a table to a certain dataset, the add-in will pull the rows from the dataset based on the key values you choose and insert them into your table when you paste the template into a message.

Tip. To quickly add a table to a template, click the corresponding button on the Shared Email Templates toolbar in the Edit mode:
Insert a table into a template.
You can learn more about tables in our How to create and format tables blog post.
  1. Put a cursor in any cell of the row that you want to complete with values from a dataset, right-click and select the Bind to dataset option:
    Bind a table to a dataset.

    Note. Your sample table may contain just one row. After you bind a table to the dataset, you will be able to choose as many rows to insert as you need when pasting the template into a message.
  2. Enter the title for the window, which will appear at the moment of inserting the template, and choose the necessary dataset from the drop-down list. Click OK:
    Enter the title and pick a dataset.
  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 dataset of your choice:
     Fill empty cells with the names of columns.
    The ~%[ ] parameters with the column names populate the cells in the same order the columns are presented in the dataset. If the number of columns is different, for example, your table has 6 columns while the selected dataset contains just 5, the sixth column of your table will not be filled.

    In case of mismatching order, you can edit the ~%[ ] parameter by typing in the correct column name in the square brackets or copy and paste it into the necessary cell.

    Note. If there is some text in cells of the selected row, the ~%[ ] parameter will not be inserted automatically into these cells. You can add the ~%[ ] parameter with a column name manually.
  4. Save the template with the bound table.
  5. When you paste this template into a message, you will see the dialog window with the title you have specified on step 2 and the list of values from the key column of the selected dataset:
    Choose the key values.
    Check the necessary key value(s) and click OK.

    If you have set default rows in your dataset, the key values from the corresponding rows 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 at the top of the dialog window.

    If you want to add all rows from the dataset to your table, check the topmost box to select all the key values:
     Check all the key values.

  6. The table filled with the data from the selected row(s) of the dataset will appear in your message:
     The table is filled with rows from the dataset.

Unbind a dataset

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

Visit our blog to discover more ways to use datasets in your daily routine: