Επεξεργασία

Κοινή χρήση μέσω


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:

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:

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

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