cancel
Showing results for
Did you mean:
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
Memorable Member

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:

Second expression is:

That's it 🙂

Kind regards, John

10 REPLIES 10
Memorable Member

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.

Kind regards, John

Frequent Visitor
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!
Memorable Member

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:

Second expression is:

That's it 🙂

Kind regards, John

Frequent Visitor

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!

Memorable Member

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

Frequent Visitor

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

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

Frequent Visitor

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

Memorable Member

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

Frequent Visitor

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 🙂

Announcements

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

We want to hear from you!

If you are a small business ISV/Reseller, share your thoughts with our research team.

Top Solution Authors
Top Kudoed Authors
Users online (1,364)