cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

email certain list items to different people based on a column

Hi all,

I was hoping to get some of your expert advice! I am sure this question has been asked, but I can't seem to find an answer for it.

 

I have a SharePoint list with the following data:

TitleChecked (Yes/No column)Lookup: LocationLookup: Email address
caryesAustraliajoe@company.com
boatnoUSAlisa@company.com
planenoGermanybob@company.com
shipyesGermanybob@company.com
trucknoAustraliajoe@company.com
scooteryesUSAlisa@company.com
motorbikenoGermanybob@company.com

 

I would like to run a flow once a month that goes through the list and aggregates all the data for a Lookup:Location and sends it to the email address (once).

 

E.g. on 01/01/2021 an email will be sent to joe@company.com with the following details:

 

TitleChecked (Yes/No column)Lookup: Location
caryesAustralia
trucknoAustralia

 

at the same time an email will be sent to lisa@company.com:

TitleChecked (Yes/No column)Lookup: Location
boatnoUSA
scooteryesUSA

 

and another email will be sent to bob@company.com:

 

TitleChecked (Yes/No column)Lookup: LocationLookup: Email address
planenoGermanybob@company.com
shipyesGermanybob@company.com
motorbikenoGermanybob@company.com

 

Other than the recurring trigger and the get items, i'm stuck...

 

Thanks

Ray

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User II
Super User II

Re: email certain list items to different people based on a column

@rayray1 

So, first change the Select to get the value of the eMail using this expression:  

 

Item()?[‘LookupLocation_x003a_EM’]?[‘EMail’]?[‘Value’]

 

20200618i.PNG

Next, we just need to fix the Filter Array inside the Apply to each loop:

 

20200618j.PNG

 

The left side will be the same expression you used in the Select statement, and the right side should just be

items('Apply_to_each')?['EMail']

 

Give that a go and see what happens.

 

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.

View solution in original post

8 REPLIES 8
Highlighted
Super User II
Super User II

Re: email certain list items to different people based on a column

@rayray1 

 

Location is a look up column. So you have another sharepoint list where you stored all your locations. Letz call it as LocationsInfo. Assuming LocationsInfo list contains unique locations.

 

Let us call your primary list : XYZ  (the list you mentioned in the screen shots)

 

a. Create a scheduled flow to run based on your required fequency.

b. Get Items - Read items from LocationsInfo list.

c.  Place Apply to Each action with values from Get Items in step b.

d. Within the Apply to Each action, now, place Get Items to read data from XYZ list with Filter Query field "Location value" eq "first value you get" in step c.

f. Create HTML action to build email body 

g. Send email action with email from step C.

 

Please review above steps and let me know if you have any questiosn.

 

Regards

Krishna Rachakonda

If this reply helped you to solve the issue, please mark the post as Accepted SolutionMarking this post as Accepted Solution, will help many other users to use this post to solve same or similar issue without re-posting the issue in the group. Saves a lot of time for everyone.
Highlighted
Super User II
Super User II

Re: email certain list items to different people based on a column

@rayray1 

(Edit: Just realized you were grouping on Location instead of email - good thing @rsaikrishna was paying attention 😁 )

 

Hey there.  This one was fun and comes up a bunch, so I'll elaborate in a blog and share it here when I'm done.  You're off to a great start with the trigger and get items, here's some stuff to get you started on the rest:

 

  • After your "Get items", you want to narrow down to just a list of the unique email addresses.  To do this, follow your Get Items with a "Select" action and in the From part, put the 'value' from your Get Items, and then in the map, put "eMail" (without the quotes) and where it says "Enter value" add the dynamic value from the SharePoint list for the eMail addresses  (Special note here that there are some field names that SharePoint uses inherently behind-the-scenes, naming a column 'Email' might be problematic later, but worked ok here (maybe because of my casing "eMail").
  • The above will produce a list with as many rows as you have items, but will only be the email address column.  To get just the unique email addresses, add a "Compose" action and use the expression builder and the Union() function to self-reference...this will produce a list of 3 email addresses.   Here's my expression (I renamed my Select to "Get eMails") union(body('Get_eMails'),body('Get_eMails')).    
  • Next, we want to cycle through each of those unique email addresses to use them as a filter on your Get Items result.  Add an "Apply to Each" loop, and put the output of the above Compose in the "Select an Output" box.
  • Inside the apply to each loop now, we will add a "Filter Array" action and put the Value from your Get Items in the From box.
    • For the criteria, put the dynamic value "eMail" from your Get items on the left, select "is equal to" and then this expression in the right:  items('Apply_to_each')?['eMail']  this says get me all of the rows that match whatever email from our list of unique emails, we are focusing on right now.  The Apply to each will cycle through each one.
    • Next, I added a step to "Create an HTML Table" from the body of the Filter Array
    • And finally, you can use a "Send eMail" using items('Apply_to_each')?['eMail'in the "To:" bit, and then the HTML table output as the body.

It will look something like this when you're done (mine doesn't have the send email bit, but as long as that is inside the loop, you should get three separate emails in your example).

 

20200616a.PNG

 

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.

 

Highlighted
Regular Visitor

Re: email certain list items to different people based on a column

Hi Ed and Krishna,

 

Thanks very much for your responses - i really appreciate the time  you have both taken.

 

I realised that i have worded the question wrong and I did want to select by email address, not location... sorry about that.

 

@edgonzales I have followed your instructions, which work if i have a text column with the email address in it, but don't seem to work if I am using a lookup column.

 

It seems like the filter array isn't working as nothing is in the output. The UniqueEmail composer is working as it outputs my 2 test email addresses.

 

I have tried variations of the LookupLocation:EM column (which is the name of the text email lookup column)

 

e.g. LookupLocation_x003a_EM_Value and then selecting the value without any success. Is there anything you can see that I am missing?

2020-06-18 20_41_12-Edit your flow _ Power Automate.png

2020-06-18 20_39_59-Run History _ Power Automate.png

 
 
Highlighted
Super User II
Super User II

Re: email certain list items to different people based on a column

@rayray1 

Totally fixable, I think.  Can you share the output of the Unique eMail step?  And you're using the output from that as the value in your Apply to Each loop, right?

 

In the right side of your filter, try adding the bold bits to your expression:

 

items('Apply_to_each')?['LookupLocation_x003a_EM']?['Value']

 

Presuming that the part you blurred out in the Filter Array output is the actual email address, ya?  


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.

Highlighted
Regular Visitor

Re: email certain list items to different people based on a column

Hi @edgonzales,

 

The output of the Unique eMail step is:

 

[
  {
    "EMail": {
      "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
      "Id": 1,
      "Value": "test1@email.com"
    }
  },
  {
    "EMail": {
      "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
      "Id": 2,
      "Value": "test2@email.com"
    }
  }
]

Below is a picture of the flow with the details of each part:

2020-06-19 12_45_44-Edit your flow _ Power Automate.png

 

Also, yes the blurred parts are the email addresses - i had an additional email column I was using for testing, which i have moved for clarity.

 

This is an example of the data going into that filter for that column:

"LookupLocation_x003a_EM": {
      "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
      "Id": 2,
      "Value": "test2@email.com"
    },

Thanks,

Ray

Highlighted
Super User II
Super User II

Re: email certain list items to different people based on a column

@rayray1 

So, first change the Select to get the value of the eMail using this expression:  

 

Item()?[‘LookupLocation_x003a_EM’]?[‘EMail’]?[‘Value’]

 

20200618i.PNG

Next, we just need to fix the Filter Array inside the Apply to each loop:

 

20200618j.PNG

 

The left side will be the same expression you used in the Select statement, and the right side should just be

items('Apply_to_each')?['EMail']

 

Give that a go and see what happens.

 

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.

View solution in original post

Highlighted
Regular Visitor

Re: email certain list items to different people based on a column

Hi @edgonzales 

 

I removed the ['EMail'] part of the statement to change it from:

Item()?[‘LookupLocation_x003a_EM’]?[‘EMail’]?[‘Value’]

to

Item()?[‘LookupLocation_x003a_EM’]?[‘Value’]

 

and it's working for me!

 

Thank you very, very much - not only is it working, but I understand the logic behind it now and really appreciate your patient assistance.

 

The email that comes out has all the columns as objects - would fixing that just be a case of creating a select straight after the "get items" and then referencing that data instead?

 

Thanks again, I'm marking this as the solution and will post the full flow.

 

Cheers,

Ray

 

Highlighted
Super User II
Super User II

Re: email certain list items to different people based on a column

@rayray1 

That's exactly correct.  The Select action is a great way to only focus on the information that you want.  Great work!

 

Here's the blog article I wrote on this issue in case you want to reference in the future:

Group a List for a Summary Using Power Automate

 

It sounds like the differences in our expressions have to do with the data being a tiny bit different.  Glad you sorted it out.

 

-Ed

 

 

Helpful resources

Announcements
firstImage

Super User Program Update

Three Super User rank tiers have been launched!

firstImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

Top Solution Authors
Users online (8,706)