Dynamics 365 Project Service Automation: Weekly time entry reminders with Power Automate

Professional services organizations want their staff to submit time entries promptly. Some organizations require employees to submit time entries daily, while weekly time entries are in general probably the most common practice. Sometimes people forget so a helpful email reminder can come in handy. Personally, I’m fairly precise with my time entries but sometimes I simply forget to submit them on Fridays. In these cases, I get a reminder email on Sunday.

D365 PSA doesn’t have a built-in reminder workflow for Time Entries but as Power Platform has Power Automate for application integration and automation, we can use it to easily solve a requirement for an automated reminder email. For this blog post the use case is: “As an employee of a professional services organization, I should be reminded of nonexisting or unsubmitted time entries every Sunday night.” I’ve chosen Sunday simply because that’s what I’m personally used to. You can easily adapt the Flow we’re dissecting in this blog post to fit your specific requirements.

Weekly time entry reminder Flow

Depending on your time zone, there might be a prerequisite to the time entry reminder Flow. I’ve previously written about aligning and fixing the value in msdyn_date (Date), depending on a user’s time zone setting. A value in the Date field will affect the results of the reminder Flow. I recommend you read my previous blog post here to get up to speed with how the Date field behaves.

The Flow’s initial actions

The weekly time entry reminder Flow fires off on Sunday evenings. A compose action stores the value of a date 7 days in the past from utcNow(). That date represents the start date of a workweek. In this example a week begins on a Monday so the evaluated time period for time entries is Monday to Sunday. If your use case is different, adjust the date values in the Flow to meet your requirements.

TimeEntryDetails variable will be used later on to define which details will eventually be sent to a user in an email. I’ve initialized the variable with a value to remember the JSON that I want to use later. TimeEntryDetails are later appended to TimeEntryArray. That’s why that variable needs to be initialized at this stage in the Flow.

The JSON for the TimeEntryDetails variable is:

{
  "Project Name": "",
  "Task Name": "",
  "Date": "",
  "Entry Status": ""
}

For a similar example of initializing object and array variables, check out my previous post on Listing a user’s active Resource Assignments in Project for the web and D365 PSA with Power Automate.

Listing Bookable Resources and their Time Entries

The next step is to list all Bookable Resources that are of type User (resourcetype eq 3). When testing the Flow, it’s a good idea to also limit the returned results to just one or two specific users. Otherwise, every user with a Bookable Resource record will receive emails when you test the Flow. The magic happens inside an apply to each loop for each Bookable Resource. A Get User action is needed so that we can get a user’s email address. If you want the loop to run faster, enable concurrency control and set a degree of parallelism. A value of 40 has worked for me.

Another list records action lists a Bookable Resource’s Time Entries. A filter query filters the results as follows:

• Look for Time Entries for the Bookable Resource being processes in the apply to each loop.
• Look for Time Entries with a Date greater than the output of the compose action in the Flow’s initial steps (its name in this example is Compose date for 7 days in the past from now).
• Look for Time entries with an entry status of Draft or Returned.

If Draft or Returned Time Entries are found, the condition is true (the list records action returns a value) and the Flow runs on the Yes branch of the condition. To check whether values are returned or not, the expression below is used in the condition. This is a handy way to avoid yet another apply to each loop.

empty(outputs('List_Draft_and_Returned_TEs_for_BR_for_past_7_days')?['body/value'])

Yes branch of the condition – Draft or Returned Time Entries

The next apply to each loop we can’t avoid. Getting Time Entries based on the previous list records throws the Flow into a nested apply to each loop. For this loop, I’ve set degree of parallelism to 30. It’s trial and error figuring out what value for degree of parallelism works and what doesn’t. That said, a “nice to have” Flow such as this one doesn’t necessarily need to be super fast so it’s better to be safe than sorry when it comes to setting degree of parallelism in a nested loop.

After getting our hands on Time Entries, the next actions get us the related Project and Project Task records. As Time Entries can be created and submitted without a value for Project or Project Tasks, configure run after has to be set for the actions succeeding the Get Project and Get Project Task actions. These actions should run when the previous action is either successful or when it has failed.

After the get actions, it’s time to simplify the value of a Time Entry’s msdyn_date (Date). This keeps the format of the eventual email of Draft and Returned Time Entries a lot simpler. In professional services, a time entry’s time component is seldom of interest. In PSA the time component is also misleading as a user can’t give a freely defined time for msdyn_date, when a Time Entry record is being created. To drop off the time component from the value, the following expression is used:

first(split(last(split(outputs('Compose_Time_Entry_Date'),'')),'T'))

The next switch action is an interesting one. A Time Entry’s entry status on a created email is valuable information as a user can easily take appropriate action based on entry status. As the Yes branch of the previous condition lists Draft and Returned Time Entry records, we want to indicate whether a Time Entry is one or the other. The challenge is that only a value for option set msdyn_entrystatus (Entry Status) is returned by the Get Time Entries action. We can’t expect a user to know what a numeric value on an email listing Time Entries means. They will definitely expect a more descriptive text.

A switch action is an easy way of composing a label for the option set’s value. The value of msdyn_entrystatus (Entry Status) is evaluated in the switch. The Flow runs through one of two paths (Default or Returned) and a compose action is used to compose a label for the related path in the switch. This label is then used in the following action, which sets a value for the TimeEntryDetails variable.

The TimeEntryDetails variable can be set with information that best matches your use case. I’ve composed hyperlinks for the related Project and Time Entry records so that a user can easily open the related records from the email message. Entry Status has dynamic content from both compose actions in the switch. This way the correct label is displayed despite the path the Flow has run in the switch.

The final step in the apply to each is to append the output of TimeEntryDetails to TimeEntryArray. This way the array can be used to compose an HTML table.

The final actions on the Yes branch are used to compose an HTML table for an email message and to send the email itself. The output of TimeEntryArray is used in the Create HTML table action. The Compose links action is important so that all the hyperlinks in the email message work. It is used to make the HTML in the table usable. For more information, be sure to read this community post. The expression used in the compose is:

replace(replace(replace(replace(body('Create_HTML_table'), '&lt;', '<'), '&gt;', '>'), '&quot;', '"'), '&amp;', '&')

The Compose email content action is only a placeholder for the HTML used in the final action, which sends an email message. Having a placeholder compose is a good way of backing up your HTML, in case you edit the final action and do something that breaks the HTML table. The HTML I’ve used is as follows:

<html>
<head>
<style>
a:link {
  color: #077D3F;
}
table {
  border-collapse: collapse;
  width: 100%;
}

th, td {
  text-align: left;
  padding: 10px;
  width:20%;
}

tr:nth-child(even){background-color: #f2f2f2}

th {
  background-color: #077D3F;
  color: white;
}
</style>
</head>

<body>
<div><span style="font-size:16px;font-family:&quot;Segoe UI&quot;,sans-serif;color:#364147;font-style:regular"><td style="padding:0in 0in 8px 0in">
Hi @{outputs('Get_User')?['body/fullname']},
<br>
<br>
These are your Time Entries which are in draft or are returned. Please submit your Time Entries as soon as possible!
</td></span></div><br>

@{outputs('Compose_links')}


</body>
</html>

No branch of the condition – Nonexistent Time Entries

If Draft or Returned Time Entries for a Bookable Resource don’t exist for a given period, the Flow runs the “No” route. In this branch, we want to check if a user has any Time Entries in PSA at all. Maybe a user has forgotten or neglected a week’s entries. The first action on this route is a list records action, which checks if Submitted, Approved or Return Requested Time Entries are found for the past 7 days. If some are, that means a user has some entries in PSA and no additional action is needed. This Flow doesn’t evaluate the amount of Time Entries. It only looks if Time Entry records per se exist. The expression used to check if the list records action returns records is the same that was used in the previous condition.

The compose action composing a date 7 days in the past from now is in the Flow, in case the logic that you want to apply to this branch differs from the other branch. The Compose time range start date action drops the time component out of the previous action’s output. This way we can get a clean date value in an email message to indicate the beginning of the period for which Time Entries don’t exist. The expression used in the compose is:

first(split(last(split(outputs('Compose_date_for_7x_days_in_the_past_from_now'),'')),'T'))

The end of the time range is composed in the following action. As the Flow runs on Sunday evenings, the end date is based on utcNow() and the time component is then dropped from the value. The expression used in the compose is:

first(split(last(split(utcnow(),'')),'T'))

The final action in this branch of the Flow is to send an email to a user. The outputs of the previous compose actions are used in the email message to indicate the period for which Time Entries don’t exist.

Composed email messages and additional considerations

The images below illustrate the email messages users will receive if they have Draft or Returned Time Entries or if Time Entries for a given period don’t exist. If you want to separate the two branches in the Flow and build more complex logic into them, I recommend splitting the Flow into smaller pieces by using child Flows. This will make it easier and more straightforward to work on individual pieces. Child Flows are also great for diagnosing logic errors in complex Flows.

As always, this Flow can be downloaded from the TDG Power Platform Bank.

Disclaimer:
All my blog posts reflect my personal opinions and findings unless otherwise stated.