How to Avoid multiple emails when share point items created

[Sivaperumal31]

Hello.

I have 2 share point list -

1. Transaction List  - Contains client transaction details with country column and does not contain email id of country managers.

2. Country Mangers List - Contains email ids of each country managers along with country column. 

Both lists have country as common column.

My requirement is to send email to country mangers whenever  transaction share point is updated with entries. I have achieved the same with help of an expert from community. The flow starts with auto trigger when item is created in transaction list and reads country managers list and sends email. And it works perfect for below example transaction list .

Each item contains different countries , Country managers get email when an entry is created in transaction list 

But the same flow sends 4 emails to same manager when Transaction share point list is updated with entries for same country like below. Which I would like to avoid.

I understand that this is expected behavior when the auto trigger is defined as  "When an item is created" in Transaction share point list.

But I would like to know of any alternate way to send only one email if transaction share point list is updated with entries for the same country, It does not have to auto trigger . I can easily do it if email id is contained in Transaction list itself . But

How can I combine 2 share point list and look up to send email with manual trigger. 

Flow I created is below. 

[selfy100]

There are two different ways I would tackle this.

1. If I was adding new transactions to the list all day long, and wanted to send the manager 1 email at the end of the day that listed all the transactions, I would change the flow from an Automated trigger, that sends an email whenever a new transaction is input, to a Scheduled trigger, that runs at a certain time everyday and finds all the transactions for each manager and sends them together.

2. If I transactions come in bunches and I want to send the manager several emails throughout the day I would add a Delay of 5 minutes or so into the flow you posted above, then after the delay, collect all the transactions that have been added since the flow was triggered and send them together to the manager.

Both would be fairly simple to set up. Let me know which one would work better for you and I will workshop the flow!

[Sivaperumal31]

Hi @selfy100 ,

Thank you for the time and the help. 

I expect several countries to be updated in transaction list with multiple entries for each country. I really do not need to keep sending email throughout the day. Once in a day , probably at the end of the day, the flow can run and trigger one email to each country managers saying that new entries are added in transaction list since last time they got the email would suffice. 

So I think option 1 with scheduled trigger once a day should be fine.

Just to provide accurate scenario, transaction list would like below at the end of the day. 

The country manager list may have multiple managers for given country, Meaning, multiple entries for same country like below.  One email can be sent to all 3 managers together for the country 'CN' below.

Thank you.

[selfy100]

This is absolutely doable using Filters in the Get Items action.

I will put together a flow for you and post it shortly.

[grantjenkins · Accepted solution]

Hopefully this is what you're looking for. I've assumed all your fields are Single line of text except for Amount which is of type Number. I've also used the Title fields (renamed as Country) for the Countries. This will run the flow daily, get all items that were created in the last 24 hours, and send them to each Country Manager as a Table.

I've used the following lists for this example.

See full flow below. I'll go into each of the actions.

Recurrence is set to run daily at 6AM (you can set this to suit your needs).

Get Transactions (Get items) retrieves the items that were created in the last 24 hours. The expression used for the Filter Query is:

Created gt '@{addDays(utcNow(), -1)}'

Get Managers (Get items) retrieves all the Country Managers.

Select Countries (Select) gets an array of all the Countries (our Title field). Note that Map is set to Text mode (see screenshot).

Compose HTML style (Compose) is some CSS that will be used to style the HTML tables within the email. I've set it before the loop as we only need to create this once, then use it multiple times.

<style>
    table {
        border-collapse: collapse;
    }
    table td,
    table th {
        border: 1px solid #ddd;
        padding: 6px 20px;
        text-align: left;
    }
    table th {
        background-color: #1C6EA4;
        color: white;
    }
</style>

Apply to each iterates over each of the Countries. We use the union expression to ensure we remove any duplicate Country values.

union(body('Select_Countries'), body('Select_Countries'))

Filter Transactions (Filter array) extracts the transactions for the current Country that we are iterating over.

Filter Managers (Filter array) extracts the managers for the current Country that we are iterating over.

Select Manager (Select) extracts the Manager emails. Note that Map is using Text mode here similar to the Select Countries. The expression used is:

item()?['Manager']

Create HTML table uses the output from Filter Transactions and maps the fields as below.

//Title
item()?['Title']

//CRM ID
item()?['CRMID']

//Client Name
item()?['ClientName']

//Amount
item()?['Amount']

Send an email uses the output from Compose HTML style and Create HTML table in the Body, Current Item (Country) in the Body and the Subject, and the following expression in the To field that joins each of the Country Managers.

join(body('Select_Managers'), ';')

When the flow runs (daily) each of the Country Managers will receive an email with any items that have been created in the last 24 hours. See example emails below.

----------------------------------------------------------------------
If I've answered your question, please mark the post as Solved.
If you like my response, please consider giving it a Thumbs Up.

[Sivaperumal31]

HI @grantjenkins ,

Thank you so much for the effort to help me here, really appreciate it.

I followed the steps , and it worked almost perfect with one small issue . 

Issue 1 - (Fixed it somehow ) 

Email content was blank with header alone like below, So I changed the formula for header in HTML table step like below for the example of country column. it fixed the problem

Issue 2: When the transaction list contains only data for one country - lets say CN, I still get email trigger for all the countries maintained in country list ,  even though no transaction is maintained or posted in transaction list for that country, In my scenario, country list will be static with all the managers for all the countries. 

But the flow loops for every country maintained in country list and send email to respective managers with blank content, is there anyway to avoid sending mail for the countries for which no entry posted in transaction list? As it will be misleading.

My country list is as below 

Transaction list only contains entries for one country - CN, but I get email for SG too with blank table. 

Thanks again

Update on Issue 2: 

Tried creating condition to check the length of the HTML table before sending the email a But its still not working - condition is always true for mail trigger for both scenario.

Found that length of the empty HTML table is 105 as it contains headers , so changed the condition like below and it worked. 

[grantjenkins]

@Sivaperumal31 

Issue 1: Yes, you would need to use your own field names here.

Issue 2: I'm assuming this is because you are using the values from Get Managers in Select Countries. You will need to use the values from Get Transactions here, so it only has Countries returned in the Transactions.

Issue 3: This will be resolved automatically after fixing Issue 2. You won't need to have a Condition at all in the flow as per my example.

[Sivaperumal31]

HI @grantjenkins ,

Spot on, That's exactly where I made the mistake. Thank you