Rajeev Pentyala – Microsoft Power Platform

Have you ever worked with “Decimal” field with “Time Zone” Format?

Lets assume your Dynamics application has a global presence and you need to capture “Customer” info along with their “Time Zone”, we can create a Decimal field with “Format” set as “Time Zone”.

With “Time Zone” format, even though, you create a field of “Decimal” type, in UI it will render as drop down and in ‘Advanced Find’ you get a string.

Lets see how to migrate data to the “Time Zone” format field.

Whats “Time Zone” format:

As you would have noticed in above screens, “Decimal” field rendered as Drop down with all time zone options. Now the question is where we are getting these option?

The answer is, Dynamics has an internal entity named “Timezonedefinition” which pulls the time zone options from.

“Timezonedefinition” entity will have below 2 fields

• Standardname – Name of the Time Zone
• Timezonecode – Time zone code which is a decimal value

When you add a ‘Decimal’ field with ‘Time Zone’ format to an entity, in UI (i.e., Forms, Advanced Find, Views, etc.) you would get the ‘Standardname’ (i.e., Time zone name) however in the back end ‘Timezonecode’ gets saved.

To check that, export the records to Excel and you would find decimal values in “Timezone” field.

As you notice above, “(GMT-10:00) Hawaii” field rendered as 2 in exported excel, which “Timezonecode”

How to migrate data to “Time Zone” format field:

• First you need to fetch the “Time zone codes” using “RetrieveMultiple” SDK call (Sample code below)

var request = new RetrieveMultipleRequest{
Query = new QueryExpression(“timezonedefinition”){
ColumnSet = new ColumnSet(“timezonecode”, “standardname”),
}
};

var response = (RetrieveMultipleResponse)_service.Execute(request);

foreach (var item in response.EntityCollection.Entities){
var timeZone = (Entity)item;Console.WriteLine(String.Concat(“Time zone name: “, timeZone[“standardname“], “, time zone code: “, timeZone[“timezonecode“]));
}

• Executing above code would fetch “Timezone Names” along with “Timezonecode”

• Once you have the codes, you can migrate the records by setting the “Time zone codes” to “Time zone format” field.
• In the excel file, add new records with “Timezonecode”

• After the successful excel import you should see new records in UI with “Time Zone Name” replaced with “Time zone code” passed from excel.

🙂