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

14 Day reminders if there's no end date

Hi, 

I've got an absence log set up with  'Start Date' and 'End Date' columns. What I want to do is send an email to the person who created the log 14 days after the absence started, as a reminder to either 'end' the absence, or let HR know it's still ongoing. I know how to do this with the reminder function (-14) but I need to write something that means the email doesn't send if there's already an entry in the end date column. 

How can I do this? 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User II
Super User II

Re: 14 Day reminders if there's no end date

Hello @ConnellP 

That sounds/looks very complicated 🙂

This is what I did in order to get items that started 14 days ago and has no end date (DueDate in my list).

Flow.FilterQuery.Dates.Null.pngMy dates are without time!

First expression is:

formatDateTime(addDays(utcNow(),-14),'yyyy-MM-dd')

Second expression is:

formatDateTime(addDays(utcNow(),-13),'yyyy-MM-dd')

 

That's it 🙂

Kind regards, John

View solution in original post

10 REPLIES 10
Highlighted
Super User II
Super User II

Re: 14 Day reminders if there's no end date

Hello @ConnellP 

 

This sounds like you need a recurrent flow that runs every day.

The flow then Gets all items for which the Start Date is equal to Today - 14 days and the End Date is empty (null).

Loop through the items and send a reminder to the creator.

 

What have you already tried?

Kind regards, John

Highlighted
Frequent Visitor

Re: 14 Day reminders if there's no end date

Yep! That's exactly what I'm trying to do, at the moment I've got it set up to do the -14 day notice without any issue, this is what the formula looks like: concat(concat('DateStarted',' ge ', '''',outputs('Today_+_x_days')),'''',' and ', concat('DateStarted',' lt ','''', outputs('x_Days_+_1'),'''')) I'm just having a brain block on how to adjust that formula so it doesn't pull data that has an end date entered! The Absence End Date column is obviously in a different one to the start date, I'm just unsure how to formulate it. Thanks for your help!
Highlighted
Super User II
Super User II

Re: 14 Day reminders if there's no end date

Hello @ConnellP 

That sounds/looks very complicated 🙂

This is what I did in order to get items that started 14 days ago and has no end date (DueDate in my list).

Flow.FilterQuery.Dates.Null.pngMy dates are without time!

First expression is:

formatDateTime(addDays(utcNow(),-14),'yyyy-MM-dd')

Second expression is:

formatDateTime(addDays(utcNow(),-13),'yyyy-MM-dd')

 

That's it 🙂

Kind regards, John

View solution in original post

Highlighted
Frequent Visitor

Re: 14 Day reminders if there's no end date

Hi!

This was working great on Thursday, I took out the '13 days' formula as I didn't think I needed it and it was still working. 

Today it's decided it's going to send an email to blank Date Ended but it seems to be ignoring the -14 rule because it's sending notifications for 6th and 7th of April! 

example.png

Highlighted
Super User II
Super User II

Re: 14 Day reminders if there's no end date

Hello @ConnellP 

Yes, it will, which is why the -13 days was in there 🙂 The startdate must be 14 days in the past, not just 1-13 days in the past!

So put it back and it will work as a 14 day reminder. Kind regards, John

Highlighted
Frequent Visitor

Re: 14 Day reminders if there's no end date

@JohnAageAnderse That's brilliant! Thank you so much!

Another question now, sorry! I'm now trying to track anything that was entered over 14 days from the current day (and has no end date). I've got a flow where it pulls the list into an Excel but I'm struggling to filter it out. 

The earliest entered start date is mid-February, so I attempted something like 'DateStarted ge ' 

formatDateTime(addDays(utcNow(),-50),'yyyy-MM-dd')' and DateEnded eq null. But I think I need to make the Date Started formula an open count? So anything past 14 days from today I need to know about, does that make sense?
Highlighted
Super User II
Super User II

Re: 14 Day reminders if there's no end date

Hello @ConnellP 

As you want to get items before 14 days ago, then the start date just need to be less than -14 days ago 🙂 and with end date empty.

Does this help?

Kind regards, John

Highlighted
Frequent Visitor

Re: 14 Day reminders if there's no end date

Hi @JohnAageAnderse 🙂

I've written the formula as 

formatDateTime(addDays(utcNow(),-60),'yyyy-MM-dd') as that'll cover the earliest possible date entered, but it's still returning values from yesterday and I've no idea why! 
the 'eq null' is working fine but I'm just stumped as to why it's not working, I'm sure it's something completely daft that I'm missing

 

Highlighted
Super User II
Super User II

Re: 14 Day reminders if there's no end date

Hello @ConnellP 

You really don't need that formula 🙂 you only need this:

 

StartDate lt formatDateTime(addDays(utcNow(),-14),'yyyy-MM-dd') and EndDate eq null

 

That will give you all items from the start of time till 14 days ago, which has an empty EndDate.

Kind regards, John

Highlighted
Frequent Visitor

Re: 14 Day reminders if there's no end date

Ah it works!! Thank you so much for your help! I was using ge instead of it, that's where I was going wrong! 

Thank you again 🙂

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.

Upcoming Events

Experience what’s next for Power Automate

See the latest Power Automate innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Users online (6,778)