Unanswered

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

(0) Share

Report

Posted on by Durrans

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

All responses (12)

Answers (0)

Durrans 31 on at

Like (0)

Report

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.
v-xida-msft on at

Like (0)

Report
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:

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:

The flow works successfully as below:

Best regards,

Kris
PieterVeenstra 2 on at

Like (0)

Report

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.
Durrans 31 on at

Like (0)

Report

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"
  ]
}
PieterVeenstra on at

Like (0)

Report

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 31 on at

Like (0)

Report

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"
      ]
    }
  }
}
Durrans 31 on at

Like (0)

Report
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.
PieterVeenstra 2 on at

Like (0)

Report

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')
Durrans 31 on at

Like (0)

Report

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?
PieterVeenstra on at

Like (0)

Report

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