Format SQL Results into Email

[dunncrew]

Hi, In my recipe, I am selecting 2 columns from a SQL table, and want to put the results in the body of an email.  I get results, but they look like this:

{"rows"=>[{"datetimeoftxn_b5896cc8"=>"2023-10-09 13:59:23.797000000", "listoutput_0ec6e5e5"=>"Test_Last_Name1, Test_First_Name1, Test_Door_Name1                                                  "},

{"datetimeoftxn_b5896cc8"=>"2023-10-09 13:59:23.797000000", "listoutput_0ec6e5e5"=>"Test_Last_Name2, Test_First_Name2, Test_Door_Name2                                                        "}]}                                         

I just want the data in my email like this:

2023-10-09 13:59       Test_Last_Name1, Test_First_Name1, Test_Door_Name1  

2023-10-09 13:59       Test_Last_Name2, Test_First_Name2, Test_Door_Name2

The 2 SQL Columns are datetime and nchar. What's the best way to get just the data in my email ?

[gary1 · Accepted solution]

I'd recommend using a message template to format the data into a nicely formatted HTML table, and then insert the table into the email body.  You'll need to brush on HTML for the formatting and mustache for the parsing in the message template, but it's definitely worth the effort -- especially if you may need to create more emails in the future.

[gary1 · Accepted solution]

I'd recommend using a message template to format the data into a nicely formatted HTML table, and then insert the table into the email body.  You'll need to brush on HTML for the formatting and mustache for the parsing in the message template, but it's definitely worth the effort -- especially if you may need to create more emails in the future.

[dunncrew]

Thanks. I have about 1 hour total experience in Workato and HTML, so not familiar with message templates. I will take a look. I hoped I could do something in the Outlook email step to specify just the data.

[gary1]

Got it. Learning how to use it will have long term benefits, but here's a quick fix for now.

Make a logger with this formula:

[array_output].pluck('datetimeoftxn_b5896cc8','listoutput_0ec6e5e5').to_csv

to_csv isn't the ideal method to use, but it's the simplest way to get close to what you want:

2023-10-09 13:59:23.797000000,Test_Last_Name1,Test_First_Name1,Test_Door_Name1  

If this is close enough, then you're done. If you want to break it up a bit, you can add this to the formula:

.gsub(',','\t')

This will substitute the commas with tabs to give the text a little more breathing room.

You can insert this logger (or the formula) directly into the body of the email.

If you want to get rid of the timestamp in the date, I would suggest reformatting it on the SQL output.

Otherwise, there are longer ways to do this by creating a list variable and reformatting the SQL data as you add it to the list. Then you can convert the list into readable text for your email body.

[dunncrew]

is .gsub(',','\t') adding a TAB ?  Is there a way to add a line break to force each record to a new line ?