Using CSV data in email step

InsightConnect
1

Hi, just about done with my first Workflow. I query some interesting data from a database using the SQL plugin, iterate through it and pull in metadata for each element using another database and another plugin (haveibeenpwned).

The final step in my loop is using the CSV plugin to combine the data into one CSV so the results can be emailed to my team. The loop has an array-type output variable that holds the CSV for each iteration [“make_member_list”].[list]

After the loop, I have a step to email with the “Microsoft Office 365 Email” plugin. The body of the email contains a reference to the CSV like this:
{{[“make_member_list”].[list]}}

The data is correct, but the open and close square brackets around the CSV are present, and there is no line break between CSV elements. Is there a way to iterate through the [“make_member_list”].[list] array within the email step and put each element on its own line? Or some other way to prepare the data for the email step?

If that’s not clear…

Current output:
[“name1,email1,hibp1,date1”,“name2,email2,hibp2,date2”,…,“nameX,emailX,hibpX,dateX”]

Desired output:
name1,email1,hibp1,date1
name2,email2,hibp2,date2
…
nameX,emailX,hibpX,dateX

Thanks!

2

Take a look at the #each function in handlebars (Built-in Helpers | Handlebars)
Also, Elijah wrote a good article on handlebar magic around formatting array outputs (InsightConnect and the Oxford Comma Problem)

3

Exactly as @brandon_mcclure says, so something like

{{#each [“make_member_list”].[list]}}{{this}}
{{/each}}

should do it!

4

you can adjust spacing between lines with additional carriage returns (literally, the enter key) inside the #each block

5

Thanks for the guidance. That looks exactly like what I want to do. I am pretty inexperienced with javascript and haven’t done handlebars before. I read through the links, and played with some examples on handlebarsjs.com in their testing console.

I’m trying out the snippet Elijah posted, and not getting the output unfortunately. I’m wondering if my variable isn’t the right data type.

At most, I was able to do this:

{{#each [“make_member_list”].[list]}}
{{this}}
{{else}} No findings
{{/each}}

And my email did have the “No findings” string in it. Double checking my earlier workflow steps, I can see that the earlier loop step has an output defined as {{[“make_member_list”].[list]}} and it is type array-string. I confirmed this by using the “+” button within the Email step and that variable shows up exactly with that name and the type is again array-string. So I’d expect each {{this}} to be the string and show up correctly. But maybe it needs to be an object instead of an array?

Am I not forming the CSV correctly earlier in the Workflow maybe? I played around with a few more attempts at removing square brackets, and adding double quotes, more curly brackets, etc. but none helped. The best I can get is the {{else}} clause to execute inside the each code.

Any suggestions?

6

I fell victim to one of the classic blunders. I should have noticed this quicker but the double quotes copied from Elijah’s reply were the “wrong” double quotes. It worked when I typed it out, but not when I copied/pasted. It’s strange because Elijah’s post uses the code formatting block.

Copied from post:
https://unicode.scarfboy.com/?s=”

Needed in InsightConnect editor:
https://unicode.scarfboy.com/?s="

Well it’s working now. Thanks again for the help!

2 Likes
7

Ahh, the quote issue (I say as I was just trying to figure out what was wrong with a query I copy-pasted and it turned out to be the same problem) :upside_down_face: But glad you got it working! Let us know if you encounter any other issues along the way, though it sounds like you’ve got your workflow up and running now. :+1: