Date Expiry Expression

(0) Share

Report

Posted on by YSUK

Hello

I have two conditions in a flow which is looking at expiry dates of documents.

The first is looking at when the date is going to be overdue and I use the condition expression

Date of Expiry is less than adddays(utnow(),-1) which gives me when the expiry date is overdue

and then I use

Date of Expiry is less than adddays(utcnow(),7) which gives me (ideally) when it date of expiry is coming up to expiry.

The problem I have is this continues to work even after the expiry date is overdue, so two emails are sent. The first saying it is overdue and the second saying it is due to expire.

Can someone provide me with a better expression for this?

Categories:

General topics

I have the same question (0)

All responses (17)

Answers (0)

Sort by

Chriddle 8,713 Super User 2026 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

Try this:

"Date of Expiry" is less than adddays(utcnow(),7) and "Date of Expiry" is greaterOrEquals adddays(utnow(),-1)

BTW, is this on purpose, that "expiry date is overdue" is less than yesterday? (Shouldn't it be lessOrEquals?)

Was this reply helpful? Yes No
YSUK 10 on at

Like
a
(0)

Report
Copy link

Link copied!

Hello Chriddle Thanks for this. I eventually figured that out.

I am really new to expressions and so sometimes dont understand the logic.

This relates to expiry date of insurance.

I want to send an email if the insurance is overdue. And I want to send another/different email a week before it is overdue.

I simply found a way that it worked not knowing the correct expressions to use.

I have another issue too. In my SharePoint list as below:
I have 5 columns with different insurance dates and sometimes, not all insurances are necessary.

When there is nothing in one of the columns, the flow does not work and therefore I need an expression which ignores it if the column is empty. Any ideas?

Was this reply helpful? Yes No
Chriddle 8,713 Super User 2026 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

You can check for empty values or set a default value if null (coalesce)

Was this reply helpful? Yes No
YSUK 10 on at

Like
a
(0)

Report
Copy link

Link copied!

Again thanks Chriddle. As with many of these things, I struggle with combination of expressions and where to put them in a flow. This is what I am currently doing

I am adding other rows to include the other coloumns but dont think I can add expression in this condition which says if it is empty ignore it, but if there is a date utcNow

Was this reply helpful? Yes No
Chriddle 8,713 Super User 2026 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

I don't quite understand what you want to achieve. Maybe you should describe it in more detail and give an example.

Was this reply helpful? Yes No
YSUK 10 on at

Like
a
(0)

Report
Copy link

Link copied!

Apologies, I did say I was new to this.

I have created a SharePoint list where 5 of the columns relate to the expiry date of certain insurances.
Every day I want it to run a flow to get the items from the SharePoint list.

Then, based on the expiry date of insurances, I want it to check if the insurance is approaching the expiry date within 7 days, and if it is, send a reminder email to say it is expiring.

I also want it to check to see if the insurance ultimately goes overdue and send a slightly different email stating the insurance is overdue.

I ran a few test based on 1 column and got it working fine.
But when I introduced the other condition/rows in the condition e.g. Insurance 1 Expiry Date, Insurance 2 Expiry Date and so on; but if the column is empty (as there is no date in it because sometimes there doesnt need to be) the flow does not work because it cant condition "blank" is less than utcNow.

In an ideal world this is what I would want to have:

Every day run a flow to check the expiry dates of the insurances in the respective columns (ignore if the column is blank) and if any of the expiry dates are expiring within 7 days send an email indicating which insurance is due to expire.
If any of the insurance expiry dates have gone past and therefore overdue, send a different email stating which are now expired.

Hope that makes some more sense

Was this reply helpful? Yes No
Chriddle 8,713 Super User 2026 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!
I created an example with Filters:

Compose (example data)

[ {"Title": "Isurance 0", "DueDate0": "2024-07-19", "DueDate1": "2024-07-10", "DueDate2": "2024-07-15"}, {"Title": "Isurance 1", "DueDate0": "2024-07-18", "DueDate1": "2024-07-11", "DueDate2": "2024-07-16"}, {"Title": "Isurance 2", "DueDate0": "2024-07-17", "DueDate1": "2024-07-12", "DueDate2": "2024-07-17"}, {"Title": "Isurance 3", "DueDate0": "2024-07-16", "DueDate1": "2024-07-13", "DueDate2": "2024-07-18"}, {"Title": "Isurance 4", "DueDate0": "2024-07-15", "DueDate1": "2024-07-14", "DueDate2": "2024-07-19"}, {"Title": "Isurance 5", "DueDate0": "", "DueDate1": "2024-07-15", "DueDate2": null}, {"Title": "Isurance 5", "DueDate0": "", "DueDate1": "2024-08-15", "DueDate2": null} ]

Filter array - Less today plus 7 days

From

outputs('Compose')

Filter

@Or( less( if( empty(item()?['DueDate0']), 'Z', item()['DueDate0'] ), addDays(utcNow(), 7, 'yyyy-MM-dd') ), less( if( empty(item()?['DueDate1']), 'Z', item()['DueDate1'] ), addDays(utcNow(), 7, 'yyyy-MM-dd') ), less( if( empty(item()?['DueDate2']), 'Z', item()['DueDate2'] ), addDays(utcNow(), 7, 'yyyy-MM-dd') ) )

'Z' is just an arbitrarty value greater that utcNow()

Filter array - Overdue

From

outputs('Compose')

Filter

@Or( less( if( empty(item()?['DueDate0']), 'Z', item()['DueDate0'] ), utcNow('yyyy-MM-dd') ), less( if( empty(item()?['DueDate1']), 'Z', item()['DueDate1'] ), utcNow('yyyy-MM-dd') ), less( if( empty(item()?['DueDate2']), 'Z', item()['DueDate2'] ), utcNow('yyyy-MM-dd') ) )

Filter array -Expiring (All items from "Filter array - Less today plus 7 days" that are not in "Filter array - Overdue")

From

body('Filter_array_-_Less_today_plus_7_days')

Filter

body('Filter_array_-_Overdue')

does not contain

item()

Was this reply helpful? Yes No
Chriddle 8,713 Super User 2026 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

Please be aware that the forum UI messes around with @Or in the Filter expressions. (the "at" character triggers a script)

or(...) is always with a small "o"

Was this reply helpful? Yes No
Chriddle 8,713 Super User 2026 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!
... and if you don't need to handle empty strings (only a valid date or null), you can simplify your filters by using coalesce:

@Or( less( coalesce(item()?['DueDate0'], 'Z'), utcNow('yyyy-MM-dd') ), less( coalesce(item()?['DueDate1'], 'Z'), utcNow('yyyy-MM-dd') ), less( coalesce(item()?['DueDate2'], 'Z'), utcNow('yyyy-MM-dd') ) )

Was this reply helpful? Yes No
YSUK 10 on at

Like
a
(0)

Report
Copy link

Link copied!

Hello Chriddle

Thanks for taking the time to help with this. I have done everything you have suggested in here and I don't seem to be getting the third filter array working. I have edited dates in the sharepoint list which shows they are within 7 days and expiry, however the outputs of the third filter are simply []

What have I missed here?

Was this reply helpful? Yes No