Recently had a requirement where alerts to a vendor need sending for orders expected in the next two weeks. A reminder is needed as the lead times for the goods are so long.
The goal is therefore, have an email sent to a vendors email address for purchase order lines in the next two weeks from today’s date.
To achieve this Power Automate or Logic Apps are the most appropriate choices. For this blog I have chosen Power Automate.
Given that I will need line data and I want to minimise the need to make multiple HTTP calls so some small DEV BC side will help. I have produced a query object so that I have all the data I require for all the areas I want data from (link to my code is at the end). I’ve chosen header level here for the “Expected Receipt Date” but you could do it for the line level if needed:
Ensure the query is published as a web service and check you have output by using the web link or from an API test tool like Postman:
To get the exact data we want for the scenario some ODATA query logic is needed. Add the following to the end of the web service URL. Replace <Your Date> with the date you want to filter by. The format must be like this: 2020-07-21T00:00:00Z:
?$filter=Expected_Receipt_Date ge (<Your Date>) and Expected_Receipt_Date le (<Your Date>) and Outstanding_Quantity gt 0
The date values will be replaced with calculated fields in the flow which will be a scheduled type flow so that is our starting point along with the ability to create variables for the date/time values which are then used in our ODATA query above:
Use the HTTP connector and call the web service with a GET command and use the current time and future time variables.
The HTTP will produce JSON which we saw earlier, from the web service call, and to use the data from this we need to use the PARSE JSON feature and we’ll be able to then select the content for use in subsequent steps like sending the email. To generate the schema just paste in an example from calling the web service. Works nicely by using an API test tool like Postman as you get it formatted in a nicer structure.
Once the JSON is read by the PARSE JSON step you are able to create a “Apply to each” step which will iterate through each of the received rows of data until it has read them all. Whilst that is happening we can initiate further actions like our email:
Each reference comes from the PARSE JSON and you can use format functions on certain data like I have for the date. The result of this flow is an email much like this one: