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

Categories:

Building flows

I have the same question (0)

All responses (12)

Answers (0)

Sort by

PieterVeenstra 33 on at

Like
a
(0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Durrans 31 on at

Like
a
(0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
PieterVeenstra on at

Like
a
(0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Durrans 31 on at

Like
a
(0)

Report
Copy link

Link copied!

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?

Was this reply helpful? Yes No
PieterVeenstra 33 on at

Like
a
(0)

Report
Copy link

Link copied!

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

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

Was this reply helpful? Yes No
Durrans 31 on at

Like
a
(0)

Report
Copy link

Link copied!
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.

Was this reply helpful? Yes No
Durrans 31 on at

Like
a
(0)

Report
Copy link

Link copied!

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"
      ]
    }
  }
}

Was this reply helpful? Yes No
PieterVeenstra on at

Like
a
(0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Durrans 31 on at

Like
a
(0)

Report
Copy link

Link copied!

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"
  ]
}

Was this reply helpful? Yes No
PieterVeenstra 33 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @Durrans,

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

Was this reply helpful? Yes No