Webinar 4: Use simple datasets and bind Excel tables

Introduction

Hello! Irina Goroshko from Ablebits is here and on this webinar I will show how to use the datasets feature of the Shared Email Templates add-in for Outlook. If after watching the video, you have any questions or feedback, feel free to share them in the comments. The links to the previous webinars please find down below.

On this webinar, I will show how to:

• Create a simple dataset in the add-in
• Create and make use of the default rows
• Add values from a dataset to the template text
• Bind a table in a template to a dataset
• Connect a dataset to an Excel table

So, let’s go!

What is dataset and two types of datasets

What is a dataset and why may it be useful for you?

Dataset is a table with data that you can use in a template. There are two ways to use a dataset: you can retrieve a value from it and paste it into your template text or you may create a table in your template and bind it to a dataset so the table will be filled in with values from dataset rows you select.

Yes, I know, for now this may sound complicated, but believe me, there is nothing difficult in datasets as soon as you figure it out.

There are two types of datasets. Simple means that you create or import it to Shared Email Templates and edit the dataset in the add-in. Connected Excel table is a table from an Excel workbook that is located in OneDrive or SharePoint and linked to Shared Email Templates. I will show how to use both types of datasets.

So, now we know what a dataset is, what it can be useful for—finally, it’s time to create one!

Create and use simple dataset

I right-click My Templates and select New Dataset. Datasets are created and edited in the web app only, so the add-in brings me to the browser. Here, we are to select the dataset type. This time, it will be Simple.

In a dataset, we can add up to 32 rows and 32 columns. The first column is the key column, let’s slow down here a little bit.

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. Further, it will be more clear, I promise.

You can rename the first column by clicking on this box, entering the text you need and clicking Enter. To add rows and columns, we click the Plus signs. Now, I can add the values to the dataset.

What do you think the mysterious Default Row in the name of a column could mean? We developed this feature to let you select the rows that will be checked by default when you use a particular dataset. For instance, in most cases I need the Program A and Program B data in my emails so I enter an asterisk symbol to this and this cells. You can add any other character to the default row cells, what is important here is that they are not empty. I’ll show you later how it works.

Oops, I almost forgot to name my dataset! Let it be Discount Programs.

The dataset is ready, and I click Save.

Now our task is to use some values from this dataset in a template. I open an existing template. In two places, here and here, I need to add values from my dataset. I place the cursor where I need to add the value, select the ~%WhatToEnter macro in the list, choose Dataset in the first box, add the window title to the second box, it will be—discount, select my dataset and —attention!—select the target column, the column from which I need to pull the value. This is the Discount column. And click Insert.

The same way, I add the ~%WhatToEnter macro to the second place of my template where I need to insert the expiry date. The main difference here is the target column, it is Expiry date in this case.

I save the template and click Paste. The add-in is asking me to select a value from the key column to identify the row from which I need to pull the data.

Here is my message, the values are pasted from the row I chose.

Bind table to dataset

Our next step is to add a table to a template and bind it to a dataset. 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.

Let’s have a look. I get back to my template. Now, my task is to add a dynamic table to a template so each time I use it I can pick the data that will be automatically inserted into the table in the email message.

I click the Table icon on the toolbar and select a table with two rows and three columns. I can select a color for the borders in Table Properties -> Advanced. For the first row, I add the headers: Program name, Discount, Expiry date.

To bind a table to a dataset, I right-click on a cell in the row that will be filled with values from the dataset and select Bind to Dataset. By the way, as you can see, I added only one additional row for the values from dataset, but the tool will add more rows if I select them. Here, I enter the title and select my dataset.

As soon as the table is bound, you will see the names of the dataset columns added. Let’s save the template and see how it works now.

I insert the template into my message. The first window has the title I entered when I bound the table, and here I am to select the rows that I want to add. Remember, we created a default row column and marked these two rows? Here they are, checked by default. I check one more row and click OK.

The second window is about the Discount in the message text, I select the row with the values to pull and click OK. Here is our message. The add-in has retrieved the necessary values from the dataset and added them to the table. In another email, I can use the same template and indicate different rows, so the table will be different.

Connect Excel table

So, at last, we have approached the final part of our lesson today: how to use an Excel table as a dataset.

In my OneDrive, I create an Excel book, copy the data from my dataset in Shared Email Templates (for this I select it and press Ctrl + C) and paste the data to the worksheet.
I select the range and press Ctrl + T to make it a table. I name my table.

Now, I can delete the dataset that I created in Shared Email Templates. What is important here is to remember its name—Discount Programs—we’ll need it in the future.

So, I go back to the Shared Email Templates web app and start creating the new dataset: I right click My Templates and select the option. This time I click the Excel table from OneDrive button. I find the book I need and click Open.

The app shows me the list of all tables the book contains (for now there is only one but could be more), I pick the table. Here, I can open my workbook or select another one. I give my dataset a name and click Save.

In the list, the connected Excel dataset is marked with the small Excel icon. We go back to our template, insert in into the message again, and you can see that it is working despite the fact that we removed the initial dataset. Now it pulls the values from the dataset with the same name that is a linked Excel table.

Wrap-up

That’s it for this webinar. We learned what is a dataset, two types of datasets, what is a key column and a default row and—the most important!—how to create and use datasets in your templates. If you liked this video, hit the thumbs up! Thank you all once again and see you very soon on the next webinar.