<aside> 💡 How to filter and split a datatable into a list of datatables
</aside>
Say you have the following data stored in an Excel file:
| Certificate | Employee | |
|---|---|---|
| Medical | John Jones | mailto:[email protected] |
| Health & Safety | John Jones | mailto:[email protected] |
| Driving | John Jones | mailto:[email protected] |
| Swimming | Mike Smith | mailto:[email protected] |
| Health & Safety | Mike Smith | mailto:[email protected] |
| Working at heights | Kevin Peterson | mailto:[email protected] |
| Cooking | Kevin Peterson | mailto:[email protected] |
| Driving | Kevin Peterson | mailto:[email protected] |
| Medical | Kevin Peterson | mailto:[email protected] |
You would like to filter this table based on the column Email. For each unique Email found, you would like to save the filtered rows in a separate datatable. Each of the resulting datatables will be converted to a HTML table to be included in the email to be sent.
The following solution will take an input Excel file, i.e. %ExcelFile%, and return a list of datatables, i.e. %ListOfDatatables%.
Each item in %ListOfDatatables% is derived by filtering the original datatable read from the Excel file. In this example, the datatable is filtered based on the column Email. The number of items in %ListOfDatatables% corresponds to the number of unique emails found in the original datatable.
Note that you will need to enter the following SQL query in the action Execute SQL statement:
Select * from [Sheet1$] where [Email] = '%CurrentEmail%';
In the second For each loop, a subflow ConvertDatatableToHTMLTableAndSendEmail is called to convert the datatable into a HTML table which can then be included in the email body.