cancel
Showing results forย 
Search instead forย 
Did you mean:ย 

Format SQL Results into Email

dunncrew
Deputy Chef III
Deputy Chef III

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 ?

2 ACCEPTED SOLUTIONS

gary1
Executive Chef III
Executive Chef III

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.

View solution in original post

gary1
Executive Chef III
Executive Chef III

For step 8, replace "Step 5 Output" with Step 5 Message. IMO it's very rare that you will use the "Step # Output" directly. You generally want to use the data pills within the step's output.

Anyway, switching gears: let's try something different.

First, make a variable list:

gary1_3-1696971373563.png

By doing this, you now have the ability to parse and transform (clean up) the values as they go into the list:

gary1_6-1696971894181.png

Using to_time makes the date string a date object and then using strftime converts it back to a string using the specified formatting. Using strip on the output removes leading/trailing whitespace characters.

Now that it's cleaned up, you can pluck/to_csv and it'll be a lot closer to what you want in your email, but I still recommend using a message template! It's only a few extra steps from here.

gary1_7-1696972119233.png

gary1_5-1696971790068.png

 

 

 

 

View solution in original post

15 REPLIES 15

mppowe
Executive Chef I
Executive Chef I

I believe replacing \t with \n will get you a newline.

dunncrew
Deputy Chef III
Deputy Chef III

I added "Log message to Job report and Workato Logs" with this syntax:

[Step 3 output].pluck('datetimeoftxn_b5896cc8','listoutput_0ec6e5e5').to_csv

I get this error: Error calculating input for field 'message': no implicit conversion of String into Integer

My data from the SQL query in step 3  is a date field and a string. no integers involved.

gary1
Executive Chef III
Executive Chef III

That error generally means you're trying to access an index or range of something that isn't an array. 

What happens when you remove .to_csv? The output of the logger/message should be a 2D array.

gary1
Executive Chef III
Executive Chef III

Also, your example says: [Step 3 output]

It may make a difference if you use the "rows" array data pill instead of the full step 3 output. They're likely structured differently.

Post some screenshots if you can.

dunncrew
Deputy Chef III
Deputy Chef III

Screen shots posted below. Thanks for your help.