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

Flow that sends an email 7 days before expiry date of a document

Hi everyone,


I am new to Microsoft Flow and I am trying to build a flow that will send an email alert to me 7 days before a document reaches its expiry date. I have the following steps so far:


  • Trigger Recurrence - 1 day
  • Compose addDays(utcNow(),7,'dd/MM/yyyy')
  • Get items
  • Apply to each
    • Compose formatDateTime(items('Apply_to_each')?['Date_x0020_of_x0020_Review'],'dd/MM/yyyy')
    • Conditon @equals(outputs('Today''s_date_+7_days'), outputs('Expiry_date'))
      • If yes - Send an email

I get the following error:



I have heard the date format can be a problem so I have tried formatting the date as dd/MM/yyyy, dd-MM-yyyy, d/M/yyyy and yyyy/MM/dd but with no luck so far.


I have also tried removing the second Compose step completely and instead putting the Expiry Date straight into the Condition step, see below:



When I do this the flow does run, but the Condition step returns the expression result 'false', even though I have test documents in the library with the date set to 7 days ahead of today's date.


Help much appreciated!





Hi Natasha,


Welcome to Flow!


Why don't you use a filter on the Get_Items



That way you can only get the items that needs to generate yor alerts.



The error message that you are sseing is because the Date is empty for some of your documents



Thanks for the quick reply!


Can you please advise on what I should write in the Filter Query box?


I've tried Date_x0020_of_x0020_Review ne 'Null' but the Apply to Each step failed to run this time and I got the following error:


ActionConditionFailed. The execution of template action 'Apply_to_each' is skipped: the 'runAfter' condition for action 'Get_items' is not satisfied. Expected status values 'Succeeded' and actual value 'Failed'.


The Get Items step also encountered a 'BadGateway' error.

HI Natasha,

I would go for a differnt approch on that filter.


First set a variable to the date that you want to alert on. 


Then the filer could be 


Date_x0020_of_x0020_Review  -eq '01/06/2018'


With this filter the Get items will only return the docuemnts that you are interested in.


So in the above the '01/06/2018' actuall shoudl come from a variable


So you could use variables('MyDateVariable')





I need this flow to run by itself every day to keep me up to date with expiring documents so I'm not sure I can include any one date in that filter?

That date variable is somehtign that you could still dynamically build up.





Okay, I have changed a few things around - new steps are in bold:


  • Trigger Recurrence - 1 day
  • Initialise variable addDays(utcNow(),7,'dd/MM/yyyy')
  • Get items - Filter query Date_x0020_of_x0020_Review ne ('')
  • Apply to each
    • Conditon @equals(variables('Today''s date +7 days'), items('Apply_to_each')?['Date_x0020_of_x0020_Review'])
      • If yes - Send an email

I can see from the flow run that the variable has been calculated as expected (value is 15/06/2018) but then the flow crashes on the Get items step. I get a BadGateway error 4 times before the flow finally fails.

Additionally the following error is present in the output body for the Get items step when I look at the flow run but I'm not sure what it means?


  "error": {
    "innerError": {
      "message""String was not recognized as a valid DateTime.\r\nclientRequestId: de3770c3-951d-49a4-89d0-2046455d2baf\r\nserviceRequestId: 4c1d6f9e-50fd-5000-be2d-6d83715de1af",
      "errors": [

Trigger Recurrence - 1 day
Initialise variable alertdate to addDays(utcNow(),7,'dd/MM/yyyy')
Get items - Filter query Date_x0020_of_x0020_Review eq variables('alertdate')
Apply to each
    Send an email



I just changed my flow to this and now I am getting 'BadRequest' with the following output body:


  "message""The query is not valid.\r\nclientRequestId: 21ebfaa6-b75a-41a0-834f-c3552ae1fcfe\r\nserviceRequestId: b91e6f9e-502f-5000-c92a-4cb1009367da",
  "errors": [

Hi @Durrans,


You will need to add the varibales(....) bit through the expression editor.

Community Support
Community Support

Hi @Durrans,


Could you please share a full screenshot of your flow's configuration?

Further, could you please share a bit more about your SP library?


The error message told that the value of Date of Review column of some records within your SP library is empty. I agree with @Pieter_Veenstra's thought almost, you should check if the corresponding Date of Revire column of a document record is empty firstly.


I have created a SP library on my side and the data structure of it as below:10.JPG

Note: The Date of Review column represents the expiry date of the document.



I have made a test on my side and don't have the issue that you mentioned. Please take a try with the following workaround:

  • Add a Recurrence trigger, Interval set to 1 and Frequency set to Day.
  • Add a "Get files (properties only)" action, specify Site Address and Library Name.
  • Add a "Apply to each" action, input parameter set to output of the "Get files (properties only)" action.
  • Within "Apply to each" action, add a Condition, click "Edit in advanced mode", type the following formula:



Within "If/no" branch of Condition, add a "Condition 2" action, click "Edit in advanced mode", type the following formula:



@equals(addDays(utcNow(), 7, 'MM/dd/yyyy'), formatDateTime(items('Apply_to_each')?['Date_x0020_of_x0020_Review'], 'MM/dd/yyyy'))

Within "If/yes" branch of Condition 2, add a "Send an email" action.



Image reference:7.JPG



The flow works successfully as below:9.JPG




Best regards,


Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hello @v-xida-msft,


I have amended my flow from your advice and now have the following structure:



I ran the flow and got the following error:



The Contractor Information Point document library has the following test documents in it:



The documents that I want email alerts for are held in the Contractor RAMS and Insurance folder. On the Get files step I can't narrow down any more than just site and library, so I am assuming that selecting the library will also search for files in the Contractor RAMS and Insurance folder in that library?


For the sake of testing the flow I have added test documents to the main library. I expected to receive an email alert for the first PDF.

Helpful resources

MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!


Experience what’s next for Power Virtual Agents

See the latest Power Automate innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Power Platform ISV STudio

Power Platform ISV Studio

ISV Studio is the go-to Power Platform destination for ISV’s to monitor & manage applications post-AppSource publish.

Top Solution Authors
Top Kudoed Authors
Users online (47,024)