email certain list items to different people based on a column

[rayray1]

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:

Title	Checked (Yes/No column)	Lookup: Location	Lookup: Email address
car	yes	Australia	joe@company.com
boat	no	USA	lisa@company.com
plane	no	Germany	bob@company.com
ship	yes	Germany	bob@company.com
truck	no	Australia	joe@company.com
scooter	yes	USA	lisa@company.com
motorbike	no	Germany	bob@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:

Title	Checked (Yes/No column)	Lookup: Location
car	yes	Australia
truck	no	Australia

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

Title	Checked (Yes/No column)	Lookup: Location
boat	no	USA
scooter	yes	USA

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

Title	Checked (Yes/No column)	Lookup: Location	Lookup: Email address
plane	no	Germany	bob@company.com
ship	yes	Germany	bob@company.com
motorbike	no	Germany	bob@company.com

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

Thanks

Ray

[rsaikrishna]

@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 Solution. Marking 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.

[edgonzales]

@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).

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.

[rayray1]

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?

[edgonzales]

@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.

[rayray1]

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:

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

[edgonzales · Accepted solution]

@rayray1 

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

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

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

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.

[rayray1]

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

[edgonzales]

@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

[Anonymous]

Hi Ed,

Thank you for posting this it's exactly what I am trying to do.   Unfortunately I am falling at the last hurdle when I try to send the email.  I'm getting the error below, do  you have any suggestions?  I'm really new to power automate and self taught so I'm sure it's something basic.

Thanks,

Sharryn

[edgonzales]

@Anonymous 

So, it looks like whatever value you are dropping into the "To" field of the email is a "List" or what they call an "Array" (note the square brackets around the value "[" - that indicates an array value, meaning: there could be more than one thing in there...which is making Outlook freak out a little.   Start a new thread and post a pic of this step (in edit mode) along with a pic of where the email address is coming from.  Be sure to tag me in it, and we'll take a look.  Great work so far, though!  🙂

-Ed

[Anonymous]

Hi @edgonzales 

Thanks so much for reply. I had another look just now with some fresh eyes and realised the to box needed to be the expression items('Apply_to_each')?['eMail'] and it's all working perfectly now.  Thanks again, this is going to be a huge help!