14 Day reminders if there's no end date

[ConnellP]

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? 

[JohnAageAnderse]

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

[ConnellP]

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!

[JohnAageAnderse · Accepted solution]

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

My 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

[ConnellP]

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! 

[JohnAageAnderse]

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

[ConnellP]

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

[JohnAageAnderse]

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

[ConnellP]

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

[JohnAageAnderse]

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

[ConnellP]

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 🙂