Thu, 10/14/2021 - 15:44 By Danny Rodrigue…

Dataverse : How to change format of existing columns

On-line

Via the Web API, we can now change the format of existing fields/columns in Microsoft Dataverse

Description

Announced in a Power Apps blog post, it is possible to change the format of an existing field!

Until now, once fields have been created with their Data type it was impossible to convert them. For example, you may have created a field under the Text type and later on realize that it is used to store Email addresses. The only way was to create a new field, transfer all data from the old field to the new one and then delete the old field. Not ideal...

To resolved this complex process, Microsoft issued a new API to transform the format for Text, Integer, or DateTime data types. Note that this API will not transform nor touch the data of the fields/columns! As stated by Microsoft: it will simply instruct the client to display the content in the selected format.

Therefore, not all transitions are available. For example, a Text data type column cannot be transformed into a DateTime data type. With the table below, transitions can only be made for the same Column Type:

Column Type Platform format FormatName
Text text Text
  email Email
  textarea TextArea
  url Url
  tickersymbol TickerSymbol
  versionnumber VersionNumber
  phone Phone
  json Json
  richtext RichText
Memo / Multiline Text text Text
  email Email
  textarea TextArea
  internalextentdata Text
  json JSON
  richtext RichText
Whole Number none/string.Empty None
  duration Duration
  timezone TimeZone
  language Language
  locale Locale
Date and Time date Date
  datetime DateAndTime

At the time of this article, there is no UI interface to interact with this API. The only way is to use the API via code.

UI functionality will be available in the fullness of time

 

Example

We have the following field in the Contact table:

Dataverse : How to change format of existing columns

In the form, the field is displayed as a simple text field:

Dataverse : How to change format of existing columns

But as you can see, the text field is used to store email addresses! So we will change the type of the column to Email and the form will display the column with a button that, when clicked, launches your default email client and inserts the address in the To: field.

  1. First thing to do is to retrieve the MetadataId for the Contact table. To do that, we use the Web API with the link /api/data/v9.2/EntityDefinitions?$select=SchemaName
    1. Dataverse : How to change format of existing columns

       
  2. Then we perform another call to the Web API to retrieve the ID of the column we want to convert: /api/data/v9.2/EntityDefinitions(608861bc-50a4-4c5f-a02c-21fe1943e2cf)/Attributes?$select=SchemaName
    1. Dataverse : How to change format of existing columns

       
  3. Now we can make the HTTP PUT call to update the type of the column. For that, we will use XrmToolBox's plugin WebAPI Launcher
    1. The URL is: /api/data/v9.2.21091.138/EntityDefinitions(608861bc-50a4-4c5f-a02c-21fe1943e2cf)/Attributes(69f51227-b126-ec11-b6e5-6045bd8b0ca8)
    2. The request JSON content is:
      {
         "@odata.type": "Microsoft.Dynamics.CRM.StringAttributeMetadata",
         "FormatName": {
             "Value": "Email"
          }
      }
      1. Note that Email comes from the FormatName in the table above.
    3. Dataverse : How to change format of existing columns
       
  4. That's it! If we can back to the form, the field is now displayed as an Email!
    1. Dataverse : How to change format of existing columns

XrmToolBox plugin 

If you want you can use a great XrmToolBox plugin of Matt Beard named Format Changer, you can fint it here on directly from the plugin gallery of XTB. 

This tool is based on this new feature and will allow you to deal with format changes. ;)

Reference

Dataverse : How to change format of existing columns

Add new comment

Image CAPTCHA
Enter the characters shown in the image.