As a resource manager or project manager it can be a difficult task to keep up with operational resource planning. While Dynamics 365 Project Operations makes some of the tasks involved a bit easier to handle, it doesn’t automate everything. Communicating which project resources are booked for the week, for example, can be a time-consuming task often requiring writing several emails. I sought to create an elegant solution using power automate and I thought I would share what I came up with in the hopes that it helps someone out there.
Here is a high-level overview of the steps performed:
1. Triggered once weekly on Monday
2. Lists Bookable Resource Bookings from Bookable Resources with “start dates” from the current week.
3. For each Bookable Resource has a “Resource Type” of “User” the following conditions apply:
1. If “No” the flow is terminated
2. If “Yes”, Bookable Resource Bookings are retrieved
1. An HTML Table is created
2. The Office 365 User is retrieved
3. An E-Mail is sent to the address listed under the user profile
Step #1 – Trigger
For this example we will set the trigger to occur every week first thing monday morning.
Step #2 – Lists Bookable Resources
What we will first need to do is retrieve the Bookable Resources who have Bookable Resource Bookings for the current week. We will achieve this by creating an action that “Lists” Bookable Resources and applying a custom filter to ensure we only retrieve relevant records.
There will most likely be multiple resource bookings for a single user, therefore, we will need to make sure we are getting unique values for users. This will all be handled by the fetch-xml code I have prepared and provided below for your convenience:
<fetch top="50" distinct="true" >
<entity name="bookableresource" >
<link-entity name="bookableresourcebooking" from="resource" to="bookableresourceid" >
<filter>
<condition attribute="starttime" operator="this-week" valueof="endtime" />
</filter>
</link-entity>
</entity>
</fetch>
Step # 3 – List Bookable Resource Bookings
At this point we can apply some conditional logic in order to make sure we are only proceeding if the resources have the type “User”. This will avoid the use of any “generic” resources which could lead to errors in the process due to an email address not being found.
If this is the case, we will no retrieve all Bookable Resource Bookings for that Resource that fall within the coming week.
Here we will use another filter to only get the rows where the “Start Time” is equivalent to any date this week. I have prepared the filter below for you to use. Just copy and paste this into the “FIlter Rows” box under the action configuration settings.
(_resource_value eq @{items('Apply_to_each')?['bookableresourceid']} and (Microsoft.Dynamics.CRM.ThisWeek(PropertyName='starttime')))
Step #4 – Create an HTML Table
With the values returned from the list of Bookable Resource Bookings we can create a table to display them in an organized fashion for our email template later.
You may need to format the date/time values that are given out from CRM. Below are some examples of the expressions you could use:
*Formats the standard Project Operations “Duration” Field value which oddly returns everything in seconds.
div(item()?['duration'], 60)
*Formats the standard date and time values.
formatDateTime(item()?['starttime'], 'dd/MM/yyyy')
Step #5 – Search for Office 365 user-profile and send email
Now that we have our HTML Table with the values ready to go, we can find the email address of the user and send an email. Once the email address is defined, you will need to make sure you enter the output of the HTML Table into your email template.
As the email template design will be dependent on many factors within your organization I will leave this up to your own creativity.
Conclusion
Communicating the weekly resource allocation to your employees can sometimes be a hassle. Hopefully this flow can provide some time and effort saving for resource managers. Thanks for reading, if you have any feedback please feel free to write me or drop me a message on LinkedIn.
Cheers!