Data type format conversions

Microsoft Dataverse has several data types that can be configured with different formats. You can specify the format of the column using either the solution explorer or by API operations. The following sections provide more details about data type formats, including:

Supported formats by data type

The format column specifies the UI on how to display the content. Some formats available in the UI are Phone, Email, or Duration. Suppose you have experimented with these formats before. In that case, you know that the applied formats do not validate context, domains, or any other values. They instruct the UI, which control to use for that type.

Formats

The following table provides information about the formats available for each data type:

Data Type Format name Description Available to app maker? Notes
Text Text Basic text column that contains text characters. Yes Default format value for the text column.
Text Area Text column that contains text characters and also allows line breaks. Yes
Email The text provides a link to open the user’s email application. Yes
URL The text provides a hyperlink to open the page specified. Any text that does not begin with a valid protocol will have “https://” prepended to it. Yes
Ticker Symbol For most languages, the text will be enabled as a link to open the MSN Money website to show details about the stock price represented by the ticker symbol. Yes
Phone Columns will be click-enabled to initiate calls. Yes
JSON Stores text in JSON format. Yes (API only) Only in non-SQL stores like Audit.
Rich Text Allows rich text formatting, including HTML markup. Yes (API only)
Version Number Stores the version number for rows. No System use only.
Multiline Text (Memo) Text Basic text column that contains text characters. Yes
Text Area Text column contains text characters and allows line breaks. Yes
Email For internal use only. No
JSON Stores text using JSON format. Yes (API Only) Only in non-SQL stores like Log.
RichText Allows for rich text formatting, including HTML markup. Y (API Only)
InternalExtentData For internal use only. No System use only
Whole Number None/string.Empty This option simply displays a number. Yes Default format value for whole number column.
Duration This format option can be used to display a list of duration options. But the data stored in the database is always in minutes. The column looks like a drop-down list and provides suggested options like 1 minute, 15 minutes, 30 minutes all the way up to three days. You can choose these options. However, you can also just type in the minutes, which resolve to that time. For example, type in 60, and it resolves to 1 hour. You can also enter “1 hour” or “two days,” and it will resolve to display that time.
The duration must be entered in the following format: “x minutes”, “x hours,” or “x days”. Hours and days can also be entered using decimals, for example, “x.x hours” or “x.x days”.
NOTE: Values must be expressible in minutes, subminute values will be rounded to the nearest minute.
Yes System reads this value in seconds.
Timezone This option displays a select list of time zones such as (GMT-12:00) International Date Line West and (GMT-08:00) Pacific Time (US & Canada). Each of these zones is stored as a number. For example, for the time zone (GMT-08:00) Pacific Time (US & Canada), the TimeZoneCode is 4. Yes
Language This option displays a list of the languages provisioned for your organization. The values are displayed as a drop-down list of language names, but the data is stored as a number using LCID codes. Language codes are four-digit or five-digit locale IDs. Valid locale ID values can be found at Locale ID (LCID) Chart). Yes
Locale Value that corresponds to a specific locale using ISO standard values. Yes (API Only) Not shown in Power Apps Maker UI.
Date and Time Date Only Date only. Includes a time of 00:00:00 if the User Local or Time-Zone Independent is selected. Yes
Date and Time Date and time format. Yes Default format value for DateTime column.

Format conversion

You can change the data type format to any of the compatible formats that data type supports. Changing the format retains your previous table definitions (maxsize) if they exist in the new target format. If an inbound payload does not include a format, Dataverse assumes the format shouldn't be changed. You can convert the format by an API call with the desired payload in the FormatName column. Changing the value in the Format column is recommended as any newly added Format selections are ignored.

Note

At this time, format conversions are only done by performing API operations.

Changing formats doesn't change any data present in the column. Due to this, you may notice some unexpected formatting issues that need to be resolved after the conversion.

As mentioned in the table above, there are some restrictions for format conversions:

  • JSON can only be used if a table is part of non-SQL storage (that is, Log).

  • You cannot convert columns with the formats of type emailbody, internalextentdata to other formats. Any conversion for these are ignored, and no error message is provided.

  • You cannot convert a column to the formats of emailbody, internalextentdata to other formats. If attempted, an error will occur.

  • Date only cannot be converted to DateTime, but Date with the behavior of User Local or Time-Zone Independent can be changed to DateTime.

If you change the data type to an incompatible format, the following error is displayed:

The format <<formatname>> is not valid for the <<datatype>> type column <<columnname>> of table <<tablename>>. For example, the format datetime is not valid for the text type column.

To set or change the format of a data type, you need to add the new format details into an OData API POST for a new column or PUT call to update an existing column:

Note

For more samples on how to use the API, see https://github.com/microsoft/PowerApps-Samples

PUT [Organization URI]/api/data/v9.2/EntityDefinitions(402fa40f-287c-e511-80d2-00155d2a68d2)/Attributes
HTTP/1.1

Accept: application/json
Content-Type: application/json; charset=utf-8
OData-MaxVersion: 4.0
OData-Version: 4.0
{
 "AttributeType": \<\<Data type you are setting the format for\>\>,
 "AttributeTypeName": {
"Value": *\<\<Datatype Type\>\>*
},
"Description": {
"@odata.type": "Microsoft.Dynamics.CRM.Label",
"LocalizedLabels": [
{
"@odata.type": "Microsoft.Dynamics.CRM.LocalizedLabel",
"Label": *\<\<text label to use for the format\>\>*
"LanguageCode": 1033
}
]
},
"DisplayName": {
"@odata.type": "Microsoft.Dynamics.CRM.Label",
"LocalizedLabels": [
{
"@odata.type": "Microsoft.Dynamics.CRM.LocalizedLabel",
"Label": *\<\<text label to use for the format\>\>*
"LanguageCode": 1033
}
]
},
"RequiredLevel": {
"Value": "None",
"CanBeChanged": true,
"ManagedPropertyLogicalName": "canmodifyrequirementlevelsettings"
},
"SchemaName": *\<\<Your chosen schema name\>\>*
"@odata.type": "Microsoft.Dynamics.CRM.StringAttributeMetadata",
"FormatName": {
"Value": *\<\<Formatname value\>\>*
},
"MaxLength": 100
}

Format and FormatName columns

Format validations