Create HTML and split table

[Jenine1_1]

Hello,

i have a workflow to send an email to managers on when their inspections are overdue. this is working but i have a create HTML that i need to split up via sites.

So i would like to have 1 email for each site. as above it would be 3 emails.  what i am getting is one email with all the sites even though they have different email addresses attached to the sites.

i have a union to combine the same name emails together.  

i tried to use a union to combine site details but it didnt work.

hope someone can help

thanks

[Nived_Nambiar]

Hi @Jenine1_1 

Just give a try with below steps 🙂

I have created an table in excel like below

Now flow screenshot overall

Step 1: List all rows under the table shown earlier

Step 2: Use select action to select Site values and store into array like below

Step 3: Now to retrive unique values from array of site values obtained in step 2, we will use union operation like below

 Inside the loop we perform following action.

 3a. Next use filter array action to filter values (obtained from list rows in table action) with site value obtained in iteration like below

3b. Next select required columns values from output obtained from filter array like below

(In my case date was not appearing in yyyy-MM-dd format, so i used formatDateTime to convert datetime format 

expression:  formatDateTime(item()?['DueDate'],'yyyy-MM-dd')

3c. Create html table out of output from select action like below

3d. Now use send email action to send email like below

in body part, i have used replace expression so that i can add borders to html table like below

replace(body('Create_HTML_table'),'<table>','<table border="1">')

Output you will get will be 3 emails for each site.

Hope this idea helps you !

Mark it as solution if it resolves your query 

[Jenine1_1]

Thank you Nived N,

The only thing that seems wrong is that I don't have a spreadsheet with names, it comes from a sharepoint list.

please see my workflow

This is my sharepoint list

[Nived_Nambiar]

Hi @Jenine1_1 

One question here, do you need to send email to specified person mentioned under email category ?

Also in my flow, you can maka changes with respect to sharepoint. Only thing that would differs is instead of list rows, we will be using get items and dynamic content of sharepoint list

[Jenine1_1]

Yes i do.  I have a list of many inspections that have the same email address names.  I will try with your flow.

thanks so much

[Jenine1_1]

I tried but it didnt do what i needed.  i have a union function to merge the same emails addresses together, but i also need to be able to split up the site details (as in original text above).  I need 3 emails with the table showing the site against the email names

1 email with 2 site

1 email with 1 site

1 email with 2 site

hope that makes sense

thanks

[Nived_Nambiar]

Hi @Jenine1_1 

So in general you need a flow which send site details per email person right ?

[Jenine1_1]

yes that is correct.

at the moment i can send 3 emails, but it has all sites on one email, please see below

so i want to have

1 email with the 2 RAC site together (same email address for both)

1 Email with RAC3

1 emails with Western Hub and RAC Brompton together (same email address for both)

hope this makes sense

[Nived_Nambiar]

Hi @Jenine1_1 

You have to do looping on top looping within each site in the flow design which i have suggested.

Flow design :

Here we will select the emails from sharepoint list and use union operation to get distinct emails out of the emails.  provided. Then bot will loop through each of it and it filters the sharepoint list with respect to email and then it take the distinct list of sites from filter list using union and loop through it again .

I have provided names for action in the screen to understand things quickly 🙂

[Jenine1_1]

I am very grateful for the help.  But you have lost me.

am i to do the original flow and then add on to the flow your latest response?

can you please show me the formula for the union

body

item

for both the apply to each for email and site.

thank you

much appreciated

[Nived_Nambiar]

Hi @Jenine1_1 

I would say try the new design which i have suggested 

AS per you said i am sharing the expression i have used in flow

Expression: union(body('Select_-_Email'),body('Select_-_Email'))

expression: union(body('Select-_Site'),body('Select-_Site'))

Hope this helps 🙂

[Jenine1_1]

I cant seem to get this working.  the apply to each for both the email and site, dont seem to pick up anything.  i am following your advise but nothing works.  thanks for the help anyway, i will have to rethink what to do

[Nived_Nambiar]

Hi @Jenine1_1 

did you meant the loop is not iterating through any elements?

If yes, could you share screenshots of flows, specifically loop part?

[Jenine1_1]

please see word document. it has both the workflow and the results of the test workflow

[Nived_Nambiar]

Hi @Jenine1_1 

See the select site part, it is empty.

Could you check the output of filter array before select site action ?

[Jenine1_1]

Sorry but i am not getting it to work, i have changed the flow a bit. and i get what i want (sort of) but do not know how to combine the outcomes into an email.  please see attached word document