SharePoint List: How to evaluate if a date has expired

[Eco_Graham]

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?

[kkurni]

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]

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?

[kkurni]

Try something similar to this.

Thanks

[kkurni]

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

But in the future it may look like this.

[EdRichardSRG]

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

Ed

[EdRichardSRG]

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

[Eco_Graham]

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

[kkurni]

@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

And it's result you can perform an action.

Thanks

[kkurni]

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

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?

[kkurni]

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]

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]

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

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]

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

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]

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

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]

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