PL400 : configure columns (Implement tables and columns)

Columns are commonly known in architecture and structural engineering as pillars that support houses and walls. In architecture, "column" refers to a structural element that also has some proportionate and decorative features. This is how columns in database tables work. Columns are a way to store a discrete piece of information within a record in a table. 

In the Dataverse, Columns define the individual data items that can be used to store information in a table. 

There are three type of column in the Dataverse:

1. Standard Column
2. Managed Column
3. Custom Column

Standard Column:

When we create the table, 19 columns are created by the system and those columns cannot be deleted. All these columns are managed by the system.

Managed Column:

Managed Solutions allow the developer to use Managed Properties to "lock down" certain features or definitions of objects in CRM to prevent end users from introducing problems or altering the product in an undesired way.

Custom Column: 

Custom column is part of customization which is defined by developer or customizer. It has the prefix that is set in the solution publisher.

Dataverse allows the user to select out-of-box features, some of them after the column is created, but the features are restricted after the column creation, so the user/developer has to use those features as per the business requirements. Those selected attributes determine the behavior of the column in the business environment.

Display Name:

Display Name is visible to user, so it is always user friendly. It can be changed after the creation of column too.

Name:

The column name is the unique name in the whole environment. A name will be generated based on the display name. Once a column is created the name cannot be changed as it can be referenced in application or code.

The name has a prefix that is automatically generated by the system. The purpose of this prefix is ​​to give your objects a unique name if you want to import them in the future in another environment (which will have a different prefix).

Data type:

The data type of a column controls how values are stored as well as how they are formatted in some applications. Once a column is saved, we cannot change the data type with the exception of converting text columns to auto number columns.

Required

Whether data is required in the column to save the record. 

Optional: The record can be saved even if there is no data in this column.

Recommended: The record can be saved even if there is no data in this column. However, a symbol appears next to the column in model-driven apps to indicate it is important.

Required: The record can’t be saved if there is no data in this column.

Searchable

When a column is searchable it appears in Advanced Find in model-driven apps and is available when customizing views. De-selecting this will reduce the number of options shown to people using advanced find.

Calculated or Rollup

Calculated columns: Use to automate manual calculations. Use values, dates, or text.

Examples

Full name: First name + last name

Net worth: Assets subtracted by the liabilities for a given account

Rollup columns: Use to get an aggregate value calculated over the records related to a specific record, similar to using sum, min, max and count in Microsoft Excel.

Examples

Open opportunities: all open opportunities related to an account.

Total open cases: the sum of all cases that are open to a linked account.

Description

Enter instructions to the user about what the column is for. These descriptions appear as tooltips for the user in model-driven apps when they hover their mouse over the label of the column.

Enable Column security

Whether the data in the column is secured at a higher level than the table.

Enable Auditing

Track changes in Dataverse data for security and analytical purpose.

Appears in global filter in interactive experience dashboard

Determines whether this column appears as an available filter when using interactive experiences dashboards.

Sortable in interactive experience dashboard

Determines whether data can be sorted by this column when using interactive experience dashboards.

Max Length

This option provides length of data in the table.

IME Mode

An IME lets you enter and edit Chinese, Japanese, and Korean characters. IMEs can be in an active or inactive state. The active state accepts Chinese, Japanese, or Korean characters. The inactive state behaves like a regular keyboard and uses a limited set of characters.

Active: This value will activate the IME initially. You can deactivate it later if desired. This is the default IME setting.

Auto: When IME mode is auto, Power Apps will not interfere with the IME.

Disabled: Disable IME mode to bypass the IME. This can be useful for entering alphanumeric characters in certain languages.

Inactive: Power Apps will deactivate the IME initially. You can activate it later if desired.

Data type:

The data type is one of the important characteristics of the column which decides the behavior and storage type of the column. The data type of a column controls how values ​​are stored as well as how they are formatted in some applications. Once the column is saved, we cannot change the data type with the exception of changing the text column to an auto number column. It is divided into different sections according to the behavior of the data type.

Text - A text value that is intended to be displayed in a single-line text box.

Text Area - A text value that is intended to be displayed in a multi-line text box. If we need more than 4,000 characters, use a multiline text data type.

Email - A text value that is validated as an email address and presented as a link to mail in the column.

URL - A text value that is valid as a URL and is presented as a link to open the URL.

Ticker Symbol - A text value for a ticker symbol that will display a link that will open to show a quote for the stock ticker symbol.

phone - A text value that is validated as a phone number, which is presented as a link to initiate a phone call using Skype.

Whole Number - A number value presented in a text box.

Duration - A number value presented as a drop-down list containing the time interval. A user can select a value from the list or type an integer value that represents the number of minutes.

Time Zone - A number value presented as a drop-down list containing a list of time zones.

date and time - date and time values.

Date Only - A date and time value that displays only one date. The time value is stored in the system as 12:00 AM (00:00:00).

Language - A number value presented as a drop-down list that lists the languages ​​that are enabled for the environment. If no other language has been enabled, the native language will be the only option. The saved value is the Locale Identifier (LCID) value for the language.

Currency - A money value for any currency configured for the environment. we can set a level of accuracy, or we can choose to base the accuracy on a single standard of precision used by a specific currency or organization.

Decimal Number - A decimal value with up to 10 digits of precision. 

Floating Point Number - A floating point number with up to 5 digits of precision. 

Image - Displays a single image for each record in the application. Each table can have an image column. The name you entered while creating the image column will be ignored. The image column is always named table image. we can only have one image column for each table.

Lookup - Creates a single row reference to a single target row type.

Options - Displays a list of options where more than one option can be selected.

Multiline Text - A text value that is intended to be displayed in a multi-line text box. This value is limited to a maximum of 1,048,576 characters. we can also set a lower maximum length.

Options - Displays a list of options where only one can be selected.

Yes/No - Displays two options where only one can be selected. You can choose which labels to display for each option. The default values ​​are yes and no.

*A ticker symbol or stock symbol is an abbreviation used to uniquely identify publicly traded shares of a particular stock on a particular stock market.