cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
FoDelement
Level 8

Collect data from a spreadsheet, conditionally. (vlookup?)

Hello,

 

I would like to know if it is possible to do the following. I have a small table that has a column called "Contact" I want a flow that will gather all the columns where the "Contact" column is the same, and send them in an HTML table as an email.

 

1.PNG

 

I have the data in an HTML table, but I can't seem to figure out how to "loop through" all the entries that have the same "Contact" field and then send an email.2.PNG

The closest I got was having a For Each loop running that would append the data to an array, but if the send email action was after the loop (So all the data was in the array), I could not select the HTML table as the HTML table was apart of the apply to each loop. This would also result in one email going out with all the data in it.

 

I would love to know what ideas you have,

 

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Collect data from a spreadsheet, conditionally. (vlookup?)

Hi @FoDelement ,

 

Do you want to group by email, send an email to the corresponding email address, which only contains the rows with the same Contact column value?

If so, please refer to the following method.

 

  • Initialize two array variables to store the value of the Contact field and the source of the HTML table.
  • Iterate over the output of List rows present in a table and append Contact column value to the variable.
  • Use union() function to remove duplicates from the Email variable and then iterate through the result.
  • Traverse the output of List rows present in a table again, get the rows that Contact is the same as the current email, and append to the HtmlTable variable.
  • Finally configure the HTML table action and Send an email action. Note that Is html field needs to select Yes.

 

Image reference:

19.PNG

20.PNG21.PNG

Please take a try.

 

Best Regards,

Community Support Team _ Barry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
Community Support Team
Community Support Team

Re: Collect data from a spreadsheet, conditionally. (vlookup?)

Hi @FoDelement ,

 

Do you want to group by email, send an email to the corresponding email address, which only contains the rows with the same Contact column value?

If so, please refer to the following method.

 

  • Initialize two array variables to store the value of the Contact field and the source of the HTML table.
  • Iterate over the output of List rows present in a table and append Contact column value to the variable.
  • Use union() function to remove duplicates from the Email variable and then iterate through the result.
  • Traverse the output of List rows present in a table again, get the rows that Contact is the same as the current email, and append to the HtmlTable variable.
  • Finally configure the HTML table action and Send an email action. Note that Is html field needs to select Yes.

 

Image reference:

19.PNG

20.PNG21.PNG

Please take a try.

 

Best Regards,

Community Support Team _ Barry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

FoDelement
Level 8

Re: Collect data from a spreadsheet, conditionally. (vlookup?)

Wow!

 

Thank you @v-bacao-msft  for such a detailed answer, I really do appreciate it.

I followed your instructions and tested it. The first email goes out correctly, however, the second email contains the last items from the email before it. Is there a way to clear the array before sending the next email to prevent this?

 

 

EDIT: Answered my own question. I just set the HTML array variable to nul after the email action. This works perfect! Thank's again!

 

This is the email to bob@chickenco.com

1.PNG

This is the email to kim@fishking.com

2.PNG



 

Community Support Team
Community Support Team

Re: Collect data from a spreadsheet, conditionally. (vlookup?)

Hi @FoDelement ,

 

Sorry, my mistakes have forgotten a very important step.

The array needs to be emptied under Send an email action.

 

Please refer to the modified configuration:

24.PNG

 

Please take a try again.

 

Best Regards,

Community Support Team _ Barry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
FoDelement
Level 8

Re: Collect data from a spreadsheet, conditionally. (vlookup?)

Yep!

Figured that out haha! It's working now. Guess I need to learn more about the union function...

 

Thanks again! 

 

🙂

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

firstImage

Incoming: New and improved badges!

We've given our badges an overhaul and also added some brand new ones!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Top Kudoed Authors (Last 30 Days)
Users online (4,872)