Formatting an HTML Table for Email in SPARK

 

VIDEO TRANSCRIPT
SPARK Training: Formatting an HTML Table for Email

Hi, I'm Jason Sexton and I've been working for TekDog with SharePoint and Business Process Automation for a few years now. Today I'm going to show you how to construct an HTML table in a SPARK workflow.

SPARK is a Business Process Automation tool we use with SharePoint. A Business Process Automation tool lets you design a workplace routine so that everyday tasks are done for you automatically.

An HTML table is useful if you want to quickly communicate a list of columns and values in a report or email. Since SharePoint data is already presented as columns and values, an HTML table makes an obvious fit for constructing a simple report.

I'll use this list of Training Requests. Let's say I need to generate a weekly report of requests to send by email. I want to display the training requests in a table because that will make it easiest to read the title, course type, dates, and so on.

Each SharePoint column will be a column in the table, and each row will be populated with values for one list item.

We will use a variable and a String Builder activity to construct the HTML table, but first a little about HTML syntax.

An HTML table has <table> opening and closing elements like this: <table></table>. A closing element in HTML always matches the opening element except is has another slash like this: </table>.

 

Within the <table> element is a header row that has opening and closing <tr> elements. The title of each column is surrounded by a <th></th> element within the <tr></tr> element like this:

<table>

<tr>

<th>Title</th>

<th>Course Type</th>

<th>Start Date</th>

<th>End Date</th>

<th>Amount</th>

</tr>

</table>

 

The entire header can be put on one line to make it more readable, especially since it won't change. Every report will have this same header.

<table>

<tr><th>Title</th><th>Course Type</th><th>Start Date</th><th>End Date</th><th>Amount</th></tr>

</table>

 

Each list item will needs to be inserted in the table, underneath the header, inside a row element, <tr>:

<table>

<tr><th>Title</th><th>Course Type</th><th>Start Date</th><th>End Date</th><th>Amount</th></tr>

<tr>

<td>[TITLE GOES HERE]</td>

<td>[COURSE TYPE GOES HERE]</td>

<td>[START DATE GOES HERE]</td>

<td>[END DATE GOES HERE]</td>

<td>[AMOUNT GOES HERE]</td>

</tr>

</table>

 

I will use the SPARK Item tab to insert the actual values for each list item, and each subsequent list item follows in the same way inside the <table> element. Note that the use of spaces or tabs doesn't matter in HTML; I only use it to make it more readable.

<table>

<tr><th>Title</th><th>Course Type</th><th>Start Date</th><th>End Date</th><th>Amount</th></tr>

<tr>

<td>[TITLE GOES HERE]</td>

<td>[COURSE TYPE GOES HERE]</td>

<td>[START DATE GOES HERE]</td>

<td>[END DATE GOES HERE]</td>

<td>[AMOUNT GOES HERE]</td>

</tr>

<tr>

<td>[TITLE GOES HERE]</td>

<td>[COURSE TYPE GOES HERE]</td>

<td>[START DATE GOES HERE]</td>

<td>[END DATE GOES HERE]</td>

<td>[AMOUNT GOES HERE]</td>

</tr>

<tr>

<td>[TITLE GOES HERE]</td>

<td>[COURSE TYPE GOES HERE]</td>

<td>[START DATE GOES HERE]</td>

<td>[END DATE GOES HERE]</td>

<td>[AMOUNT GOES HERE]</td>

</tr>

...

</table>

 

Now I'll set this up inside SPARK. This will be a Site Workflow so that I can run it on a weekly schedule. I'll create a string variable called varHTMLTable to hold the HTML as I put it together piece by piece. It needs to be a Plain Text type to hold the HTML.

The Set Workflow Variable activity can be used to initialize the variable with the opening <table> element and the header, since it will always be the same for each report, regardless of the actual training requests.

Now we need to get the actual values from the TrainingRequest list. I'll use the CAML Query. In settings I select the TrainingRequests list and the output will just be a collection of SharePoint IDs for each request. I could put a filter in to select only certain dates or requests, but for now I just want a complete list.

 

The CAML Query output is a collection, and I'll need a variable to store that collection: varTrainingRequestsIDCollection.

Now I need to loop through each ID and query again to get the specific values for each list item. I'll each the For Each activity on the varTrainingRequestIDsCollection, and store each value in variables as follows.

 

varCurrentTitle, varCurrentCourseType, varCurrentStartDate, varCurrentEndDate, varAmount.

 

Now I can construct the HTML row for this item in the table. I'll use the String Builder activity to append the new row to the existing variable that already has the <table> opening and header. I'll have to fill in each of the values from the SPARK Item tab.

<tr>

<td>$WFvariables(varCurrentTitle)</td>

<td>$WFvariables(varCurrentCourseType)</td>

<td>$func-FormatDate($WFvariables(varCurrentStartDate),"MM-dd-yyyy")</td>

<td>$func-FormatDate($WFvariables(varCurrentEndDate),"MM-dd-yyyy")</td>

<td>$WFvariables(varCurrentAmount)</td>

</tr>

 

Finally, outside of the loop, I have to wrap up the table by adding the closing </table> element. I'll use String Builder again.

Now I can use the Notification activity to construct a nice report. I can put the table in the body of the email by using the varHTMLTable variable I'll constructed.

I'll use a Log activity to log the varHTMLTable so I can see it, at least in part. And that will also help with troubleshooting if something isn't working right.

Save, Publish. Now I'll run it. No errors. It works! I don't have email set up on this server, so I'll have to show you a mock-up what it will look like.

That's the conclusion of my session on constructing an HTML table in a SPARK workflow. If you have any questions or would like more help from TekDog contact Kerry Kicos at TekDog with the information on the screen.

Check out our free SPARK Forms and Workflow course >>