Unanswered

How can I display a list of rows in an email body from SQL without using Apply To Each?

(0) Share

Report

Posted on by benshikari

I have a Power Automate flow, that uses Execute a Stored Procedure (v2) to get a list of Rows from the database based on some logic that occurs server-side.

The list contains one column of 5 rows, that I need to place into the body of an e-mail, but when I select the ResultSet (column name), it automatically defaults to using Apply to Each, which means it takes the 5 values and sends 5 emails to the user for each row with one unique value in each e-mail.

Basically, I want the e-mail to send the row as this:

Hi, here are your 5 references:

123456

234567

345678

456789

567890

How can I achieve this?

Thanks

Categories:

Building flows

I have the same question (0)

All responses (5)

Answers (0)

grantjenkins 11,063 Moderator on at

Like (0)

Report

You can look at using Create HTML table and pass in your ResultSet. Or use a Join and join with a <br> tag since the email body will output as HTML.

Was this reply helpful? Yes No
benshikari 10 on at

Like (0)

Report

I had looked at that, but I really need the values as text. Is there a way to join the text results together as a string using <br> like you say?

Was this reply helpful? Yes No
grantjenkins 11,063 Moderator on at

Like (0)

Report

join(YOUR_RESULTSET_FIELD, ‘<br>’)

Was this reply helpful? Yes No
benshikari 10 on at

Like (0)

Report

Thanks for your help with this.

Unfortunately, I can't select the resultset field as a dynamic content item within the formula. If I manually type it in, it returns as blank when used in the fx formula.

The other result sets return a string like:
{"case_ref":30000590},{"case_ref":30003393},{"case_ref":30007703},{"case_ref":30013185}
Is it possible I could extract these numbers manually within the string?

Was this reply helpful? Yes No
grantjenkins 11,063 Moderator on at

Like (0)

Report
Are you able to show your JSON to understand exactly what's within the ResultSet object? Does it look like the following:
{ "ResultSet": [ { "case_ref": 30000590 }, { "case_ref": 30003393 }, { "case_ref": 30007703 }, { "case_ref": 30013185 } ] }

If so, you can use a Select then Join. The expressions in my Select are below. You would likely be able to just select your ResultSet from dynamic properties - in this example I'm getting the data from my Compose.
//From - this would come from your data source outputs('Compose')?['ResultSet'] //Map item()?['case_ref']

And the output from Join.

Was this reply helpful? Yes No