Dynamics 365 Project Service Automation: Update and fix Date on Time Entry

Tested on:
Dynamics 365 PSA version 3.10.9.21

Time Entries in PSA can be created by using either the quick create form on Time Entry or the Time Entry Grid PCF control that is by default enabled on the entity. If we look at the msdyn_date (Date) field after a Time Entry record has been created, the field’s value is displayed in a user’s local time in the UI. So far so good. This is how it should be as OOTB the field’s behavior is set to user local.

msdyn_date field

This leads us to another very important topic. What if the behavior of msdyn_date should be Date Only or Time-Zone Independent in your use case? If you find yourself even considering this, remember that the behavior gets locked if you change it. I do NOT recommend changing the behavior of a mission-critical field such as msdyn_date. If the product team introduces a change to the Time Entry logic, you will risk breaking Time Entries. I’ve tested all the different behaviors and while Time-Zone Independent does work, changing the behavior of msdyn_date to Date Only will completely break the Time Entry Grid. Do NOT change the behavior of msdyn_date to Date Only!

Differences in Time Entry date values

Time to get back on track and fix the Date field. So why does it need fixing per se? The answer is in the field’s database value. I’ve compared Time Entries created with both quick create and the Time Entry Grid for 8 different time zones (set from personalization settings) and I found that the database values differ between the two Time Entry entry methods. Quick create produces results we can expect of a field with behavior set to User Local.

The results for the Time Entry Grid are very different: There is a 3-hour difference in the time component compared to fields created with quick create. The reason for this is that the custom control has built-in DST handling. The following table illustrates the differences in Time Entries for different time zones. The table has been composed at the end of March and before European countries had switched to DST. When DST is not observed, the difference in the database value for time zones UTC +1-3 (Amsterdam, Helsinki, Istanbul) is a day. This causes inconsistencies when creating reports against the msdyn_timeentry entity.

Personalization SettingsDB value –
quick create
DB value –
grid control
Date given for record in UI
UTC -7 Pacific (PDT)2020-03-17T07:00:00Z2020-03-17T10:00:00Z17.3.2020
UTC +-0 London2020-03-17T00:00:00Z2020-03-17T03:00:00Z17.3.2020
UTC +1 Amsterdam2020-03-16T23:00:00Z2020-03-17T02:00:00Z17.3.2020
UTC +2 Helsinki2020-03-16T22:00:00Z2020-03-17T01:00:00Z17.3.2020
UTC +3 Istanbul2020-03-16T21:00:00Z2020-03-17T00:00:00Z17.3.2020
UTC +4 Abu Dhabi2020-03-16T20:00:00Z2020-03-16T23:00:00Z17.3.2020
UTC +5:30 Mumbai2020-03-16T18:30:00Z2020-03-16T21:30:00Z17.3.2020
UTC +10 Sydney2020-03-16T13:00:00Z2020-03-16T16:00:00Z17.3.2020
Database values. DST not observed in Europe.

When DST is observed, the database values behave differently. As the table below illustrates, DST in the UK causes the database value of Time Entries from quick create to behave differently compared to when DST is not observed: The date changes as the time component changes by an hour. The results for UTC +10 Sydney are a bit odd as there is a 4h difference between quick create and Time Entry Grid entries but we can disregard that time zone as the difference doesn’t impact reporting.

Personalization SettingsDB value –
quick create
DB value –
grid control
Date given for record in UI
UTC -7 Pacific (PDT)2020-04-03T07:00:00Z2020-04-03T10:00:00Z3.4.2020
UTC +1 London2020-04-02T23:00:00Z2020-04-03T02:00:00Z3.4.2020
UTC +2 Amsterdam2020-04-02T22:00:00Z2020-04-03T01:00:00Z3.4.2020
UTC +3 Helsinki2020-04-02T21:00:00Z2020-04-03T00:00:00Z3.4.2020
UTC +3 Istanbul2020-04-02T21:00:00Z2020-04-03T00:00:00Z3.4.2020
UTC +4 Abu Dhabi2020-04-02T20:00:00Z2020-04-02T23:00:00Z3.4.2020
UTC +5:30 Mumbai2020-04-02T18:30:00Z2020-04-02T21:30:00Z3.4.2020
UTC +10 Sydney2020-04-02T13:00:00Z2020-04-02T17:00:00Z3.4.2020
Database values. DST observed in Europe.

Fixing the value in the Date field

Let’s look at a possible solution to fixing the value in the msdyn_date (Date) field so that its value is consistent across the two different entry methods. I’m going to focus on an approach that involved Power Automate. Consider what technology to implement for a fix, based on your exact use case. Power Automate isn’t the only technology that can be used. To name a few others, a fix could also be implemented with Logic Apps, plug-ins or Azure Functions.

As DST has an impact on how the Flow performs, the Flow dissected in the following chapters may not work for all time zones. I have tested the Flow with the time zones illustrated in the tables found in the previous chapter. DST per se doesn’t impact the Flow’s end results for time zones and cities illustrated in the tables.

Flow – Part 1

The Flow fires off when a Time Entry is created. The first few actions are good for diagnosing the value that the msdyn_date field will have. The time component needs to be extracted from the value so that it can be evaluated in the switch action. Only values for UTC +1-3 are of interest. The msdyn_date field will be updated based on the evaluated value as the Flow progresses. The expression for extracting the time component is:

first(split(last(split(outputs('Compose_Date_from_Get_Time_Entry'),'T')),''))
Part 1 of the Flow.

Flow – Part 2

As the time component is evaluated in the switch action, the switch will progress the Flow based on the evaluated value. As only certain UTC +1-3 time zones have discrepancies in database values, time components for those time zones need to be evaluated. These time components are 23:00:00Z, 22:00:00Z and 21:00:00Z. Note that DST related to those time zones doesn’t affect the Flow. As the Flow progresses, +1 day is added to msdyn_date when a Time Entry includes the previously mentioned time components. For other time zones, the switch will run the default path and the Flow will terminate as canceled.

The approach I’ve taken is to update all time entries created from a quick create. By adding a day to all Time Entries created from a quick create, the database value of Date will match no matter how a record is created. The expression used to add a day is:

adddays(outputs('Compose_Date_from_Get_Time_Entry'), 1)

If you have a global professional services business, your resources span continents or you are in a time zone where DST might affect the outcome of the Flow, you may need to re-evaluate the approach I’ve covered. For a practice operating in time zones discussed in this blog post, the described fix should work just fine. As always, this Flow can be downloaded from The Power Platform Bank here.

Part 2 of the Flow.
Disclaimer:
All my blog posts reflect my personal opinions and findings unless otherwise stated.

Leave a Reply

Your email address will not be published. Required fields are marked *