A couple of weeks back I posted how to create a project request process for Office 365 Project Online using Microsoft Forms, Microsoft Flow, now known as Power Automate, and SharePoint, for those that missed that post, it can be found here: https://pwmather.wordpress.com/2019/10/23/office365-projectonline-project-request-process-using-microsoftforms-and-microsoftflow-projectmanagement-ppm-msproject-sharepoint-powerplatform/. Since the official release of the new Microsoft Project application, known as Project for the Web, I’ve built a similar example that uses Microsoft Forms, Power Automate and the Power Platform Common Data Service (CDS). For those of you that haven’t seen my posts on the new Project for the Web service, here is a link: https://pwmather.wordpress.com/tag/project-for-the-web/.
The components required for this project request process are Microsoft Forms, Power Automate, the CDS and Project for the Web. Firstly for the entry point to initiate the project request – I’ve used a simple Form as seen below but this could also just as easily have been a Power App.
As with the Project Online example, we could ask whatever details we need to support the project request process but this is just a simple example. The department question is key for this example as that is used to control who approves this project request. I’ve only got two options here to keep it simple but you will see later on in the post how / where to add to this. The next component is the CDS Entity. I’ve created this new Project Requests entity in the default org:
I’ve filtered the fields above to show the custom fields I’ve created in this new Project Requests entity. These are summarised below:
- Project Name – used to store the Project Name from the request form
- Description – used to store the Project Description from the request form
- Project Department – used to store the project department from the request form
- Project Justification – used to store the project justification from the request form
- Reviewed By – used to store the Flow Approval reviewer
- Review Status – used to store Flow Approval status
- Reviewer Comments – used to store the Flow Approval reviewer comments
- Project Requested By – used to store the email address of the user who completed the request form
- Project ID – used to store the Project ID from Project for the Web for project that gets created by the Flow
- Project ID Linked – links to the project in the Project entity for project that gets created by the Flow
To support this process, I’ve also added the “Project Department” field to the Project entity as an option set – add the option values as required.
Options:
The next component is the Power Automate Flow, this is used to automate the process from receiving the project request form response, sending out the approval and creating / updating the CDS entities accordingly. The Power Automate Flow can be seen below with one of the switch cases expanded:
The Flow is covered in more detail below. The final component required is Project for the Web.
This Flow is triggered when a new form response is submitted to the Project Request form. The first action is to initialize a Project Manager variable that is set later on. It then uses an Apply to each loop with a list of the response notifications passed in from the trigger. The first step inside the Apply to each action is the Get response details Form action with the form response ID passed in.
The next action inside the loop is the Get user profile action, here we pass in the Project Request responders email address then the Flow uses the List records CDS action to query the Users entity, a filter is added as we pass in the users ID from the Office 365 Get user profile action. Here it is assumed that the user who completed the Form response is a user in the CDS Users entity. We then set the Project Manager variable inside a 2nd Apply to each loop. Whilst our List records example will only return one result, the List records action could return multiple results so Flow automatically adds the Apply to each. The input for the Apply to each 2 action is the output from the List records actions. The value used to set the ProjectManager variable is in this example is “items(‘Apply_to_each_2’)?[‘systemuserid’] but just pick “User” from the List records dynamic content:
The Flow then uses a Switch action to have different paths based on the department value, the “Which department is the project for?” form answer is used to switch on:
The Flow then has different cases in the Switch action, one for each of the possible values for the “Which department is the project for?” question. I’ve just got 2 cases in this example but this is where you would expand this for more departments:
Expanding one of the cases to see the details and I will point out the differences between each case:
As you can see, the first action is a Flow Approval that is set to “Approve/Reject – First to respond”. I’ve set the Title to be “Marketing Project Request”, update this for other departments as need. I’ve assigned this to the admin account but this where you would assign it to the user who approves the marketing projects. In the details section I’ve added the Project Request form responses for the “Get responses details” action to give the reviewer the details needed. The Condition check action is used to check the approval outcome. Then for requests that were approved the Flow uses another loop. The loop is required as the default Approval action can have more than one response in some cases but in this case it will only have one. Inside the loop there are 3 CDS actions:
Firstly to create a new record in the Project Request entity by passing in the form response value and the approval details:
The second CDS action in this loop is to create the project in the Project entity, here the Calendar Id is required but this is set by the service, here I just put a 1 in there as a value is required in the action, the Name is passed in from the Form value. The Contracting Unit is the Organizational Unit ID found in the Organizational Unit entity, Project Manager is set with the Project Manager variable. The Work hour template is set to the correct Work Template ID found in the Work template entity. The final field set in this example is the Project Department value, here we set Marketing but set this based on the correct Project Department:
The final CDS action in the loop is to update the original Project Request record in the CDS with the newly created Project identifier Project record:
If the project is rejected, in this example a record is created in the Project Request entity only but typically you would also send an email etc. to notify the requester. This action is similar to the first action in the approved side but the Review Status is set to Rejected:
That’s it, a very simple Power Automate, Microsoft Forms and Power Platform CDS project request process for Project for the Web. I will post a short video on my YouTube channel demoing this in action later this week along with a video for the Project Online version too: https://www.youtube.com/channel/UC_b_pa1ADKlUqIpLK9AmR1g?sub_confirmation=1
Modern Work Management - Project and the Power Platform blog 