In our week 8 client project with LendLease we had the task of creating an automatic emailing system in Alteryx to email a series of tables to several different people. This might sound like an easy task, but it had the extra condition that it could not use the inbuilt email tool, as it did not work on their network. Instead we used a third-party API (Mailgun). I will not go into too much about it if you want to learn more they have lots of documentation on their website. The main thing to take from the API is that the body of the email can be html. Looking into how others have integrated the Mailgun API into Alteryx, I found that most would right out a file using the render tool and then bring it back in. I didn’t like this solution, so I wanted to try something a bit different.

 

HTML Tables

I need to get my data into standard html format (see above) where each cell of data is enclosed by “<td></td>” tags,  field headings by <th></th>, rows by <tr></tr> and the entire table within <table></table>The Macro I created to do this takes a table that has been transposed so that it is a long, this is so any number of fields can be passed into the macro. The important thing to know is that order the fields and rows are in when they enter the macro is the order they will be in the final table.

Creating the Table

The first thing it does it makes sure the data fields have a much larger allocation of memory, because eventually it will have an entire table in a single cell. It then does a bit of cleaning just to remove any excess white space. The first container one adds <td> </td> tags to every cell of data. Container two sets the order of the fields for later use, this is needed because whenever  a summarize tool is used it reorders everything. Container three is where the table structure starts to get built. The data is sorted to make sure that they are in the correct order. Then a summarize tool pushes all the cells for a single row together using the concatenate function. It reorders it and the row tags are added to each one. The fourth container does a similar thing but for the header of the fields. The headers and the rows are unioned together, concatenated again and the table tags added to create a proper HTML table.

A basic HTML table does not look very nice.  So a template is input through the secondary input of the macro. In the template there needs to be this string ‘|XXXXXXXXX|’. This is where the table will be inserted into.

Setting up the Macro

The Macro has five required inputs.

1. 1. RowID – Unique identify for every row of data.
   2. KEY – A Table will be created for each unique KEY in your data set.
   3. Field Names – Field names which will become the headers of the final table.

1. Data – Contains all the data
2. Template – HTML template, must be in a single cell (one note here when you bring in a html file it will put each line in a cell. It needs to all be in one cell. I concatenated it all using summarize tool)

 

Wrapping up

If you would like to use the macro you can find it on the Alteryx public gallery here. This is my first attempt at publishing a macro so any feedback would be great.