Compare 2 Excel Tables & Send Email Only When Records Match

[Bssmith21]

I have 2 excel tables;

The first table (Contacts) has a list of clients in column A and a list of email addresses in Column B:

The second table (Population) shows a list of transactions for each client.  Column A includes a unique transaction ID and column B has the client name.  This table will be updated monthly.  Clients could have multiple transactions listed or they could have no transactions listed in the Population table for the given month:

I built a Flow in Power Automate that compares these tables and sends an email to each client with a list (in table format) of their transactions for the month:

The issue I'm having is, I only want to send an emails to clients if they have transactions listed in the Population table for the given month.  Currently, the flow still generates an email with a list of transactions (that is blank).

Any assistance would be appreciated.

[edgonzales]

@Bssmith21 

Great work so far!   Let me know if you want more detail, but here's a good way to get the list you're after:

• After each of your list rows actions, add a select action to only get the Seller Name column
• After that, use a Compose action with a "Union" function using the outputs of your selects as the parameters.  Union is used to compare collections and returns the unique list of things they have in common.  (sidenote:  You can use Union to get the unique values of any single list by listing the array twice)
• The output of that Compose will give you a list of just the accounts that appear on both lists.  Use that as the value for an apply to each loop rather than the Excel table you're using now.

The blog article above may help with other questions, as well, since it's doing pretty close to the same thing.

Good luck!  Keep us posted.

-Ed

If you liked this reply, please give it a thumbs up! If this reply has answered your question or resolved your challenge, please consider marking it as a Solution. This helps other users find it more easily via search.

[Bssmith21]

Thank you @edgonzales.  I need some additional detail if possible:

I added the following Select actions:

Then created the union below:

I tried to use the output of union to create an HTML table.  This is where the flow breaks and I get a little lost.  (I'm not sure all of the data I need is included in the union output).

any thoughts on where I messed this up?

Thanks again

[edgonzales]

@Bssmith21 

Super close!   So, the union is only going to give you a short list of company names that are on both lists, right?  So you'll still need to get the data from your spreadsheet to build the HTML table.  First thing, though, rename both of your Select fields to be the same (this will make things easier, later):

Then, adjust your loop to put the "Filter Array" back in (You're still going to filter the spreadsheet array, but instead of matching on each individual SellerName, you'll use the one that is being looked at in the Apply to Each loop:

See if that helps...

-Ed

[Bssmith21]

@edgonzales It appears that my union is not built correctly.  The output from my union is not the short list of items that appear  on both lists, but the total list of items that appear on either list.

The select actions output the expected values:

Select = list of clients from the 'Contacts' table (there are 13 of them in my test data).

Select2 = list of clients for each record in the 'Population' table (there are 27 of them in my test data).

All of the 27 records in my 'Population' table are tied to 5 of the 13 clients.

My Compose / Union Action - union(body('Select2'),body('Select'))

results in a list of 13 records (the individual clients that appear on either table)... when I was expecting the list of clients that appear on both tables (witch would be the 5).

Thoughts? 

Again thank you for any assistance.

[edgonzales]

@Bssmith21 

Totally my fault - I meant "Intersection" rather than Union.  Here's the link to the function: https://learn.microsoft.com/en-us/azure/logic-apps/workflow-definition-language-functions-reference#...

Everything else should be the same.

Sorry about that.  🙂

-Ed  

[Bssmith21]

@edgonzales 

Ok, thank you.  The intersection function worked as expected!  Now I have a short list of clients (sellers) that appear in both tables!

Next, I was able to add an "apply to each" function that takes the output from my compose / intersection function and filters by seller and creates an HTML table. (all of this is working fine and as expected).

The next step is to send the email (containing the newly created html tables) to the filtered clients (sellers).  The email addresses are located in the Contact table that we bring in with the first step of the flow.

Within the Apply to Each I tried to add a "send email" function.  This creates an "Apply to each 2" function, but fails when I select the only available email listed in the dynamic content (from the Contacts table).

Do I need to create another filter array function to pull in email addresses?

Once again, I appreciate any feedback.