Hello,
If you read this, hope you are having a great. I'm trying to solve a notification problem where I email people with only details specific to them from a data table. Below is a screenshot of my flow
and the result of my first SQL query is below
I have set up my send email notification this way
Currently, the recipients get the entire results of my table (QueryResult2%). What I want to achieve is for user with the email address first email address in the table to receive only the first two rows and Dinesh Kumar to receive only the last row.
Help is needed and appreciated.
Best Regards,
Simon
cc @tkuehara @eliotcole please would appreciate your review. I've seen you have responded to issues similar to mine
DataTables are represented with the syntax %DataTable[RowIndex][ColumnIndex]%.
So, you have a bit of work yet to get what you want. First, lets get the body of what you to send into a table variable or a text variable.
Set Variable %LoopMax% to %QueryResult2.RowsCount% #we know how many rows the table holds
Create New List %EmailBody% #we will add to this list per email and then clear it before the next email
For Each %CurrentItem% in New Input #you already have this command
Loop starting at 0 to %LoopMax% increment of 1 #we are going to loop through each row of QueryResult2
If %QueryResult2[LoopIndex][6]% = %CurrentItem%
Add %QueryResult2[LoopIndex]% to List %EmailBody% #add the row to the email body list
#this will loop through all rows adding each row to %EmailBody% if the email matches
End Loop
Send Email #update the email with %EmailBody% instead of %QueryResult2%
Clear List %EmailBody% #need to clear the list so it starts over for the next "For Each"
End For Each
The logic works, the only thing I am not sure on is if you can add the entire row %QueryResult2[LoopIndex]% to a list item. It may be that you have to create a datatable instead of a list, and then write each [row][column] to the corresponding row column of your new datatable, then you could post the whole new datatable to the email. The logic would be the same, the execution would be more difficult.
Best of luck!
If the trigger is:
I'll try to mock something up for you later, but play with that in your head, and without getting too frustrated with the danged thing, see where you get to. You'll be surprised what you figure out.
Either way you will want SQL Item values in that email, rather than written text, where possible.
Hello @MichaelAnnis
Thank you for responding.
I'm trying the suggestion but I keep getting this error which subsequently hangs my Power Automate
Best Regards,
Simon
Hi @SimonSeez
Well, I did a code but you'll need to adapt it (I used an Excel as a data source and Outlook to send emails). Also, it needs a review (might be inefficient... but it works):
Looping each Excel row and checking the current index: the first time you can't send email, and the last row you must send email
For the other cases in between, check if the current email is the same as the previous one; if so, then it belongs to the same "datatable subgroup"
In the last run, if the emails are different (previous != current), send both the previous message and the current one; if not the last iteration but emails are the different, you just need to send the previous one
Now finally if the last two records are the same, it means you just need to send the current one
The previous value becomes the current one at the end of each loop
It sent to each one a separate email according to the Excel data table:
Data table with values from Excel file
Please also notice I had to send the emails with a HMTL body:
Here's some more details about the data table:
Good luck.
My apologies...try [5]. I don't believe the number column in your %QueryResult2% counts, and the first column "Customer Name" is actually column [0], so the "Email" column would be [5].
Best of luck!