Column definitions

Tables include a collection of columns that represent the data that can be included within each record. Developers need to understand the different types of columns and how to work with them.

Columns names

Like tables, each column has a unique name defined when it's created. This name is presented in several ways:

Name	Description
SchemaName	Typically, a Pascal cased version of the logical name. For example: AccountNumber
LogicalName	All lower-case name. For example: accountnumber

When you create a custom column, the name you choose is prepended with the customization prefix value of the solution publisher associated with the solution that the column was created within.

You can't change the names of a column after it's created.

Each column also has two properties that can display localized values. The values of these properties are used to refer to the columns in an app.

Name	Description
DisplayName	Typically, the same as the schema name, but can include spaces. For example: Account Number
Description	A short sentence describing the column or providing guidance to the user. For example: Type an ID number or code for the account to quickly search and identify the account in system views. In model-driven apps, this information appears when users hover over the column in a form.

These are localizable values that are used to refer to the columns in an app. These values can be changed at any time. To add or edit localized values see Translate customized table, form, and column text into other languages.

Column types

The AttributeTypeName property describes the type of a column. This property contains a value of type AttributeTypeDisplayName, which provides a label for each the different types of columns that exist.

Note

Don't be confused by the AttributeType property. The values in this older property are mostly aligned with AttributeTypeName except that it shows ImageAttributeMetadata and MultiSelectPicklistAttributeMetadata as Virtual. Refer to the AttributeTypeName property rather than the AttributeType property.

In the following table:

• These types are grouped by category for comparison.
• For each AttributeTypeDisplayName value, the corresponding .NET assembly AttributeMetadata derived class is included where available. There isn't a 1:1 relationship between these types and the AttributeTypeDisplayName label.
• Those column types that can be created as custom columns include the corresponding label displayed in the UI.

Category	AttributeTypeDisplayName/ AttributeMetadata Type	Can Create/ Label	Description
Categorization	BooleanType BooleanAttributeMetadata	Yes Two Options	Contains the selected value from Yes/No that usually indicate a true or false value. More information: Choice
Categorization	EntityNameType EntityNameAttributeMetadata	No	Contains a value that corresponds to a table in the system. For internal use only.
Categorization	MultiSelectPicklistType MultiSelectPicklistAttributeMetadata	Yes MultiSelect Option Set	Contains multiple selected values where multiple values can be selected. More information: Choice Choices columns
Categorization	PicklistType PicklistAttributeMetadata	Yes Option Set	Contains the selected value where one option can be selected. More information: Choice
Categorization	StateType StateAttributeMetadata	No	Contains the value that describes the status of a table record. More information: Choice
Categorization	StatusType StatusAttributeMetadata	No	Contains the value that describes the reason for the status of a table record. More information: Choice
Collection	CalendarRulesType	No	Contains a collection of CalendarRules table records. There are no columns that use this type. When generating a proxy, the code generation tool creates two simulated columns that aren't present in the definition. These columns represent a view of the calendar rules records associated in a one-to-many relationship to the table record.
Collection	PartyListType	No	Contains a collection of ActivityParty table records. More information: ActivityParty table
Date and Time	DateTimeType DateTimeAttributeMetadata	Yes Date and Time	Contains a date and time value. All date and time columns support values as early as 1/1/1753 12:00 AM.
File	FileType FileAttributeMetadata	Yes File	Contains data to support retrieving binary data for a table record. More information: Work with file column definitions using code
Image	ImageType ImageAttributeMetadata	Yes Image	Contains data to support retrieving image data for a table record. More information: Entity Images
Managed Property	ManagedPropertyType ManagedPropertyAttributeMetadata	No	Contains data that describe whether the solution component stored in the table record can be customized when included in a managed solution. More information: Managed Properties
Quantity	BigIntType BigIntAttributeMetadata	Yes	Contains a BigInt value.
Quantity	DecimalType DecimalAttributeMetadata	Yes Decimal Number	Contains a Decimal value. The Precision property sets the level of precision.
Quantity	DoubleType DoubleAttributeMetadata	Yes Floating Point Number	Contains a Double value. The Precision property sets the level of precision.
Quantity	IntegerType IntegerAttributeMetadata	Yes Whole Number	Contains an Int value
Quantity	MoneyType MoneyAttributeMetadata	Yes Currency	Contains a Decimal value. The PrecisionSource property determines where the level of precision is set. 0: The Precision property 1: The Organization.PricingDecimalPrecision column 2: The TransactionCurrency.CurrencyPrecision column in the table record
Reference	CustomerType LookupAttributeMetadata	Yes Customer	Contains a reference to either an account or contact table record.
Reference	LookupType LookupAttributeMetadata	Yes Lookup	Contains a reference to a table record. The logical names of the valid records are usually stored in the Targets property of the column.
Reference	OwnerType LookupAttributeMetadata	No	Contains a reference to either a user or team table record.
String	MemoType MemoAttributeMetadata	Yes Multiple Lines of Text	Contains a string value intended to be displayed in a multi-line textbox.
String	StringType StringAttributeMetadata	Yes Single Line of Text	Contains a string value intended to be displayed in a single-line textbox.
Unique identifier	UniqueidentifierType UniqueIdentifierAttributeMetadata	No	Contains a GUID unique identifier value.
Virtual	VirtualType	No	These columns can't be used in code and can generally be ignored.

Supported operations and form usage for columns

Each column includes boolean properties that describe the kinds of operations it can participate in and how it can be in a form.

Property	Description
IsRequiredForForm	Whether the column must be included in a form
IsValidForCreate	Whether the column value can be set in a create operation
IsValidForForm	Whether the column can be included as a column in a form
IsValidForRead	Whether the column value can be retrieved
IsValidForUpdate	Whether the column value can be set in an update operation

If you try to set a value in a create or update operation for a column that isn't valid for that operation, the value is ignored. If you try to retrieve a column that isn't valid for read, a null value is returned.

Column requirement level

The RequiredLevel property is a Boolean managed property that describes if a column value is required.

This property can have the following values set:

Name	Value	UI Label	Description
None	0	Optional	No requirements are specified.
SystemRequired	1	System Required	Dataverse requires the column to have a value.
ApplicationRequired	2	Business Required	The application requires the column to have a value.
Recommended	3	Business Recommended	It's recommended that the column has a value.

Dataverse only enforces the SystemRequired option for columns created by the system. Custom columns can't be set to use the SystemRequired option. Dataverse doesn't return an error when a column with ApplicationRequired applied doesn't have a value.

Model-driven apps enforce the ApplicationRequired option and use a different presentation for the Recommended option. Creators of custom clients may use this information to require similar validation or presentation options.

Because RequiredLevel is a managed property, as a publisher of a managed solution you can decide whether consumers of your solution are able to change these settings on columns in your solution.

More information: Managed Properties

Formula, rollup, and calculated columns

Formula, rollup, and calculated columns free the user from having to manually perform calculations and focus on their work. System administrators can define a column to contain the value of many common calculations without having to work with a developer. Developers can also use the platform capabilities to perform these calculations rather than within their own code.

More information:

• Define rollup columns that aggregate values
• Define calculated columns to automate calculations
• Formula, rollup, and calculated columns

Column format

The format values for column controls how it's displayed in model-driven apps. Developer of custom apps may use this information to create similar experiences.

Integer formats

Use the Format property with integer columns to display alternate user experiences for this type.

Option	Description
None	Displays a text box to edit a number value
Duration	Displays 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	Displays a drop-down list that contains a list of time zones.
Language	Displays a drop-down list that contains a list of languages that have been enabled for the organization. If no other languages have been enabled, the base language is the only option. The value saved is the LCID value for the language.

String formats

Use the FormatName property with string columns to set values from the StringFormatName to control how the string column is formatted.

Name	Description
Email	The form column validates the text value as an e-mail address and creates a mailto link in the column.
PhoneNumber	The form column contains a link to initiate a phone call by using Skype.
PhoneticGuide	For internal use only.
Text	The form displays a text box.
TextArea	The form displays a text area column.
TickerSymbol	The form displays a link that opens to display a quote for the stock ticker symbol.
URL	The form displays a link to open the URL.
VersionNumber	For internal use only.

Date and time formats

The DateTimeBehavior property controls the behavior for Date and Time columns. There are three options:

Option	Short Description
UserLocal	Stores the date and time value as UTC value in the system.
DateOnly	Stores the actual date value with the time value as 12:00 AM (00:00:00) in the system.
TimeZoneIndependent	Stores the actual date and time values in the system regardless of the user time zone.

Use the Format property control how the value is to be displayed in a model-driven app regardless of the DateTimeBehavior.

Option	Description
DateAndTime	Display the full date and time
DateOnly	Display just the date.

More information: Behavior and format of the Date and Time column

Auto-number columns

You can add an auto-number column for any table. Currently, you can add the column programmatically. There's no user interface to add this type of column. More information: Create autonumber columns

Choice

Columns that display a set of options can reference a set of options defined by the column, or they can reference a separate set of options that can be shared by more than one column. This is useful when values in one column also apply to other columns. By referencing a common set of options, the options can be maintained in one place. Those choice that can be shared are global choice. Those defined within the column are local choice.

Retrieve options data

The SDK for .NET provides request classes you can use to retrieve the options used by a column.

Use the SDK for .NET to retrieve options

Each of the columns with options inherit from EnumAttributeMetadata include an OptionSet Property. This property contains the OptionSetMetadata that includes the options within the Options property.

With the SDK for .NET you can use the following request classes to retrieve information about choice:

Request Class	Description
RetrieveAllOptionSetsRequest	Retrieves data about all global choice
RetrieveAttributeRequest	Retrieves data about a column including any choice
RetrieveMetadataChangesRequest	Retrieves metadata based on a query that can include choice More information: Query schema definitions
RetrieveOptionSetRequest	Retrieves data about a global choice.

More information:

• Create and edit global choices overview
• Sample: Dump choices/picklist information to a file
• Customize choices

Use the Web API to retrieve choice values

The Web API provides a RESTful style for querying choice values. You can retrieve local or global choice by retrieving the column within a table. The following example returns the OptionSetMetadata for the choice included in the Account.AccountCategoryCode property.

GET <organization url>/api/data/v9.2/EntityDefinitions(LogicalName='account')/Attributes(LogicalName='accountcategorycode')/Microsoft.Dynamics.CRM.PicklistAttributeMetadata?$select=LogicalName&$expand=OptionSet

With the Web API, you can also use the RetrieveMetadataChanges Function.

More information: Query table definitions using the Web API > Retrieving attributes

Column mapping

When you create a new table record in the context of an existing table record, you can automatically transfer certain values from the existing table record as default values for the new table record. This streamlines data entry for people using model-driven apps. Application users see the mapped values and can edit them before saving the entity.

For developers creating custom clients, the same behavior can be achieved by using the InitializeFrom message (SDK InitializeFromRequest Class or Web API InitializeFrom Function) to get the entity data with the configured default values set.

More information

• Map table columns
• Customize table and column mappings

Logical columns

Logical columns contain values which are stored in different database tables than other columns in the table. In most cases this internal implementation is not relevant to working with Dataverse. When you use logical columns as sources for a calculated field, the values in the calculated field cannot be sorted. Use the AttributeMetadata.IsLogical property to detect if an attribute is a logical attribute.

See also

Dataverse tables