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:
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!
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.
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?
Okay, I have changed a few things around - new steps are in bold:
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?
"message": "String was not recognized as a valid DateTime.\r\nclientRequestId: de3770c3-951d-49a4-89d0-2046455d2baf\r\nserviceRequestId: 4c1d6f9e-50fd-5000-be2d-6d83715de1af",
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",
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:
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.
The flow works successfully as below:
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.