Scheduled power automate flow to send email for deadline approaching

[laz8592]

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? 

[eric-cheng]

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')

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

[laz8592]

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. 

[eric-cheng]

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

[laz8592]

List items. 

[eric-cheng]

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

[laz8592]

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. 

[laz8592]

Any News? 

[DamoBird365]

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:

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

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 😉

[laz8592]

Thanks Damien, 

The deadline column does not include time only date. 

Attached my get items history and the output of the download.  

[DamoBird365]

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

[laz8592]

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 

[DamoBird365 · Accepted solution]

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

[laz8592]

Thanks for the help Damien! 

Flow now works, cheers!

[laz8592]

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.