cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SimonSeez
Frequent Visitor

Send Email based on a columns in a data table

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 

SimonSeez_2-1630430153277.png

 

and the result of my first SQL query is below 

SimonSeez_1-1630430128911.png

I have set up my send email notification this way 

SimonSeez_3-1630430250221.png

 

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 

5 REPLIES 5
MichaelAnnis
Super User
Super User

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!

 

 

 

 

 

 

 

 

eliotcole
Super User
Super User

If the trigger is:

  1. A new SQL Db item - Then in the To field of the email action place the RM Email value from the new item. Then pick the other values accordingly for the rest of the email.
  2. Scheduled Check - Get SQL items, union() the entire RM Email column as an array, twice, then Apply to each result a filter on the Get SQL items with an email and an HTML table or whatever data you want to include.

 

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 

 

SimonSeez_0-1630442184426.png

 

Best Regards,

Simon

tkuehara
Super User
Super User

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 emailLooping 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"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 oneIn 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 oneNow finally if the last two records are the same, it means you just need to send the current oneNow finally if the last two records are the same, it means you just need to send the current oneThe previous value becomes the current one at the end of each loopThe 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 fileData table with values from Excel file

tkuehara_6-1630441706195.png

tkuehara_1-1630442140402.png

 

tkuehara_0-1630442049785.png

Please also notice I had to send the emails with a HMTL body:

tkuehara_2-1630442345532.png

Here's some more details about the data table:

tkuehara_3-1630442413525.png

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!

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

New Ideas Forum MPA.jpg

A new place to submit your Ideas for Power Automate

Announcing a new way to share your feedback with the Power Automate Team.

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

MPA Licensing.jpg

Ask your licensing questions at the Power Automate AMA!

Join Priya Kodukula and the licensing team, super users and MVPs to find answers to your questions on Power Automate licensing.

Top Solution Authors
Top Kudoed Authors
Users online (1,604)