Make Project For The Web Smarter: Auto delete zero duration projects


Project for the web is simple, and this simplicity also creates problems 🙂

In my earlier blog, I discussed about notifying team members of their late tasks. As I started working on the new Project, I observed it is very easy to create a project. If I grant Project Plan 1 / 3 / 5 subscription to users, they may end up creating projects without tasks.

It is important to periodically review all projects and delete zero duration projects. Otherwise, the reports generated either through Power BI or other tools will present wrong information. (Do you know about Project for the web Power BI Template)

Advantage of the new Project is all project data is stored in CDS. I can automate this cleanup process through Power Automate. Yet, I don’t want to risk deleting zero duration projects that might created for future need. So, how about flow asking the project manager for approval through Teams and on approval delete the project.

Let’s start with Project subscription: Do I need a Project subscription to build or run Flows that access Project for the web data?

Yes. To build or run Flows that access Project for the web data you must have a Project Plan 3 (formerly Project Online Professional) or Project Plan 5 (formerly Project Online Premium) subscription. You also need to be a licensed user of Microsoft Flow with a subscription that gives you rights to build the Flows you need. For more information on Microsoft Flow subscriptions see Microsoft Flow Pricing. (Source: https://support.office.com/en-us/article/customizing-project-for-the-web-ddd7ecd3-5c33-4f40-bc55-f8a149d8c55f )

Start by creating a scheduled flow. The below flow will run on Saturday every week.

High level steps of this flow is given below.

The first step is to get all Projects with zero duration. Project details are stored in the entity Project (real name: msdyn_project). Duration is stored in field msdyn_duration. The datatype of msdyn_duration is decimal number. Use List records action of CDS.

Now look at details of two projects – one with zero duration and other with non-zero duration.

Notice zero duration project have duration value empty (this is null). So, in the flow check if the duration is null (not zero).

Project manager field of Project entity has many to one relationship with User entity. Let’s use this relationship to get the email address of the project manager in Expand Query parameter of List records action

This step lists projects and email address of project manager with null duration and active status

Now, let’s iterate the list of selected projects. Selected projects are returned as JSON content. This includes the project manager’s email address. Now, let’s use Parse JSON in Data operations to first get the project manager property, and next get the primary email address (this is stored in field internalemailaddress.

We need to send an adaptive card to the project manager to approve or reject the deletion request. This requires an automated approval process. Good news is Power Automate can manage the approval process.

To create an approval workflow, you can use the approval actions in Power Automate. Use create an approval action of Approvals connector

1. Approve/Reject – First to respond. I have only one project manager per project. So, this is good
2. This is the title displayed of the card when displayed in Microsoft Teams
3. This is the primary email address of the project manager
4. Use standard markup to include the link label and link to Project (see more)
5. Enable notification = Yes sends the email to approver. Email notification = No, no mail sent
6 . Enable reassignment = No doesn’t give option to reassign.

In the above picture, notice (5). Enable notification should be sent to No. As we don’t want email to be sent to approver, rather notify the approver in Teams. Good news is Create an approval action returns an adaptive card for approval, and the approval id to track approval status.

When the create an approve action runs, an approval request is sent to the project manager. Project manager can see this approval in Power Automate.

Project manager can go to Power Automate and see the approvals. Yet, we don’t want manager to switch between too many apps. So, lets send an adaptive card using Insert Post your own adaptive card as Flow bot to user action. This action needs the adaptive card and primary email address.

This action will display an adaptive card like the one below. Notice the title and details in this card.

When the project manager clicks approve or reject in this adaptive card, this updates the approval status. You might wonder how is this done. A sample Adaptive card generated by create an approval action

{  
   "type":"AdaptiveCard",
   "version":"1.0",
   "body":[  
      {  
         "type":"Image",
         "url":"https://aka.ms/approvals-logo"
      },
      {  
         "type":"TextBlock",
         "size":"large",
         "weight":"bolder",
         "text":"Do you want to delete Zero duration project?"
      },
      {  
         "type":"TextBlock",
         "text":"Requested by Sai Prasad B <saiprasad@october072019.onmicrosoft.com>",
         "wrap":true
      },
      {  
         "type":"FactSet",
         "facts":[  
            {  
               "title":"Date Created",
               "value":"2019-11-16T05:35:25Z"
            }
         ]
      }
   ],
   "actions":[  
      {  
         "type":"Action.ShowCard",
         "card":{  
            "type":"AdaptiveCard",
            "version":"1.0",
            "body":[  
               {  
                  "type":"TextBlock",
                  "text":"Comments",
                  "wrap":true
               },
               {  
                  "type":"Input.Text",
                  "id":"comments1",
                  "placeholder":"Enter comments",
                  "isMultiline":true,
                  "maxLength":1000
               }
            ],
            "actions":[  
               {  
                  "type":"Action.Submit",
                  "data":{  
                     "Environment":"Default-ee51c033-a7ec-44a0-af67-bbac529403c8",
                     "ApprovalTitle":"Do you want to delete Zero duration project?",
                     "ApprovalLink":"https://flow.microsoft.com/manage/environments/Default-ee51c033-a7ec-44a0-af67-bbac529403c8/approvals/received/e239874d-2361-4d71-9e90-d28b155d3f11",
                     "ApprovalName":"e239874d-2361-4d71-9e90-d28b155d3f11",
                     "OnBehalfOfNotice":"Requested by Sai Prasad B <saiprasad@october072019.onmicrosoft.com>",
                     "CreatorName":"Sai Prasad B",
                     "CreatorEmail":"saiprasad@october072019.onmicrosoft.com",
                     "CreationTime":"\"2019-11-16T05:35:25Z\"",
                     "MessageTitle":"Do you want to delete Zero duration project?",
                     "Options":[  
                        "Approve",
                        "Reject"
                     ],
                     "SelectedOption":"Approve",
                     "ActionType":1
                  },
                  "title":"Submit"
               }
            ]
         },
         "title":"Approve"
      },
      {  
         "type":"Action.ShowCard",
         "card":{  
            "type":"AdaptiveCard",
            "version":"1.0",
            "body":[  
               {  
                  "type":"TextBlock",
                  "text":"Comments",
                  "wrap":true
               },
               {  
                  "type":"Input.Text",
                  "id":"comments2",
                  "placeholder":"Enter comments",
                  "isMultiline":true,
                  "maxLength":1000
               }
            ],
            "actions":[  
               {  
                  "type":"Action.Submit",
                  "data":{  
                     "Environment":"Default-ee51c033-a7ec-44a0-af67-bbac529403c8",
                     "ApprovalTitle":"Do you want to delete Zero duration project?",
                     "ApprovalLink":"https://flow.microsoft.com/manage/environments/Default-ee51c033-a7ec-44a0-af67-bbac529403c8/approvals/received/e239874d-2361-4d71-9e90-d28b155d3f11",
                     "ApprovalName":"e239874d-2361-4d71-9e90-d28b155d3f11",
                     "OnBehalfOfNotice":"Requested by Sai Prasad B <saiprasad@october072019.onmicrosoft.com>",
                     "CreatorName":"Sai Prasad B",
                     "CreatorEmail":"saiprasad@october072019.onmicrosoft.com",
                     "CreationTime":"\"2019-11-16T05:35:25Z\"",
                     "MessageTitle":"Do you want to delete Zero duration project?",
                     "Options":[  
                        "Approve",
                        "Reject"
                     ],
                     "SelectedOption":"Reject",
                     "ActionType":1
                  },
                  "title":"Submit"
               }
            ]
         },
         "title":"Reject"
      }
   ]
}

Block 31 to 75 is for approve, 76 to 120 is for reject. ApprovalLink properties in lines 56 and 101 is a deep link to the flow with approval id. In the above JSON content, approval id generated by Create an approval action was e239874d-2361-4d71-9e90-d28b155d3f11. This approval id and the environment id is used to construct this link.

https://flow.microsoft.com/manage/environments/<CDS enviroment id>/approvals/received/<approval id>

Now, the flow should wait for approval status. Insert Wait for an approval action with the approval id.

The above action will get executed once the manager approves or rejects the request.

Now, if the outcome of Wait for an approval action is Approve then delete that project.

Here is animated version of complete workflow process of deleting zero duration projects

Leave a Reply

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

WordPress.com Logo

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

Google photo

You are commenting using your Google 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