Send Email once to distinct user for multiple sharepoint list Items

[Alex_Security]

Hi All, 

hoping someone here will be able to help me. I run a sharepoint list for our sales team and that holds account name, creation date, and salesperson name as three columns. I've built a flow to send an email to the salesperson when the item exceeds 30 days (so they can update it), and whilst that flow works absolutely fine, if one salesperson has say, 7 items for 7 different accounts, they receive 7 emails. (one for each list item.)

what i'd like, is for the flow to filter the list based on the user, compile a table of all items aligned to that user, input that table into the body of a send an email and send 1 email, with a table of all accounts aligned to them. the current flow that works looks like this:

 once the variable is set it sends an email using the variable in the 'To' field, with the individual item details in the bottom.

thanks in advance!

[fchopo]

Hello @Alex_Security 

You could do something like the following:

1) Get the SharePoint items where the ReviewDate (or whichever date field you have) is less than today plus 30 days using an OData filter query:

ReviewDate le 'formatDateTime(addDays(utcNow(),30),'yyyy-MM-dd')'

2) For each item, store the AssignedTo (or whichever field like SalesPerson) in an Array.

Once we have the different AssignedTo (salespersons) users, for each of them, we can filter the SharePoint items and send an email:

1) Compose: Make a distinct operation (remove repeated users)

2) For each user (salesperson), get the SharePoint items, create a HTML table with a couple of columns and send it by email.

I've done a test on my side and it works!

Hope it helps!

Ferran

[Alex_Security]

Hi @fchopo 

that looks close to what i need, although my main issue is the salesperson column is created as a patch through a powerapp of the User().FullName entity, so appending their name only to a variable won't allow the sending of an email, hence why i used the search for users feature using the salesperson column in the for each. how would i achieve this in your model?

thanks for your help!

[fchopo]

It would apply the same, and the only difference would be that you need to search for the user just before sending the email (last step).

Take into account that the search for users may return multiple results, but you would only need the first one (using first function would solve this).

Hope it helps!

Ferran

[Alex_Security]

so tried this, but i'm now getting the below?

[fchopo]

Hello @Alex_Security 

Be careful with the "current_item" in the filter action. It should reference the "apply to each 3" action:

Hope it helps!

Ferran

[Alex_Security]

ahh that did it - last one, i'm filtering in the get items using the ODATA filter query by a choice field (either open, lost or won). do you know how to set this so it works?

[fchopo]

Hi @Alex_Security 

Unfortunately you can't use OData filters in a choice field. So you have 2 options:

1) In SharePoint create a calculated column (the value in the choice field) and use this column in the OData filter.

2) Get all the items in SharePoint and apply a "Filter" action to them (less efficient than option 1).

Hope it helps!

Ferran