Column types in Microsoft Dataverse
Tables contain columns. When creating a column, you select the column's data type. The data type determines the values that can be stored within that column. All columns have one and only one data type.
Dataverse has numerous data types for columns in a table. Some data types have formats that affect how the column is displayed in apps. Each data type has its own attributes that can be configured to provide more customization to the column.
The column types that are available in Microsoft Dataverse are categorized here:
Text
Text columns contain alphanumeric text strings and also provide specific formatting.
Single line of text
A text value intended to be displayed in a single-line text box. Single line of text has several formats:
Plain text or Text - Free format text string displayed in a single line text box.
Text area - A text value intended to be displayed in a multi-line text box. If you require more than 4,000 characters, use the Multiple lines of text data type.
Rich text - A formatted text value intended to be displayed in a multi-line text box. If you require more than 4,000 characters, use the Multiple lines of text data type.
Email - A text value that is validated as an email address and rendered as a mailto link in the column.
Phone number - A text value that is validated as a phone number rendered as a link to initiate a phone call by using Skype.
Ticker Symbol - A text value for a ticker symbol that displays a link that opens to show a quote on MSN Money for the stock ticker symbol.
URL - A text value that is validated as an http or https URL and rendered as a link to open the URL.
The maximum length of a single line of text column is 4,000 characters, the default length is 100 characters. The length of a single line of text column can be changed before and after the column is created.
Note
You can edit a single line of text column and change its data type to an autonumber column.
Multiple lines of text
A text value intended to be displayed in a multiple-line text box. Multiple lines of text have two formats:
Plain text or Text - Free format text string displayed in a multi-line text box.
Rich text - A formatted text value intended to be displayed in a multi-line text box.
The maximum length of a multiple lines of text column is 1,048,576 characters, the default length is 2,000 characters. The length of a multiple lines of text column can be changed before and after the column is created.
Number
Number columns store numeric values. There are several numeric data types. For more information, see Using the right type of number.
Whole number
Whole number columns store integer values. Whole number has several formats:
None - A number value presented in a text box.
Duration - A number value presented as a drop-down list that contains time intervals. A user can select a value from the list or type an integer value that represents the number of minutes.
Timezone - A number value presented as a drop-down list that contains a list of time zones.
Language - A number value presented as a drop-down list that contains a list of languages that are enabled for the environment. If no other languages are enabled, the base language is the only option. The saved value is the Locale Identifier (LCID) value for the language.
By default, a whole number column has a minimum value of -2,147,483,648 and a maximum value of 2,147,483,647. The minimum and maximum values can be configured. You can set the Size of number property for a whole number column to Big and it allows for values between -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807.
Decimal
A decimal value with up to 10 decimal places. Decimal numbers are stored in the database exactly as specified.
By default, a decimal column has a minimum value of -100,000,000,000 and a maximum value of 100,000,000,000. The minimum and maximum values can be configured.
Float
A floating point number with up to five decimal places. You should only use float when necessary as floating point numbers store a close approximation of the value and several Dataverse features don't support float such as calculated columns.
By default, a float column has a minimum value of 0 and a maximum value of 1,000,000,000. The minimum and maximum values can be configured.
Currency
The currency data type stores money values for any currencies that are configured for the environment.
When you add a currency column, four columns are added to the table:
Currency - A numeric column containing the money value for the selected currency.
Currency(Base) - A read-only column contain the money value converted to the base currency set when creating the environment.
Currency - A lookup column that users can set to any active currency configured for your environment.
Exchange rate - A decimal column that provides the exchange rate used for the selected currency.
You can set a level of precision, or you can choose to base the precision on a specific currency or a single standard precision that is used by the organization.
By default, a currency column has a minimum value of -922,337,203,685,477 and a maximum value of 922,337,203,685,477. The minimum and maximum values can be configured.
Date and time
The Date and time column stores date and time values. In Microsoft Dataverse, you can specify how date and time values are shown to users and how they're adjusted for time zones.
Two options are available for date and time columns.
Format: Whether to display the time portion of the value.
Time zone adjustment: Whether to adjust values for time zones.
Format
All date and time columns have a time portion. Format determines whether to display the time portion of the value.
Date and time: Displays the date and time of the value.
Date only: Displays the date portion of the value only.
Time zone adjustment
Dataverse stores all date and time values in UTC time zone. When your app displays values or processing values entered by users, Dataverse and model-driven apps can adjust for the user's time zone with these Behavior options.
User local: Adjust values for the user's time zone. This is the default behavior. You can change it once to another behavior.
Date only: No time zone conversion. The time value is stored as 12:00 AM (00:00:00) in the system.
Time zone independent: No time zone conversion.
The user's time zone is set in the user's personal options, and doesn't use the system time zone in Windows, Android, iOS, or macOS.
Usage guidelines
Use Time zone independent behavior when time zone information isn't required, such as hotel check-in times. With this selection, users in all time zones see the same date and time value.
Use Date only behavior when information about the time of the day and the time zone isn't required, such as birthdays or anniversaries. With this selection, users in all time zones see the exact same date value.
Time zone independent behavior with Date only format is practically the same as Date only behavior. Use the former if you aren't sure whether you need the time portion in the future.
Reference
There are two types of lookup column that you can create:
Lookup - A column that links two tables together in a many-to-one relationship. A lookup column creates a reference to a single row in the related table.
Customer - A lookup column that you can use to specify a customer, which can be either an account or a contact. Adding a lookup column to a table creates two many-to-one relationships from the table to the account and contact tables
Choice
A choice is a type of column that defines a set of options. When a choice is displayed in a form, it uses a drop-down list control. Choice columns are also known as picklists and option sets.
Choice columns are key/value pairs contain items with an integer value and a label.
Choice column
A choice column displays a list of options where only one option can be selected.
When creating a choice column, you can specify Sync with global choice. It has two options:
Global - A global choice is a separate component and can be reused for multiple columns on multiple tables. The list of choices is shared for the table columns that use the global choice
Local - A local choice only exists for the table column.
Choices
You can configure a Choice column to allow selection of multiple options. It can't be changed after the column is created.
Yes/no
A Yes/no column is a boolean that has only two options. You can choose which labels are displayed for each option. The default labels are Yes and No.
Note
Labels in choice and yes/no columns are supported for localization when language packs are enabled for the environment.
File
Dataverse provides two data types for storing documents and images:
- File - A column that can store files up to a specified maximum size. The default maximum file size is 32,768. The maximum size must be between 1 KB and 131,072 KB.
- Image - Displays a single image for each record in the application. You can optionally select an image column to be the primary image for the table and this image is displayed in the top left of a model-driven app form. The default maximum image size is 10240. The maximum size must be between 1 KB and 30,720 KB.
Autonumber
Autonumber columns automatically generate alphanumeric strings whenever they're created. You can customize the format of these columns and then rely on the system to generate matching values that automatically fill them in.
Automnumber columns are covered later in this module.
Behavior
When creating a column, you can specify its Behavior. Behavior has three options:
Simple - A column that users can enter data or select an option.
Calculated - A read-only column whose value is calculated from other column values.
Rollup - A read-only column that is calculated by aggregating values from table rows and columns in a one-to-many relationship.
Note
Not all data types are supported by calculated and rollup columns.
Formula (PowerFx)
Formula columns used the Power Fx language to calculate the value for the column. Formula columns are read-only.
You should use Formula columns instead of calculated columns.
System data types
There are several data types for table columns but that you can't use when creating columns.
Unique Identifier -A system column stores a globally unique identifier (GUID) value for each row.
Owner - Allows for a single reference to either a team or a user row. All team or user-owned tables have one of these columns.
PartyList - Allows for multiple references to multiple tables. These lookups are found on the Email table To and Cc columns. They're also used in the Phone and Appointment tables.
Regarding - allows for a single lookup reference to multiple tables. These lookups are found in the regarding column used in activities
Status - A system column that has options that generally correspond to active and inactive status. Some system tables have more options, but all custom tables have only Active and Inactive status options.
Status Reason - A system column that has options that provide more detail about the Status column. Each option is associated with one of the available Status options. You can add and edit the status reason options.
Tip
You can add any combination of columns to a custom or standard table to meet your needs, but you can't delete a standard column from a standard table.