Thursday 14 March 2019

How to populate a date and time field based on a user's local time zone

22:17 Posted by Benitez Here , , , , , , No comments
There's been questions from the Dynamics community about not being able to successfully populate a date and time field in Dynamics 365 or CDS. A cryptic error is presented at times and often leads to frustration because when populating a date and time field in classic Dynamics 365 workflows it is a breeze. Hold on to that thought though.

In this WTF episode I show you how to populate a date and time field based on a user's local time zone. Yes, you read that right. This has been a problem for organisations that use Dynamics 365 or a Model-Driven app where end users are in multiple time zones. Now with the power of Flow you can do it without code and it's fast.

Use case

When a Case is created, create a new Task for the Owner with a Due Date by adding 2 days and set the date to 9am. Guilty - I forgot to explain this in my WTF vlog episode 😂 #oops

What you're familiar with today

There's two methods in classic Dynamics 365 workflows that are available when configuring the Due Date of an activity.

Using operators

You can add or subtract the following
  • Months
  • Days
  • Hours
  • Minutes
Nice but not good enough. Why?

You can't use a fixed time of 9am. It's either add or subtract the Hours and Minutes.

Using a default Date and Time

You can set a default date and time value.

Nice but still not good enough. Why?

You're restricted to the fixed date you've selected and cannot use the add or subtract function. However you can set a fixed time of 9am.


If you do choose to default the date and time, there's still an underlying problem that exists. It's to do with the time zone of a user.

The time set is based on the time zone of the user who created the classic Dynamics 365 workflow. For users in a different time zone of that particular user, they will see the time of the Due Date field in their local time.

As seen in my vlog - a user in the Melbourne, Australia time zone will see the Tasks with a Due Date of 9am.


A user in the Wellington, New Zealand time zone will have their Task created with a time of 11am for the Due Date. This is because Wellington is 2 hours ahead of Melbourne, Australia time zone.


This has always been a constraint when working with classic Dynamics 365 workflows.

Still think populating a date and time field in classic Dynamics 365 workflows is a breeze? 
No soup for you!

Show me the power of Flow

The solution I've come up with applies the convert time zone action which was covered previously in WTF episode 11 where I showed you how to delay sending a birthday email based on the time zone of recipient.
  • Watch WTF episode 11 here

1.2 Get the User of the Case

The trigger of the Flow will be when a Case is created. Through the Owner field, we can retrieve the user record using the CDS Get Record action.

1.3 Get the User's time zone

In Dynamics 365 and CDS a user's time zone is configured through Personalization Settings.



We want to retrieve this information about the user. To achieve this, use the CDS Get Record action and reference the User Settings entity. This entity is not visible from within Dynamics 365 or CDS and is traditionally accessed through the Dynamics 365 SDK.


When you run this Flow and observe the response returned in this action, you'll see a property (which is a field in the User Settings entity) called timezonecode.


This represents the time zone of the user. However we want to know the name of the time zone so that it can be used later in the Convert Time Zone action.

1.4 Get the time zone name of the time zone code

As per this docs.microsoft article, there are three entities that store time zone information in Dynamics 365 (and I also assume CDS). The entity that has the time zone code information is the Time Zone Definition entity. This entity is not visible from within Dynamics 365 or CDS and is traditionally accessed through the Dynamics 365 SDK.

Use the CDS Get Record action to retrieve the time zone definition entity.


When you run this Flow and observe the response returned for this action, you'll see an array of all the time zone definitions for Dynamics 365. For each of the time zones there will be a property called standardname that represents the time zone name.


How do you return only the time zone that is associated to the user's settings?

1.5 Use a query through the CDS List Records action

This is a tip I learnt from fellow MVP and colleague Natraj. The CDS List Records action allows you to query against an array. In previous episodes I showed how to query against records in Dynamics 365. This time round, I am going to use it against the returned response.

The filter query expression to use is to reference the timezonecode field in the Time Zone Definitions entity and the timezonecode field from the User Settings entity from the earlier action (1.3 Get User's Personal Settings). 


This will then only return the time zone definition details of the user's configured time zone.

1.6 Configuring the date and time for the value of the Due Date field

Use a Compose action to set the date and time. In my example, I am adding 2 days to the current date and setting the time to 9am. To achieve this I am using the addDays function
  • To reference the current date, I am using the utcnow function
  • To set the number of days, I am using the integer of 2
  • To set the time, I am entering a string value
The expression I have used is addDays(utcNow(), 2, 'yyyy-MM-ddT09:00:00')

1.7 Convert the local time into UTC

You would have seen or read me cover this in WTF episode 11. I'll outline what I am doing slightly different this time.
  • For the Base Time, I am referencing the output from the previous action where I've set the date and time.
  • For the Format String value it has to be in the UTC Date time format.
  • The source time zone will be the standardname property from 1.5 Get Time Zone Name of User action. When you select this, the Apply to Each will appear.
  • The destination time zone will be the UTC time zone. 

Quick recap on why UTC

  • Remember, Dynamics 365 underneath the hood stores date and time as UTC. Time zone entity definitions + user's time zone is what makes it appear as "local" in a Model-Driven app.
  • Stephen's blog post explains that Flow treats Date and Time in UTC.

1.8 Creating the Task

The last action is to create the Task and reference the output of the convert time zone action as the Due Date value.


The result

When you trigger the Flow as a user based in Wellington, New Zealand, the Task will have a time of 9am in their local time for the Due Date.


When you trigger the Flow as a user based in Melbourne, Australia, the Task will have a time of 9am in their local time for the Due Date.


If you compare the Tasks with each other the due date and time will be different.

As the Wellington based user, I will see the Melbourne user's Task have a due time of 11am.


As the Melbourne based user, I will see the Wellington user's Task have a due time of 7am.


To see it in action watch my WTF vlog.

Summary

Using the power of Flow you can now set the Due Date of an activity or any other date and time field based on a user's local time zone. You are not able to achieve this functionality today in a classic Dynamics 365 workflow.

This Flow is available for download in the TDG Power Platform Bank.

For another great time zone related post, check out Tip #1205.