Tested on Project Operations on Dataverse version 4.23.0.7 (2021 wave 2 EA)
A frequent ask in Dynamics 365 Project Operations implementations is a weekly reminder that is sent to resources and their managers, when a resource’s weekly time entries fall below a defined minimum. A weekly minimum is usually equivalent to the number of hours a resource works during a regular work week. In my country, employees typically work 37,5 hours a week in IT consulting. In some countries a work week is 40 hours and in others it’s something else. There are many variations depending on countries, cultures, collective agreements and so on.
Weekly reminders is exactly the kind of customization that is a typical example of how PSA and ProjOps are extended in real-world scenarios. I’ve previously written about creating a weekly reminder that looks at unsubmitted time entries so this post is sort of a continuation of the topic. My older post can be found here. Before we look at how we can send out a weekly reminder to resources and managers, let’s define the use cases:
- A project resource should receive a weekly notification as a reminder to submit hours for their whole week, when the sum of their time entries for a work week is less than x.
- Managers should receive a list of submitted vs. missing time entries for their direct reports once a week.
A work week can naturally be different between different resources. Mary might work 37,5 hours a week but John might only work 30 hours. While resource work hours are stored in the calendar table and their calendar rules, querying that table is far from straightforward. For this example, I’ve simply created a custom column on the Bookable Resource (later BR) table, and that holds a value for weekly time entries. If you absolutely must use the calendar table, bring your requirement to your friendly developer, and wish them a happy headache.
By now you may have guessed that the approach I’m taking will be based on a cloud flow i.e. Power Automate. This first version that I’m writing about in this post is built with a few nested apply to each loops and variables. If I have the time and energy, I might compare performance differences at some point by creating a version that uses Pieter’s method. Thanks to Elaiza for reminding me with her brilliant post! A loopless option would be to use FetchXML – let’s see if I end up trying that approach. For now, let’s dissect the version I have ready!
Sending weekly time entry reminders with Power Automate
The first thing that needs to be decided is the time frame from which work hours are summed up. In this example the recurring flow runs every Sunday and looks at time entries from Monday to Friday. You can easily adopt this to include Saturdays as well. A very important point to remember is that the date column msdyn_date that is used for time entries has a behavior of user local. Why is this important? It’s because of the actual database value that new time entry rows will have when they are created. As I’m in EET; UTC +2 in the winter and EEST; UTC +3 in the summer, a time entry row created today will have a database value for yesterday. I think you see the point now: when I create a time entry on a Monday, its database value is Sunday. When I create a time entry on a Friday, its database value is Thursday. The difference with winter/summer time has to do with the timestamp of the database value: when I’m in UTC +2 (winter), the timestamp of the database value will be 22:00:00. When I’m in UTC +3 (summer), the timestamp will be 21:00:00.
I could confuse you even more by revealing that GMT does not equal UTC in the summer in Britain because of BST but that’s not the point of this blog post. The key takeaway is that dates, times, and time zones can be very tricky and you need to think of your scenario when you’re using Power Automate. Next, let’s look at what the flow holds.
Initial actions
Like previously mentioned the flow fires off every Sunday. Three different variables are initialized to later hold values for the following:
- TotalDuration. Holds total time entries of a work week for a resource. This variable has to be of type float. Otherwise a div function later in the flow might not return correct results: If either the dividend or divisor has Float type, the result has Float type. Thanks to the fantastic John Liu for reminding me about this!
- ManagerArray. Holds details of resources and their time entries. Managers will get a table based on contents of this array and the following MangerResults array.
- ManagerResults. A manager’s direct reports from the ManagerArray are listed under the ManegerResults array. Think of it as a header that holds manager specific listings of resources and their time entries. Huge thanks to my colleague and fellow MVP Timo Pertilä for helping me out on logic around this array.
The next step is to compose dates and time stamps for time entries entered for a Monday in the time entry grid in ProjOps. Remember that the database value in this example will be for Sunday. As the flow runs on a Sunday, the previous Sunday’s date is needed. The following expression is used to get the date: addDays(utcNow(),-7,'yyyy-MM-dd'). As the database value is stored in a format of yyyy-MM-ddT21:mm:ssZ, the date needs to be formatted to include the time portion. The expression for date with a UTC +3 timestamp is formatDateTime(outputs('Date_for_Sunday'),'yyyy-MM-ddT21:mm:ssZ'). For UTC +2, just substitute 21 with 22.
The final initial action is to list all Bookable Resources for users (resourcetype eq 3). Next, all Bookable Resources are looped through for their weekly time entries.
Listing Bookable Resources and their time entries
The first action in the Bookable Resource (later BR) loop is to get the BR row being looped through. An expand query UserId($select=systemuserid,azureactivedirectoryobjectid) is used to get the AAD object id and GUID of the user related to the BR row (needed for sending a digest of a resource’s weekly time entries to the resource). The following get action is then used to get the manager related to the BR. A select column is used to limit the returned columns, and an expand query parentsystemuserid($select=systemuserid,azureactivedirectoryobjectid) is used to get the AAD object id and GUID of the BR’s manager (needed for sending the contents of the ManagerArray to a manager). The Azure AD get user actions are used to return information about the BR being looped through as well as their manager. Notice that configure run after for is successful and has failed must be set for the two actions seen in image 2. Otherwise the flow will fail if a manager hasn’t been set for a user.
The final action in the BR loop is for listing the BR’s time entries. It unfortunately means yet another loop, which will slow the flow down. The list time entries action has to have its filter rows exactly right or the action returns incorrect time entries:
- Bookable Resource must match the BR being looped through.
- Date of the time entry must be greater than or equal to the date of the previous Sunday with a UTC +3 timestamp.
- Time entry must be submitted or approved.
The OData filter expression used is _msdyn_bookableresource_value eq @{outputs('Get_Bookable_Resource')?['body/bookableresourceid']} and msdyn_date ge @{outputs('Date_for_Sunday_in_yyyy-MM-ddT21:00:00Z')} and (msdyn_entrystatus eq 192350003 or msdyn_entrystatus eq 192350002). Remember to substitute the output expressions above with dynamic content. Next, a BR’s time entries will be looped through.
The time entry loop is fairly simple. A get records action is used to retrieve the time entries being looped through. By incrementing the TotalDuration variable a week’s time entries can be summed up.
The next actions take place outside the time entry loop but inside the BR loop seen in image 2. I’ve blurred the time entry loop but left it visible in image 4 so that it’s easier to observe the big picture. As time entries are stored in the database in minutes, the TotalDuration variable has to be divided by 60 to get a week’s total time entries in hours. This is where the division will produce incorrect results if the variable isn’t initialized as a float at the beginning of the flow. The div expression used is div(variables('TotalDuration'), 60). The next action of Missing hours is used to subtract the previous action’s total hours from the weekly hours set on the Bookable Resource row. The expression used is sub(outputs('Get_Bookable_Resource')?['body/d2d_weeklyworkhours'],outputs('Total_hours_for_Bookable_Resource_for_this_week')).
As I’m writing this post I realized I’ve not considered a scenario where total hours are greater than weekly hours i.e. overtime scenarios. This is something you might want to take into consideration if you’re building a similar flow for your organization.
As the total hours for a week vs. weekly hours are now know, an Adaptive Card is sent to the resource if their missing hours for a week are greater than 0. Remember to use environment variables for your D365 URL in your AC if you want users to be able to open the time entry grid in ProjOps. The JSON of the AC is under image 6. I won’t dissect the AC in more detail as that would bloat this blog post. The posted AC can be seen in image 7.
{
"type": "AdaptiveCard",
"$schema": "http://adaptivecards.io/schemas/adaptive-card.json",
"version": "1.2",
"body": [
{
"type": "TextBlock",
"text": "Hi <at>@{outputs('Get_Bookable_Resource')?['body/name']}</at>!",
"wrap": true,
"size": "Large"
},
{
"type": "TextBlock",
"text": "Your weekly time entries don't add up to @{outputs('Get_Bookable_Resource')?['body/d2d_weeklyworkhours']} h. You only submitted @{outputs('Total_hours_for_Bookable_Resource_for_this_week')} h for this week. Check your time entries and submit the missing @{outputs('Missing_hours')} hours.",
"wrap": true
},
{
"type": "ActionSet",
"actions": [
{
"type": "Action.OpenUrl",
"title": "Open time entries",
"url": "yourUrlHere"
}
]
}
],
"msteams": {
"entities": [
{
"type": "mention",
"text": "<at>@{outputs('Get_Bookable_Resource')?['body/name']}</at>",
"mentioned": {
"id": "8:orgid:@{outputs('Get_Bookable_Resource')?['body/userid/azureactivedirectoryobjectid']}",
"name": "@{outputs('Get_Bookable_Resource')?['body/name']}"
}
}
]
}
}
We’re closing in on the end of the BR loop. The first of the final actions in the loop is an append action for appending properties to the ManagerArray. The MangerArray is later sent to a BR’s manager as a list that contains all time entries from a manager’s direct reports. The final action in the BR loop sets the TotalDuration variable to 0 so that the next iteration of the loop starts the count of a new resource’s time entries from 0. The Compose ManagerArray action is outside the BR loop and is used to compose the contents of the ManagerArray. This makes it easier to read through the flow as it executes.
The compose actions highlighted in red in image 8 are related to using Pieter’s method instead of incrementing variables. This blog post won’t cover that method in more detail – I may (or may not) write a follow-up later that compares variables and Pieter’s method and their performance.
Prepping manager arrays
The following apply to each and the immediate related actions outside the loop are for prepping the manager arrays to contain information that can be sent to managers about their direct reports’ weekly time entries. The idea behind the ManagerArray loop is to append the contents of the ManagerArray to the ManagerResults array so that only resources that are a manager’s direct reports are included in the ManagerResults array. Otherwise every manager would get a list of all resources in the entire organization.
The first thing to do is to filter the ManagerArray. The idea is: When a manager in the ManagerArray matches the manager being looped through, append the results to the ManagerResults array. The manager’s direct reports from the filtered ManagerArray are under the Team property and the name of the manager is under the ManagerHeader property.
Use the advanced mode of the filter array action to input the expressions. The one I’ve used is
@equals(item()?['Manager'], items('Apply_to_each_ManagerArray')?['Manager'])
The dynamic content for Team in the loop’s append to array action is the body of the previous filter action. The value for the ManagerHeader property is the following expression, which populates the property with the manager from the loop’s iteration: items('Apply_to_each_ManagerArray'')?['Manager'].
After the ManagerArray loop is cleared, a union expression is needed to find all distinct records in the ManagerResults array. You can read more about union expressions from MVP Vivek Bavishi’s blog. The expression I’ve used is union(variables('ManagerResults'),variables('ManagerResults')).
As some resources might not have a manager set, the following filter action will weed out all results where the ManagerHeader property only has a value of /. Why can it hold such a value? The answer is in the JSON of the ManagerArray seen in image 8. I’ve chosen to include both the email and the name of a manager in the property and those two have a / between them. The value for ManagerHeader in the filter comes from the following expression: item()?['ManagerHeader'].
A compose action stores the filtered ManagerResults to make the flow easier to read as it has executed. The last action before the flow’s final part is a parse JSON for the filtered and composed ManagerResults. The JSON schema that I’ve used is below.
{
"type": "array",
"items": {
"type": "object",
"properties": {
"ManagerHeader": {
"type": "string"
},
"Team": {
"type": "array",
"items": {
"type": "object",
"properties": {
"Bookable Resource": {
"type": "string"
},
"Week's total time entries": {
"type": "string"
},
"Week's missing time entries": {
"type": "string"
},
"Manager": {
"type": "string"
}
},
"required": [
"Bookable Resource",
"Week's total time entries",
"Week's missing time entries",
"Manager"
]
}
}
},
"required": [
"ManagerHeader",
"Team"
]
}
}
Sending managers their direct reports’ missing time entries
The final loop is for sending managers the weekly sum total time entries of their direct reports so that they know what their subordinates are up to. This is naturally based on the parse JSON from the previous action seen in image 9. The table that’s sent is just an example of how to provide managers with a digest of time entries. If you want to make conditions about what is sent, you can do so by modifying the flow I’ve covered in this blog post.
The first action in the loop is used to extract the manager’s UPN from the ManagerHeader property with the following expression: split(items('Apply_to_each')['ManagerHeader'], '/')[1]. The second action extracts the manager’s name with expression split(items('Apply_to_each')['ManagerHeader'], '/')[0].
I’ve chosen to create the columns of an HTML table automatically, based on the dynamic content from the Team property (from the parse JSON). Formatting the table turned out to be trickier than I thought. The Teams action seen in image 10 didn’t play ball when I inserted HTML to it. I had to play with inline CSS a bit by using a replace expression against the created HTML table to get it to look a bit nicer. The expression with inline CSS is below.
replace(replace(replace(body('Create_HTML_table'),
'<th>','<th style="text-align:center;color:white;background-color:#077D3F;padding:2px">'),
'<td>','<td style="text-align:center;color:black;padding:3px">'),
'<table>','<table style="border-collapse:collapse;width:100%">')The final action of the flow is to send a message to a manager based on the UPN extracted a few actions back. The body of the message is naturally the output of the formatted table. Image 11 shows the message that managers will receive in Teams chat.
I hope this longish flow gives you an idea of how to send time entry reminders to resources and their managers. The managers part is what’s a bit tricky so if you can leave that out, the flow will perform a bit faster and will be more straightforward to build.