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

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!

 

Thanks,

Natasha

12 REPLIES 12
Super User
Super User

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

Hi Natasha,

 

Welcome to Flow!

 

Why don't you use a filter on the Get_Items

 

GetItem.PNG

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

Durrans
Level: Powered On

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

Hi,

 

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.

Super User
Super User

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

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

 

 

Durrans
Level: Powered On

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

Hi,

 

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?

Super User
Super User

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

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

 

addDays(utcNow(),-7,'dd/MM/yyyy')
 
 WeekAgo.PNG
 
 

 

 

Durrans
Level: Powered On

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

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.

Durrans
Level: Powered On

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

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": {
    "code"502,
    "source""europe-001.azure-apim.net",
    "clientRequestId""de3770c3-951d-49a4-89d0-2046455d2baf",
    "message""BadGateway",
    "innerError": {
      "status"502,
      "message""String was not recognized as a valid DateTime.\r\nclientRequestId: de3770c3-951d-49a4-89d0-2046455d2baf\r\nserviceRequestId: 4c1d6f9e-50fd-5000-be2d-6d83715de1af",
      "source""https://jamesdurransmail1.sharepoint.com/hub/penistone/H&S%20Hub/_api/SP.APIHubConnector.GetListItems(listName='Contractor%20Information%20Point',queryOptions=@q)?@q='%2524filter%3dDate_x0020_of_x0020_Review%2520ne%2520(%2527%2527)'",
      "errors": [
        "-1",
        "System.FormatException"
      ]
    }
  }
}

Super User
Super User

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

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

Durrans
Level: Powered On

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

Hi,

 

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",
  "status"400,
  "source""https://jamesdurransmail1.sharepoint.com/hub/penistone/H&S%20Hub/_api/SP.APIHubConnector.GetListItems(listName='Contractor%20Information%20Point',queryOptions=@q)?@q='%2524filter%3dDate_x0020_of_x0020_Review%2520eq%2520variables(%2527AlertDate%2527)'",
  "errors": [
    "-1",
    "Microsoft.SharePoint.SPException"
  ]
}

Super User
Super User

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

Hi @Durrans,

 

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

Community Support Team
Community Support Team

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

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:

 

@empty(items('Apply_to_each')?['Date_x0020_of_x0020_Review'])

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

 

8.JPG

The flow works successfully as below:9.JPG

 

 

 

Best regards,

Kris

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.
Durrans
Level: Powered On

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

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

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
Users Online
Currently online: 84 members 4,860 guests
Recent signins:
Please welcome our newest community members: