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

Read excel table, aggregate all appointments for an office and email an HTML table to that office.

I have the following table of offices. I need to email each office with the appointments for their office only:

Samizdat_0-1597943476831.png


I have a flow like this which successfully emails each office, but they get:
a) A table of the entire spreadsheet instead of just the ones for their office
b) A separate email for each row 

 

Samizdat_1-1597943638950.png

 

How do I have it aggregate all rows for office 1 and email it to office 1 without sending an email for each row and also without including rows from all other offices? 

 

End result should be 1 email per office with a table of all excel rows which pertain to their office. 

1 ACCEPTED SOLUTION

Accepted Solutions
Jronash
Solution Supplier
Solution Supplier

Yes, you are correct that you would create a lookup table for the offices.

 

To loop through them, you use an 'Apply to each' block.  This block allows you to put other blocks inside of it.  Here's what it would look like (without the final block to send the email).

table.png

 

You want the Apply to each block to reference your lookup table of offices, so it only processes each office once.

 

In the Filter array block, you set the From field to your table of appointments, because you want to filter every appointment.  In the criteria, you tell it that you want it to return any row where the Office # value (in the appointments table) is equal to the current Office # value from the office lookup table.

 

Then all you have to do is create your HTML table from the results of the filter block.

 

When you look at the run history, you'll be able to go through and see what each loop iteration looks like.  Here are the first two iterations from my test flow:

 

Iteration #1:

result1.png

 

Iteration #2:

result2.png

 

If this is still confusing, search youtube for videos related to the Apply to each block and the Filter array block.  Once you have a sense of how both of those work, the rest of this should fall into place.

View solution in original post

7 REPLIES 7
Jronash
Solution Supplier
Solution Supplier

As far as I know, Automate does not have a Group By function, so that will make things more difficult for you.

 

If it were me, I'd create a separate table for your offices.  Each office would have one row that contained the Office # and the email address.

 

Then you can loop through the offices one at a time, and use filter array on your appointment table to filter out only the rows that match that Office #.

 

Take the results of your Filter Array action and use it to create your HTML Table.  Then email the HTML table to the email address for that office.

Samizdat
Frequent Visitor

I'm sort of new to power automate so your instructions aren't actionable on my end unfortunately. 

Are you saying make a second lookup table that just lists the offices and email addresses?
Once I've done that, how do I "loop through them one at a time and use filter array on your appointment table to filter out only rows that match Office #

Conceptually I understand the premise but am not sure how it would actually be implemented in Power Automate or what that would look like. 

Any additional help would be super appreciated. 

Jronash
Solution Supplier
Solution Supplier

Yes, you are correct that you would create a lookup table for the offices.

 

To loop through them, you use an 'Apply to each' block.  This block allows you to put other blocks inside of it.  Here's what it would look like (without the final block to send the email).

table.png

 

You want the Apply to each block to reference your lookup table of offices, so it only processes each office once.

 

In the Filter array block, you set the From field to your table of appointments, because you want to filter every appointment.  In the criteria, you tell it that you want it to return any row where the Office # value (in the appointments table) is equal to the current Office # value from the office lookup table.

 

Then all you have to do is create your HTML table from the results of the filter block.

 

When you look at the run history, you'll be able to go through and see what each loop iteration looks like.  Here are the first two iterations from my test flow:

 

Iteration #1:

result1.png

 

Iteration #2:

result2.png

 

If this is still confusing, search youtube for videos related to the Apply to each block and the Filter array block.  Once you have a sense of how both of those work, the rest of this should fall into place.

View solution in original post

Samizdat
Frequent Visitor

Awesome thank you so much for your assistance it is truly appreciated. If you have time, my only remaining question is I'm not sure how the correct way to get the data from both tables prior to the apply to each step. I'm using list rows present in a table and then pulling the appointments table first. Do I just repeat that with another list rows present in table and select my other table? 

 

When doing this it automatically changed my apply to each to look like this, where it added 2 new apply to eaches. not sure if I am doing it properly?

 

Samizdat_0-1598280866406.png

 

Jronash
Solution Supplier
Solution Supplier

From your screenshot, it looks like the Excel connector puts the array of rows into the Value container, which is part of the Body.

 

So to fix this issue, you need to refer to the 'value' from the applicable spreadsheet, rather than the 'body'.

 

So in the Apply to Each block, set the source to 'value' from the Offices lookup table.

 

In the Filter Array block, set the From field to 'value' from the appointments table.

 

The Create HTML block should still be set to the 'output' of the Filter Array block.

Samizdat
Frequent Visitor

It works! You have saved several people a ton of weekly work by helping me figure this out, thank you so much for taking time out of your day to assist me with this @Jronash👍

 

For posterity, here is what I ended up with since I wanted to be able to run it from sharepoint documents directly for anyone who stumbles across this in the future. 

 

Samizdat_0-1598299840102.png

 

 

Good evening, @Samizdat.

 

I have a similar case. It is about send an email with information of appointment's supplier. I have a excel file with data table and I would like to do a flow similar your solution. Can you help me how I should make it, please??

I read all conversation but I couldn't run my flow.

 

Best regards,

 

Jhonatan Ora

Helpful resources

Announcements
Process Advisor

Introducing Process Advisor

Check out the new Process Advisor community forum board!

MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Users online (26,721)