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

Scheduled power automate flow to send email for deadline approaching

Hi 

I am quite new to Power Automate so any help is appreciated. 

I have a Team which I have added a Microsoft List in one of the tabs for project management between employees. The Manager creates a project and assigns the project to another person for proceeding the tasks with a deadline for the project. 

 

In sharepoint, the deadline column is a 'date and time field' and the assigned column is a 'person or group' field. 

I want to do a scheduled power automate flow to run daily to check the deadline date column and send a reminder email to the person who is assigned for that project that the deadline is approaching (say 5 days before) . 

Can someone please advise the steps on how to achieve this? 

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @laz8592 

 

I've just realised that the date of your list needs to be compared with today +5.  I.e. if today is 22/09, you want to return list items where date equals 27/09, I see that the original formula is -5, which would return 17/09 or based on yesterday's example 16/09.

 

Try formatDateTime(addDays(utcNow(),5),'yyyy-MM-dd') and if you have any dates in your list for 27/09, they should be returned and emailed 🤞

 

Damien

View solution in original post

14 REPLIES 14
eric-cheng
Solution Sage
Solution Sage

Hi  @laz8592 ,

 

Try the below.  

 

The formula in the filter will get all files where ExpirationDate is 5 days away.

 

@formatDateTime(addDays(utcNow(),-5),'yyyy-MM-dd')

 

Screenshot_20210916-234637_Chrome.jpg

 

--------------------------------------------------------------------------
If I have answered your question, please mark my post as a solution
If you have found my response helpful, please give it a thumbs up

Connect on LinkedIn

Hi Eric 

 

I applied all the settings as per your workflow, and did a test and everything was checked. BUT , when the flow ran on scheduled no emails went to the assigned email. 

 

I have a feeling that maybe something wrong with the library name  as I have to enter a custom value as it doesn't find it when getting item properties (it only sees documents), it only sees the site (see below). It is a list not a library. 

 

laz8592_0-1631874250341.png

 

eric-cheng
Solution Sage
Solution Sage

Hi @laz8592 ,

 

Are you looking for list items or documents in a library?

 

--------------------------------------------------------------------------
If I have answered your question, please mark my post as a solution
If you have found my response helpful, please give it a thumbs up

Connect on LinkedIn

List items. 

eric-cheng
Solution Sage
Solution Sage

Hi @laz8592 ,

 

You can use Get Items and the same filter 

 

--------------------------------------------------------------------------
If I have answered your question, please mark my post as a solution
If you have found my response helpful, please give it a thumbs up

Connect on LinkedIn

Hi Eric

 

Even with the 'get items' filter, no emails arrive in the mailboxes. The assigned emails is a column whereby the emails are added in.

No emails are sent to these even when adjusting the deadline dates to be less than 5 days. Is the timezone have to be utc time like in the filter? I have european time on my site  , maybe that's why ? The test of the flow is all ticked too. 

 

 

Any News? 

DamoBird365
Super User
Super User

Hi @laz8592 

 

I appreciate you reaching out to me, from what I can see from above it would appear that despite using get items and the filter expression date eq formatDateTime(addDays(utcNow(),-5),'yyyy-MM-dd'), your emails are not being sent.

 

Presumably you have an apply to each on the value from the get items action which sends the email but I understand no email is sent.

 

To help me help you, I would need a screenshot of your flow, but also, I think it would be worth you exploring the history of the get items, does it return 0 items?  If so the filter is not working.  Can you check if the date includes a time on the List?

 

This is what I would expect your solution to look like:

 

DamoBird365_0-1632226215710.png

 

This is how you check the history output of the get items:

DamoBird365_1-1632226388464.png

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien


P.S. take a look at my new blog here and like & subscribe to my YouTube Channel thanks 😉

 

 

Thanks Damien, 

 

The deadline column does not include time only date. 

 

Attached my get items history and the output of the download.  Get Items.jpgDownload error.jpg

Hi @laz8592 

 

So you can see from the history at the end of the array that the value is an empty array [].  Therefore there are no deadline dates equal to 2021-09-16.  Can you check your list for list items with that date and if there is definitely one in there, create a new manually triggered flow, with the get items (no filter) and check the history output.  You should be able to check for the deadline date in the history and hopefully debug what is going on?!

 

Columns have an internal name but if it is wrong, I wouldn't expect your get items to run without an error.  So, try above, and hopefully we'll be able to solve your problem.

 

Damien

Hi Damien

 

There are no deadline dates before the 16-09-2021 in that column , if you see the screenshot below. I think the logic behind this email reminder is wrong from the beginning. Basically I want the flow to check this 'deadline' column daily and see if any of the dates are approaching 5 days before the project's expiry and if there are send an email to the assigned email.  

 

I don't know why the filter query is equal to the date 2021-09-16, as it doesn't make sense for the above to work. 

See below my columns 

 

Deadline column.jpg

Hi @laz8592 

 

I've just realised that the date of your list needs to be compared with today +5.  I.e. if today is 22/09, you want to return list items where date equals 27/09, I see that the original formula is -5, which would return 17/09 or based on yesterday's example 16/09.

 

Try formatDateTime(addDays(utcNow(),5),'yyyy-MM-dd') and if you have any dates in your list for 27/09, they should be returned and emailed 🤞

 

Damien

View solution in original post

Thanks for the help Damien! 

 

Flow now works, cheers!

 

laz8592
Regular Visitor

The 'assigned to' column I have now marked as NOT mandatory. So the creator of the task if they don't assign someone but want to be notified by email when the deadline is approaching , how do I do this as the flow is integrated with the assigned column?

 

I have tried adding the 'created by email' dynamic expression in BCC but how will that work if the orginal value output is for the 'assigned'?

 

Any help will be appreciated, I don't want to create another seperate flow for bcc/ccing the creator of the item when deadline approaches. 

Helpful resources

Announcements
MPA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Users online (1,499)