cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Eco_Graham
Level: Powered On

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?

 

24 REPLIES 24
Microsoft kkurni
Microsoft

Re: SharePoint List: How to evaluate if a date has expired

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.

 

Thanks

 

Eco_Graham
Level: Powered On

Re: SharePoint List: How to evaluate if a date has expired

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?

Microsoft kkurni
Microsoft

Re: SharePoint List: How to evaluate if a date has expired

foreach.png

 

Try something similar to this.

 

Thanks

Microsoft kkurni
Microsoft

Re: SharePoint List: How to evaluate if a date has expired

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

But in the future it may look like this.

EdRichardSRG
Level: Powered On

Re: SharePoint List: How to evaluate if a date has expired

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

 

Ed

EdRichardSRG
Level: Powered On

Re: SharePoint List: How to evaluate if a date has expired

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

Eco_Graham
Level: Powered On

Re: SharePoint List: How to evaluate if a date has expired

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

Microsoft kkurni
Microsoft

Re: SharePoint List: How to evaluate if a date has expired

@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

 

Thanks

Microsoft kkurni
Microsoft

Re: SharePoint List: How to evaluate if a date has expired

@EdRichardSRG @Equal is an expression.

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

 

Here is the docs https://aka.ms/logicexpressions for more information 

 

Thanks

 

Eco_Graham
Level: Powered On

Re: SharePoint List: How to evaluate if a date has expired

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?

Microsoft kkurni
Microsoft

Re: SharePoint List: How to evaluate if a date has expired

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.

https://msdn.microsoft.com/library/azure/mt643789.aspx

Eco_Graham
Level: Powered On

Re: SharePoint List: How to evaluate if a date has expired

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

efialtes
Level 8

Re: SharePoint List: How to evaluate if a date has expired

@Eco_Graham

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

 

https://msdn.microsoft.com/en-us/library/azure/mt643789.aspx

 

Hope this helps

Eco_Graham
Level: Powered On

Re: SharePoint List: How to evaluate if a date has expired

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

efialtes
Level 8

Re: SharePoint List: How to evaluate if a date has expired

@Eco_Graham

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 

triggerBody()?['CHAS']

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

 

https://powerusers.microsoft.com/t5/Flow-Forum/Flow-Conditions-Instantly-Fail-In-Advanced-Mode/m-p/5...

 

Hope this helps

Eco_Graham
Level: Powered On

Re: SharePoint List: How to evaluate if a date has expired

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

efialtes
Level 8

Re: SharePoint List: How to evaluate if a date has expired

@Eco_Graham

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

 

 

 

 

Eco_Graham
Level: Powered On

Re: SharePoint List: How to evaluate if a date has expired

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?

Eco_Graham
Level: Powered On

Re: SharePoint List: How to evaluate if a date has expired

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

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

firstImage

Incoming: New and improved badges!

We've given our badges an overhaul and also added some brand new ones!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Top Kudoed Authors (Last 30 Days)
Users online (4,500)