Make Project For The Web Bigger: Automate and integrate with Flow and Teams

Project for the web data is stored in entities within Dynamics 365 Common Data Services (CDS).

With data in CDS, you can customize these entities, build new entities, create relationships as per your business needs. Also, you can extend its benefits by building apps in Power Apps, automate tasks in Power Automate (earlier called as Microsoft Flow).

In this blog, I will use CDS, Power Automate and Teams to notify team members when their tasks is late at regular intervals like the one below

This is product research project created in project for the web. Notice, the first task Research Product is late. Finish date is colored in RED.

Let’s start with the Project for the web CDS schema to know the entities and relationships. To get started, download Project for the web Power BI template (download here), connect to CDS and look at the model view. This is most simplified version of CDS as it gives complete information of the entities. Though these tables are merged and have calculated values, this will help you to get started. Thanks Microsoft for this template.

You can notice the relationship between these entities are 1-N or N-1 or N-N. For instance, the 1 .. * relationship between Projects and Project Tasks indicates 1 project can have many tasks. Knowing the relationships between these entities is important as it helps to navigate from one to another and filter.

Red-colored tables will be focused to find the due tasks and to whom it has been assigned to.

A high level construct of the Flow before you begin

Let’s start a quick brief with high level steps and entities that will help us.

  1. List all incomplete tasks and its project name with end date less than today’s date | Entity used: msdyn_projecttasks, msdyn_projects
  2. For each task selected
    1. Get the resources assigned to the current task | Entity used: msdyn_resourceassignments
    2. For each assigned resource
      • Get the email address of the resource | Entity used: systemusers, bookableresources
      • Post an adaptive card that task is due to the Team user

In Power Automate, create a scheduled flow. Configure the parameters including starting date, time and pattern.

Next, add the initialize action to initialize a String variable, say Now, with current date and time.

Insert list records action of CDS. In this action, you will get late tasks. Focus on the below parameters

  1. Entity name set to Project Tasks (note this is display name, the real name of this entity is msdyn_projecttasks).
  2. Filter query set to statuscode eq 1 and msdyn_progress lt 1 and msdyn_scheduledend le $variable$. Field statuscode =1 indicates active task. % Complete is stored in field msdyn_progress. 1 indicates 100%. Field msdyn_scheduledend is the calculated finish date of the task.
  3. Expand query set to msdyn_project($select=msdyn_subject). This is interesting parameter. This retrieves the name of the project (stored in field msdyn_subject) related to the late tasks

Insert the apply to each control action to iterate the list of due tasks. When notifying the user, the project name is needed. Remember the list late tasks returns a JSON content. This response includes project name of each task. This is not a property of Project task entity and hence it is not listed in the Dynamic content panel. So, this project name needs to parsed from JSON content.

A task can be assigned to multiple resources. So, to get all resources assigned to the due task insert list records action of CDS. Focus on two parameters:

  1. Entity name set to Resource Assignments (note this is display name, the real name of this entity is msdyn_resourceassignments)
  2. Filter query set to _msdyn_taskid_value eq @{items(‘Apply_to_each’)?[‘msdyn_projecttaskid’]}. Interesting part of this filter is its syntax. Field msdyn_taskid is a lookup field to task table. So, the syntax is _LookupField_Value

Let’s loop through the assignments and get the email address. Email address is needed to notify the resource in Teams. This is bit tricky. Email address is in systemusers entity, and this entity is linked to Resource assignments entity through a bookable resource entity (a kind of bridging table).

Insert Get Record action of CDS to get details of the bookable resource associated with the current assignment. Then, insert another Get Record action of CDS to get the details of the system user associated with the user id.

With all details available, post a JSON message to Team user. Insert Post your own adaptive card as the Flow bot to user action. I used to design the card, and copied the JSON card here. This is fun!

Here is animated version of how flow notifies the Team user.


One thought on “Make Project For The Web Bigger: Automate and integrate with Flow and Teams”

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s