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?
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?
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
And it's result you can perform an action.
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'))
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.
for your case, you can try something like this.
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?
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
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()))
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()))
We've updated and improved the layout and uploading format of the Power Automate Cookbook!
Fill out a quick form to claim your user group badge now!
We've given our badges an overhaul and also added some brand new ones!
Learn how to build the business apps that you need.
Find out where you can attend!
Watch & learn from the Power Automate Community Video Gallery!