Showing results for 
Search instead for 
Did you mean: 
Frequent Visitor

SharePoint List: How to evaluate if a date has expired

Hi all,

I have a SharePoint list showing expiry dates for certificates and would like to send an reminder email 7 days before the certificate expires. So far, I’ve created a condition that successfully sends an email when the SharePoint list item ‘CHAS’ is changed to today’s date using the following advanced condition:


@equals(triggerBody()?['CHAS'], utcnow ('dd-MM-yyyy'))


Do you know how I could track existing dates that only change once a year? And if possible, can Flow send an email 7 days before the expiry date?



Hi Eco_Graham,


I still don't get your questions.


What do you mean by tracking dates that only change once a year ?

Do you mean logging it ? or getting the value / storing it ?



Flow will send an email based on the configuration on that flow.

So where the expiry date coming from ?

You can use recurrance trigger every day to check that expiry date if you want.


If possible send us a bit of screenshot about how you configure your flow as well.




The SharePoint list has a column named CHAS that lists the annual expiry dates of client certificates. The flow should work in the same way as a recurring calendar event, which sends a reminder 7 days before a fixed date.

A recurrence trigger to check every day if the CHAS date is today’s date + 7 days sounds good. Could you please let me know how you would configure the condition and recurrence?



Try something similar to this.



Btw at this moment we don't support this scenario foreach inside condition yet.

But in the future it may look like this.

Hi can you explain Equals(TriggerBidy.... is there any on-line reference to this? Will this also work for properties on Doclbs?



I'm getting an error on this: @equals(triggerBody()['Submitted'], 'Yes') (doc lib has a field called Submitted of type (Yes/No)

I've added the recurrence and Get Item and now get the following error:

The template action 'Apply_to_each' at line '1' and column '1090' is not valid. Only actions of type other than 'scope', 'if', 'foreach', or 'until' are allowed to be nested under an action of type 'foreach'.

@Eco_Graham Sorry for this problem. (I have created a bug for that error).

But in the mean time, there is another workaround for your problem.


You can try to use "Filter Array" Action

filter array.png


And it's result you can perform an action.

filterarry -foreach.png



@EdRichardSRG @Equal is an expression.

You can see this when you create a condition and click "Advance".


Here is the docs for more information 




Thank you for the workaround. I've tried the following, which saved as a Flow but then sent an email for every list item even if it has no date.


Recurrence: 1 day

Get Items: Certificate Expiry List

Filter Array From: Value; Condition: advanced: @equals(triggerBody()?['CHAS'], utcnow ('yyyy-MM-dd'))

Send Email


Could you please confirm how I can filter the dates to today's date + 7 days?

I think you need to debug what will be the value of CHAS field.


Does it have the same format as utcnow('') ?


does CHAS value also return utc time ?


For adding date, you can use addseconds('2015-03-15T13:27:36Z', -20)

so you can try something like this. 

adddays(utcnow(), 7)



for your case, you can try something like this.

@greater(triggerBody()?['CHAS'], adddays(utcnow(),-7))


Here is more documentation.

The CHAS field format is Date only with UTC region London. The CHAS value is dd/mm/yyyy and was selected from the calendar picker. This must be correct as the modified item Flow correctly sends an email with the condition: @equals(triggerBody()?['CHAS'], utcnow ('yyyy-MM-dd')). However, if I use the same condition with the filter array I get multiple emails for every item.


I also tried @greater(triggerBody()?['CHAS'], adddays(utcnow(),-7)) but it failed with the following message:

The execution of template action 'Filter_array' failed: The evaluation of 'query' action 'where' expression '@greater(triggerBody()?['CHAS'], adddays(utcnow(),-7))' failed: 'The template language function 'greater' expects two parameter of matching types. The function was invoked with values of type 'Null' and 'String' that do not match.'.


Does CHAS field always contains a date? If your Sharepoint list contains registers with CHAS field not filled in, then probably Microsoft Flow applies NULL value. If so, a potential workaround is to use the function coalesce


Hope this helps

The list will contain at least one field with a renewal date, but some fields will be blank. How would I use the function coalesce in the current Filter array condition?

@greater(triggerBody()?['CHAS'], adddays(utcnow(),-7))


Acording to the documentation, coalesce "returns the first non-null object in the arguments passed in. Note: an empty string is not null. For example, if parameters 1 and 2 is not defined, this will return fallback: coalesce(parameters('parameter1'), parameters('parameter2') ,'fallback')"


So, if registers with blank field should not generate a notification, I guess you can try removing 


and using instead

coalesce(triggerBody()?['CHAS'], '2199-12-12')


Please also note the following thread reports problems when editing Conditions in Advanced Mode. However, coalesce-based advanced conditions used to work on May-June 2016 timeframe


Hope this helps

I replaced the filter type with coalesce(triggerBody()?['CHAS'], '2199-12-12') and it came back with the following error:


The 'inputs' of workflow run action 'Filter_array' of type 'Query' is not valid. Property 'where' must be a template language expression.


Also tried it on a condition without the filter and it came back with:


The template validation failed: 'The property 'expression' 'coalesce(triggerBody()?['CHAS'], '2199-12-12')' of template action 'Condition' at line '1' and column '927' is not a valid template language expression.'.


Sorry to hear that. This is the exact expression I tested successfully on May-June timeframe


@and(not(item()['Permanente']), lessOrEquals (coalesce(item()['Proximo_x0020_recordatorio'], item()['Fecha_x0020_ Vencimiento']),utcnow()))





How would you amend your expression to select today's date for the CHAS fields? And did you use the expression in the condition or filter array action?

Could you please confirm how I would use the expression for the CHAS date field? What parameters would I use? And do I add this to the advance Filter array?


@and(not(item()['CHAS']), lessOrEquals (coalesce(item()['???'], item()['???']),utcnow()))

Helpful resources

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

New Ideas Forum MPA.jpg

A new place to submit your Ideas for Power Automate

Announcing a new way to share your feedback with the Power Automate Team.

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

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

MPA Licensing.jpg

Ask your licensing questions at the Power Automate AMA!

Join Priya Kodukula and the licensing team, super users and MVPs to find answers to your questions on Power Automate licensing.

Users online (3,306)