Dataverse : How to change format of existing columns

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:

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:

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

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. 
      
       
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. 
      
       
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. 
       
4. That's it! If we can back to the form, the field is now displayed as an Email!
   1. 

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

• Power Apps Blog post
• Dataverse Developer's documentation

Dataverse : How to change format of existing columns