Email a Summary of Project Online Tasks
Introduction
Flow includes a list of actions for Project Online which include getting a list of projects and getting a list of tasks. This post will work fetching the late and upcoming tasks for one project and then creating the email to send. The trigger for this example will be a scheduled trigger once a week on a Monday morning. This post is split into 3 parts, getting Project ID, Listing the tasks and Sending the email. Using the same structure Flow will be split into three scopes.
The final email will look like this.
Getting Project ID
The Project Online connection includes an action of List Tasks. This action requires 2 parameters, URL of the project site and the Project ID. Unlike some other actions in flow when you enter the URL you do not get a drop down of the available projects, we need to get project id first.
We know the project name so we can fetch the list of projects and then use the name to filter the returned array. List projects action does not include an advanced features so we need to use a Filter Array action.
So before the List Tasks action can be added, I add a scope to the flow and add a List Projects action and then add a Filter Array action to filter it down to the Returned projects Project Name equalling "Training Plan".Then we use a compose action to get the Id field from the first row in the filtered array. So I add a compose action and click on expression and add the following
first(body('Filter_array'))?['Id']
Then I run the flow to check that the compose action does return a project id. The compose action should return an Id. Then I am ready to use that Id to get a list of tasks.
Fetching Tasks from Project Online
For this post I want to list Late tasks and tasks due in the next 7 days. So this will not include Summary tasks and tasks that have been completed. This means that in the List Tasks action we can use an OData filter to not include them.
I start by adding a new Scope action and rename it to Get Task Lists. I add List Tasks from the Project Online connector and use the output from the Compose action
Then I expand the Advanced options to add an OData filter which is:
IsSummary eq false and PercentComplete lt 100
In the email I want to lists of tasks, late ones and upcoming ones, so we do two filter arrays. The first filter is a simple one of the finish date being less than today so can done using the simple editor.
The scond filter array needs the Finish date to be greater than or equal to today and less than today plus 7 days. This will require the filter to edited in advanced mode. So add the Filter Array action and click Edit in advanced mode. The formula is
@and(greaterOrEquals(item()?['Finish'], utcNow()),less(item()?['Finish'], addDays(utcNow(), 7)))
Compiling Email
In order to create the email I will convert the two filtered arrays into html tables and then add headings before each table. I add the final scope to create the email. The two tables are created using the same action, from is the body from the filter array actions and I've included headings and columns are Custom. Also in order to make the due date to be short I've used the substring function to just return 10 characters of the date string, e.g. 2019-07-15.
Once the two html tables are created, they can then be used inside a Create Email action with headings. We need to expand the advanced options to select for the email to be html.The flow is now complete, and will now send emails weekly on late tasks.
Conclusion
The list of actions available to Project Online connection is limited so we cannot limit to set assignments but using filter actions a summary can be built.
Thanks for reaading!
Comments
-
Email a Summary of Project Online Tasks
Hi
I am loving the fact you are posting flows for Project Online, so good to see the possibilities! Keep them coming!
*This post is locked for comments