cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

How do I set up a flow to send an email at the end of a day but only on those days a Sharepoint list is updated and include specific column info from the list in the body of the email.

Hi, so here is my scenario.   I have a scheduling request powerapps app that is tied to a Sharepoint List.

 

On some days there will be zero additions to the list and on others it could be 10 so I don't want to send an email every day or every time the list is updated.  Rather I want to share the list only once a day (at the end of the day or beginning of the next) via email that additional requests have been added.

Additionally I would like to have a summary of only those request that were entered that didn't assign a rep to handle the job. In other words that column would be blank in my sharepoint list.   


For example if this was my list of things that were added on a Tuesday.

 

Requesting RepClientClient SiteDates NeededNotesAssigned Rep
LarryBig CityMilwaukeeJuneNo Friday Visits 
LarryBig CityMilwaukeeMay Jean
JeanLiquid JunkToledoMay  

 

An email would go out at the end of the day or beginning of the next with something like the following:

Hello, There have been new requests added to the Scheduling List.  If you or one of your team can handle the visit please update the list.

Requesting RepClientClient SiteDates NeededNotes
LarryBig CityMilwaukeeJuneNo Friday Visits
JeanLiquid JunkToledoMay 

 

So even though Larry put in two requests, only the one he didn't already assign someone would get sent in the email.


Finally, I would also like to send out a weekly email that (if there are any), that summarizes any and all open requests that haven't been assigned yet.

 

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User III
Super User III

Hi @barkir 

 

I assume Assigned Rep is a string filed type.

 

Please follow the below.

 

First we need to filter the list. Here I am running the query to find which are the records created yesterday. Schedule the trigger to run early morning everyday before the business starts.

 

The expression I used is as follows:

formatdatetime(addDays(utcNow(),-1,'yyyy-MM-dd'),'yyyy-MM-dd')

 

image.png

 

Next we need to select the columns we want. 

 

image.png

 

Next convert the above results to HTML table and send email.

 

image.png

 

If you have anymore questions please let me know.

 

Thanks



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog

Power Automate Video Tutorials

View solution in original post

22 REPLIES 22
Highlighted
Super User III
Super User III

Hi @barkir 

 

You can use the scheduled trigger (Daily) and SharePoint List Items action steps. Under the List Items use the filter createdon date filtered today or yesterday (depending on when you sending)and assigned rep is null will filter the records you want.

 

image.png

 

To find todays date use expression utcNow() for the filter query to extract the relevant records.

 

To create HTML Table and send email please see below post

 

https://powerusers.microsoft.com/t5/Building-Flows/Create-HTML-tables-from-a-Sharepoint-List-sorted-...

 

 

Create another flow for weekly run and change the filter query accordingly (From and to created date) to list all the records.

 

 

Thanks



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog

Power Automate Video Tutorials
Highlighted

@abm thanks for the response.  I am still a bit confused.

 

"and assigned rep is null will filter the records you want."   Where does this get input?

 

"To find todays date use expression utcNow() for the filter query to extract the relevant records."  Where does this get input?

 

Also I was a bit confused by the answer in the link as it seemed to show how to link up two different sharepoint lists but I am not sure how to set up the HTML Table in the body of the email so all the relevant rows appear.

 

thanks

Highlighted
Super User III
Super User III

Hi @barkir 

 

Will something soon.

 

Thanks



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog

Power Automate Video Tutorials
Highlighted
Super User III
Super User III

Hi @barkir 

 

Is that Assigned Rep is string data type? Please let me know.

 

Thanks



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog

Power Automate Video Tutorials
Highlighted
Super User III
Super User III

Hi @barkir 

 

I assume Assigned Rep is a string filed type.

 

Please follow the below.

 

First we need to filter the list. Here I am running the query to find which are the records created yesterday. Schedule the trigger to run early morning everyday before the business starts.

 

The expression I used is as follows:

formatdatetime(addDays(utcNow(),-1,'yyyy-MM-dd'),'yyyy-MM-dd')

 

image.png

 

Next we need to select the columns we want. 

 

image.png

 

Next convert the above results to HTML table and send email.

 

image.png

 

If you have anymore questions please let me know.

 

Thanks



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog

Power Automate Video Tutorials

View solution in original post

Highlighted

@abm   Thanks.  Running it for tomorrow so I will let you know

Highlighted

Hi @abm I got this error on the second step

 

"status": 400,
"message": "The expression \"2020-03-11T13:19:47Z gt '2020-03-10' and eq null\" is not valid.\r\nclientRequestId: df4f75c9-9076-4147-ace2-f440ee5e4d3b\r\nserviceRequestId: df4f75c9-9076-4147-ace2-f440ee5e4d3b"
 
Here is what I have in the 2nd step for the filter query.
Annotation 2020-03-11 093222.jpg
Also having issues with the next step as the only thing that saves is the "Notes" column in there.  Not sure what to put on the left side as I can't type but have to choose from a list similar to the right
 

Annotation 2020-03-11 092501.jpg

 

 

 

 

thanks

 

 

Highlighted
Super User III
Super User III

Hi @barkir 

 

Did you press enter after the equal 'eq'? If you have the enter key in that filter query please remove it.



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog

Power Automate Video Tutorials
Highlighted

@abm Nope.  No return after either eq or null  Thanks

Highlighted
Super User III
Super User III

Hi @barkir 

 

Try to test with one condition now. Make sure that condition is working fine or not. 

 

Also what's the data type of the null column you are checking?



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog

Power Automate Video Tutorials
Highlighted

Hi @abm it is single line of text.  Going to try and do it without the date function and see how that works.  Will report back.  Thanks

Highlighted

Hi @abm , So I run my flow and it says to add a new item which I do but nothing happens.  Wondering if this may be my issue.  I have the filter under "get items" as 

 

Annotation 2020-03-11 161750.jpg

 

Should it instead be  

Assigned_x0020_SNI_x0020_Rep eq null
 
Here is the rest of what I have:
 
Annotation 2020-03-11 161750a.jpg
"Assigned_x0020_SNI_x0020_Rep" is what appears in the code versus a dynamic choice that I have in the query
Highlighted
Super User III
Super User III

Hi,

 

Could you please post your flow screen shot? If you are using Get Items then under the filter query you need to use Assigned_x0020_SNI_x0020_Rep eq null. You need to use the column schema name. Here you can't use the dynamic mapping values. It's difficult to give you an answer at this point so a screenshot may be helpful.

 

Thanks



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog

Power Automate Video Tutorials
Highlighted

Hi so I don't want to keep bugging you as you have been more than gracious with your time.   I replaced the above with the suggestion

 

Assigned_x0020_SNI_x0020_Rep eq null
 
It still wasn't working when testing.   During testing it would say add a new record to run the flow and I would but nothing happened. So I scrapped it completely and went with a Run when Triggered instead...AND IT WORKED!  So the issue with my flow was in the first part where it was trying to see if a new entry was made.

So I think instead of what I was trying to do  I will just have an email sent to me when a new item is added and then I can decide to manually trigger the flow to send out all the open requests to the team.
 
One last request:  One of the fields I would like to add to the Select portion is Type of visit.   However this is a drop down in my data source and it gives me too much info in the final email.
 
All I want to see under "Type of Visit" is "Training" in this record but getting much more.  Any work around?   Thanks again for all the amazing guidance.
 
Annotation 2020-03-11 180710.jpg
Highlighted
Super User III
Super User III

Hi @barkir 

 

Happy to help 😀

 

If you want to change the trigger back to when a item is created then you need to make sure the column schema name is correct. To find the correct schema name click the settings of the list. Click and open the field. Then check the URL (Browser) as you can see field=xxxxx. Copy the field name from the URL and paste in your flow action query step. Hopefully that should work as expected.

 

Looks like the type of visit is a multi-select field so the output of this field is an array.

 

If you want to use the expression to get the value of this field, you should add array index to get the element, like item()?['Type_x0020_Of_x0020_Visit'][0]['Value'].

 

If this choice filed is radio option, it is recommended that you could set this field to Radio Buttons, then you could use item()?['Type_x0020_Of_x0020_Visit']?['Value'] directly to get field value.

 

Thanks



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog

Power Automate Video Tutorials
Highlighted

Sorry....thought I was done.   Not sure where this goes

 

If you want to use the expression to get the value of this field, you should add array index to get the element, like item()?['Type_x0020_Of_x0020_Visit'][0]['Value'].

 

Put it here as an expression but not sure that was where it goes b/c it failed on testing.   Thanks

 

Annotation 2020-03-11 203416.jpg

Highlighted
Super User III
Super User III

That looks good to me. 



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog

Power Automate Video Tutorials
Highlighted

@abm thanks.  As I mentioned it fails on testing though.  So not sure.  Might just have to forgo that column in the final.  Thanks again!!  You have been an incredible help.

Highlighted
Super User III
Super User III

Hi @barkir 

 

Check the column schema names is correct?

 

Thanks



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog

Power Automate Video Tutorials

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

Top Solution Authors
Top Kudoed Authors
Users online (11,878)