Filter Array from 2 table and sent unique email

[hongdet]

Hi Power Automate Community, 

I'm trying to build a flow which is able to read thru 2 different table and sent out unique email to individual with consolidated data. 

Example Table:

The output I'm trying to achieve would be - 

Email 1

Dear John,

You have the following hardware:

- Apple [Serial 10001]

- Windows [Serial 20002]

- iPhone [Serial 111100]

Email 2

Dear Ken,

You have the following hardware:

- Apple [Serial 30003]

Email 3

Dear Kelly, 

You have the following hardware:

- Tablet [Serial 222200]

And the list goes on...

I've reference to most material online but can't seems to figure out how can I filter array from 2 different table and combined it before sending out the email. A user could have a record in table 1 and not have a record in table 2.

Both excel are are hosted on OneDrive for Business. 

TIA for all the help! 🙂 

[Redders]

Hi @hongdet,

I am going to assume that you are comfortable in creating the emails once you have combined the data together and so I will advise you how I would bring the data together.

I would use the List rows present in a table for both tables then use a compose to combine the data. I would do this using a union expression

union(outputs('List_rows_present_in_a_table_2')?['body/value'],outputs('List_rows_present_in_a_table')?['body/value'])

Once this had been done, I would then run the flow to get a sample output from the compose, then use a parse JSON following. I would generate from sample and paste in the output from the previous test. This then provides all of your dynamic content

[hongdet]

Hey @Redders ,

Thanks for the union trick! It does the magic and I was able to see both table data as 1 single output! 

And sorry for not stating in the initial thread, I don't really know the email portion hence would need your help too 🙂 

TIA! 

[Redders]

Hi @hongdet,

I'm glad I've got you over the first hurdle however, the email one is something that I have yet to do.

From what I have read so far, it uses the same union function that I previously mentioned however, this time you will need to 

• Initialise an array variable (the best practice is to do this at the beginning of your flow)
• Append the email addresses (from the first part of the solution) to that array variable
• Union the array variable against itself (just put the array variable in both parts of the expression in a compose)

This will then give you a list of unique email addresses where you can use an apply to each (from the compose) and a condition (where compose email address = email address in the compiled list in the first solution) and create an email. 

I hope this makes sense

[hongdet]

Hi @Redders ,

Thanks alot for the respond! I'm a little confuse and help your help further. 

Here's the flow that I've built so far - 

Sorry for the trouble and hope you can guide me further. 

Thanks in advance! 

BR, 

hongdet

[Redders · Accepted solution]

Hi @hongdet,

I'm not 100% on this so it might not work, like I said, it's not something I have done yet. I think that you have a couple of issues. I can't be certain as I don't know which dynamic content has come from where.

Your apply to each should be based on each email in the compose 2. So for each email address, filter the array from your parse JSON, create your HTML table then send the email (using the email from compose 2)

━━━━━━━━━━━━━━━━━━━━━━━━━

If I have answered your question, please mark your post as Solved.

Solved questions helps users in the future who may have the same issue or question quickly find a resolution via search.

If you liked my response, please consider giving it a thumbs up. Thank you!

[hongdet]

Thank you very much @Redders !