Tested on: PSA version 3.10.30.41
A recent customer ask was about creating a project team automatically when a Project was created in PSA. While creating a project team is usually a one-time effort at the beginning of a project, I do see value in project team templates in cases where teams are fairly static and they’re assigned on different projects. The ask sounded interesting so I wanted to see how it could be fulfilled.
Data model for Project Team Template
Before we get started, I want to give huge kudos to MVP Elaiza Benitez for her Powerthon Automate Saturday session on retrieving records from a N:N relationship. The Flow that I cover on this blog post is based on Elaiza’s session. Instead of re-writing the content of her session on this post, I’m pointing you to her session so that you get an understanding of how Flow is used to access data behind a N:N relationship.
Let’s look at the data model for a Project Team Template solution. The Project Team Template entity is a new custom entity that holds template records. As each Project Team Member on a Project needs a role (Bookable Resource Category), it’s possible to set a default role for a template record. This way all Project Team Members automatically get a role that is defined on a template.
Bookable Resources can be on multiple different templates. A template is built by adding Bookable Resources in a subgrid on a template record. A “native” N:N relationship is enough for a Project Template solution to work. If you want to create a N:N relationship the “manual way” to fulfill more versatile needs, you can definitely do that. To read more about a “natively” vs. a “manually” created N:N relationship, read this blog post by Carl de Souza.
Let’s look at the bits and pieces of the solution before we dissect the Flow that creates Project Team Members from a Project Team Template. A Project has a lookup to a Project Team Template. When a template is selected, Project Team Members for the Project are created.
Bookable Resources are added to a Project Team Template. A default role will define the role that all added Project Team Members will have. Roles can be changed manually when a project team has been created. An alternative solution would be to use a Bookable Resource’s default role on the project team. This example doesn’t cover that approach.
The Project Team Template Flow
Let’s dissect the Project Team Template Flow. It’s is fired off when a Project is created and when the Project Team Template lookup contains a value. Next we list all Project Team Members related to the crated Project. Only a single record, the Project Manager of the Project, is returned. Why is this? The reason is that when a Project is created, the user creating the record is automatically assigned as a Project Manager for the Project.
With the Get Record actions we can access the necessary Project Team Member, Bookable Resource, Project Team Template and Bookable Resource Category (Role) records.
- Get a Project Team Member record: Gets us access to the Bookable Resource record in the next action.
- Get the Bookable Resource from PTM record: Bookable Resource related to PTM record. This is needed later to check if the same Bookable Resource is also on a Project Team Template record.
- Get a Project Team Template record: Template related to the Project in question. Used in a FetchXML query later on. It also gets us access to all Bookable Resources added on the template.
- Get a Bookable Resource Category record: Default Role defined on the Project Team Template record. The returned role is given to all Project Team Members created from the template.
The next part of the Flow includes details from Elaiza Benitez’s session on retrieving records from a N:N relationship. To get our hands on all the Bookable Resources on the retrieved Project Team Template record, we have to query data using the Web API. The entity that is used in the query is bookableresources and a bit of FetchXML is needed to get our hands on all desired Bookable Resource records. If you watch Elaiza’s session you will see she has used Advanced Find to build her query. I did the same for mine. If you prefer to use FetchXML Builder, that’ll work fine as well.
The Apply to each is something we can’t avoid in this Flow. We need to loop through all returned records and then get their GUID. The values that I’ve used for the Apply to each action and the compose that follows it are:
body('Invoke_an_HTTP_request_to_retrieve_Bookable_Resources')?['value']
items('Apply_to_each_retrieved_Bookable_Resource')?['bookableresourceid']After the compose, Get Records actions are used to get the details of the Bookable Resource record being looped through as well as the Organizational Unit related to the Bookable Resource. A condition is used to check if the Bookable Resource being looped through matches the Project Manager of the Project in question. If it does, no action will be taken. If doesn’t, a new Project Team Member record will be created and it will be associated to the Bookable Resource record being looped through.
