Sunday, 14 April 2019

Storing the date input value in a Dynamics 365 or CDS DateTime field

In Flow there are triggers and one of the CDS triggers available is the "When a record is selected." In this trigger you can configure an input that will allow the end user to enter information. The available inputs today are the following:

When you configure your trigger to use the date input, it might puzzle you at first when you look at the value in your model-driven app. For example in my Flow the end user is selecting a date and the value will be used as the Due Date of a Task that is created from the Flow.

When you review your Flow run history it may look like this:

The Flow will be successful however when you look into the run detail you'll notice that in the Task action the default time value associated is midnight.

When the end user views the Task in the model-driven app it will look like the following:

This may not make sense at all to the end user. The reason behind it is due to how Flow, Dynamics 365 and CDS use UTC for DateTime fields. In a model-driven app the end user is viewing it in their time zone. In my case I am in the Melbourne, Australia time zone and UTC midnight equals 11am in Melbourne.

So how do you display it accordingly? That's what this WTF episode covers.

Retrieving the time zone of the user who triggered the Flow

Since the trigger is "When a record is selected," it's not possible to use the "Get record" action to retrieve the user's time zone as you would've seen in my previous WTF episode. This action is more suited to when you want to retrieve information based on a user lookup field. However since the Flow will be triggered from an end user selecting a record, the method this time has to be different.

Enter two other MVP sources
  1. Tip #1205: Local time in Flow using Common Data Service
  2. CDS, Microsoft Flow and DateTime formats
Both outline methods that allow you to identify a user's time zone. For this WTF episode I am using the method described in the CRM Tip of the Day post. The Flow in the CRM Tip of the Day post is available for download as well.

For the remainder of this blog post I'll now break down the Flow.

1 - The trigger

The trigger is when a record is selected in CDS through the model-driven app and the date input is used as seen earlier.

2 - Retrieving the date input value

Use a compose action to grab the date input value of the trigger. This is so that it can be referenced in the last CDS action.

3 - Get the end user's Office 365 profile information

Use either the Office 365 Get My Profile or Get User Profile - either one will work, by referencing User ID from the trigger. 

The purpose of this action is to retrieve a value that will identify the Dynamics 365 or CDS user through their Office 365 ID in order to retrieve their time zone from their defined personal settings. This value is what will be used in the next action to associate it to the user's Azure Active Directory Object ID.

4 - List records to grab details of the user in Dynamics 365 or CDS

The entity to references is Users and the filter query is the following,

This will identify the user in Dynamics 365 or CDS. If you look at the output of this action and view it in Notepad++, it's this property that lives in the Users entity:

5 - Retrieve the user GUID

This step is not necessary but it keeps your Flow tidy. If you don't do this step, the Apply to Each action will appear. Use a compose action to retrieve the systemuserid value. The function you're going to use is first. In a list record action, multiple results are usually returned. When you know that you're only going to reference one record returned by the query, the first function is handy because it'll make the output appear as "one" rather than a list of records. This will prevent the Apply to Each from appearing. Your expression will look like this


6 - Retrieving the time zone information of the user

This get record action is the same action from my last WTF episode where you retrieve the user's personal settings to identify their time zone through the timezonecode property. Reference the output of the previous compose action that has the systemuserid.

7 - Retrieving the time zone name of the user's time zone

This list records action is the same action my last WTF episode where you reference the time zone definition entity to retrieve the time zone name. The time zone of the user is defined through the property timezonecode.

8 - Retrieve the time zone name

Similar to #5 where you use the first function in an expression to retrieve the time zone name from the standardname property. This will keep your Flow tidy.


9 - Set the Time of the DateTime value

When you select a date from within the trigger (#1) the default time will be midnight. To avoid confusing the end user when they are viewing the DateTime field that will reference the selected date, provide a set/fixed time. In my vlog I was using 9am which is 09:00:00.

Use a compose action and the function you are going to use in your expression is formatDateTime to reference the output of the Date Input followed by a string format of the date and time. In the string format is where you included your desired set/fixed time.

formatDateTime(outputs('1.2_Date_input_value'), 'yyyy-MM-ddT09:00:00')

10 - Convert time zone action

This is your best friend when it comes to storing date and time values in Dynamics 365 or CDS DateTime fields. To display the desired set/fixed time of 9am, you must convert 9am in the context of the end user's time zone into UTC.  Since Flow, Dynamics 365 and CDS use UTC, this is required whenever you want to display a DateTime value that makes sense to the end user based on their time zone.
  1. For the base time, the output of the compose action in #9 is used.
  2. For the the format time, use the usual ISO format. To do this select custom and enter the format.
  3. For the source time zone, use the output of the compose action in #8.
  4. For the destination time zone, use UTC.

You can also use a compose action and use the convertTimeZone function in an expression if you want to be ultra nerdy and not use the official convert time zone action. This works too #TriedAndTested

11 - Grabbing the converted time

In my action that is creating a new Task record, I am referencing the output of the convert time zone action as the Due Date. 

Ta da!

Now when you run the Flow and view the record in the model-driven app, the date and time now displays correctly according to the end user's defined time zone.


Whenever you use the Date Input in your "When a record is selected" action and need to display the value back to the end user, it has to be defined in the local time zone of the user and then converted into UTC. To reference the user's personal settings who triggered the Flow for their time zone information, you'll need to grab details of their Office 365 profile to identify their user record in Dynamics 365 and CDS.