Time Zone Labels in Dynamics 365 Customer Insights - Journeys Events
One of those things I’ve been blissfully unaware of until pointed out by a customer, and now I can’t unsee or live with it. The ‘Event time zone’.
There is a magical ‘Time zone’ field in Dynamics 365 which is used for Events in Customer Insights - Journeys. Technically speaking in the background it all works great, the time is correctly converted to UTC and shows as expected for calendar invite timings etc BUT the actual name of the GMT offset time zone e.g. GMT+10 is only correct for half the year in any region that uses daylight savings… doh!
PS: In the world of Australia, November is summer which still blows my mind on an annual basis
Why do I care?
When you include this in your email to show the event time zone of the event, it doesn’t look too smart! And the long format label is pretty cringe worthy too. So lets create a field for the pretty and year round correct GMT offset time zone that can be used in event communications, with style. This post will show you how to create a simple Power Automate flow to generate a correct and neater time zone label for all your events.
Add a new field
Create a new text field to store the pretty label which can then be easily used in dynamic content emails. I called mine ‘Event timezone GMT label’ but that’s completely up to you. Be sure to add it to the form as read only.
A Child Flow for Dataverse Time Zone Conversion
I do love a good child flow. Instead of creating the logic tied to the event only, I have created it as a child flow that can be used from any Dataverse table using time zones.
Manually trigger a flow
Add two inputs to your trigger, be sure to select the correct data type. Both these inputs come directly from the standard Event data fields - the start date and the time zone code.
Text Input: DateTime
Number Input: TimeZoneCode
Find the time zone definition
All the data you need to calculate if its daylight savings or not live i this standard Dataverse table called ‘Time Zone Definitions’. Use a Dataevrse List Rows action to find the correct table entry for the time zone using the number code.
FindTimeZoneDefinition
Table name: Time Zone Definitions
Select columns: standardname
Filter rows: timezonecode eq @{triggerBody()['number']}
Row count: 1
Calculate the time difference
Using the data from the Time Zone Definition we can calculate the hours difference between from UTC and the current date/time and time zone combination.
dateDifference(convertTimeZone(formatDateTime(triggerBody()['text'], 's'), first(body('FindTimeZoneDefinition')?['value'])?['standardname'], 'Greenwich Standard Time'), triggerBody()['text'])
Create the GMT offset label
Now to pull it all together to say whether its + or - and the difference calculated, with a GMT label.
GMT
if(startsWith(outputs('HoursDiff'),'-'),'','+')
substring(outputs('HoursDiff'),0,sub(length(outputs('HoursDiff')),3))
Return the label to the parent flow
The label created in 'the compose action ‘GMTString is then returned to be used in an flow which calls it.
Use the child flow to update the Event time zone GMT label
Now we can generate the label for any given date and time zone code, we can now use this to update the event GMT offset label field.
When a row is added, modified or deleted
We need to trigger this flow whenever the start date or time zone is updated to ensure the label reflect an accurate GMT offset
Change type: Added or Modified
Table name: Events
Scope: Organization
Select columns: msevtmgt_eventstartdate,msevtmgt_eventtimezone
Call the child flow
Now we can trigger the child flow created above to do the GMT label creation, we just need to pass in the Event start date and Event time zone which already exist on the event table.
ConvertTimeZoneCode
Child Flow: CHILD Convert Time Zone Code to GMT Offset including Daylight Savings
DateTime: triggerOutputs()?['body/msevtmgt_eventstartdate']
TimeZoneCode: triggerOutputs()?['body/msevtmgt_eventtimezone']
Update the Event time zone GMT label
Use a Dataverse ‘Update a row’ action to update the event field with the label returned from the child flow.
UpdateTimeZoneCode
Table name: Events
Row ID: triggerOutputs()?['body/msevtmgt_eventid']
Event time zone GMT label: body('ConvertTimeZoneCode')?['gmtoffsetstring']
Use the field in email dynamic content
The ‘Event registration created’ trigger is great for using in a journey that spans multiple events with completely dynamic content. Now with a year round correct and concise time zones too!
