• Overview: An entity such as "Contact" has fields (attributes) such as First Name, Last Name, Date of Birth, etc.; A CRM admin or customizer can create fields using built-in customization tools (no programming needed). Fields can be placed on one or more forms.
    • The terms "attribute" and "field" are used interchangeably; the term "column" is used when reference the corresponding SQL Server database column
  • Field Properties:
    • Name and Display Name: Each field in CRM requires a name that is unique for the entity. A field has a Logical Name (all lowercase), Schema Name (mixed case) and a Display Name (appears on forms, in views and in Advanced Find).
    • Field Requirement: Three levels: Optional, Business Recommended (blue + symbol), Business Required (red * symbol). Requirement levels are only enforced on forms. Required fields cannot be removed from forms or entities unless level is changed.
    • Searchable: Available for searching in Advanced Find, regardless of whether they are on a form. Set to Yes or No. You can simplify Advanced Find for setting non-important or unused fields to No (not searchable).
    • Field Security: By default, if a user is a member of a security role that lets the user read an entity, the user can read all fields in that entity. Field Security allows for more restrictions.
    • Auditing: When enabled, changes to the field value are audited. Auditing also needs to be enabled for the entity and at the organization level.
    • Description: Appears in the CRM UI (fields) as an infotip (tooltip). On touch devices, user can tap to see the description.
  • Each field has a data type. See the "Field Data types and Properties" table below for the types and their related settings and properties.
  • Once you've create a field of a particular type, you cannot change the field to a different type. You'll need to create another new field and migrate the data to the new field, update forms, views, reports, etc. You also cannot change the field's name.
  • After you create a field, you can later modify the Display Name, Field Requirement, Field Security, Auditing and Description. For the numerical data types, you can change the min/max values and precision.
  • You can edit multiple fields using the Edit Multiple Fields dialog box. You can set the Field Requirement, Searchable and Auditing.
  • Deleting fields: System fields cannot be deleted; Custom fields can be delete but all data is lost; Cannot delete fields referenced by workflows or dialog processes; Cannot delete a field if it's used in a field mapping
  • Tips:
    • When you create a field, create it under the desired solution and publisher -- those one that will give the field the proper name prefix.
    • You can also create fields from the Form Assistant pane on the right side while editing a form.
    • There's no fixed limit of fields for an entity but having several hundred fields for an entity can lead to usability and performance problems (e.g., slow loading forms); Consider creating child entities to store related data.
    • Before making changes to fields, check the field's dependencies so that you can change other items in CRM where necessary. Go to the field properties page and click More Actions > Show Dependencies.
    • On-premises: If the field you create is heavily used in Advanced Find, views, reports, etc. then consider adding a non-clustered index for the field. Run sample queries in SQL to determine whether this will help with performance.
    • You can use functionality in the CRM SDK to bulk-create attributes via code. This can save hundreds of clicks and lead to more rapid and consistent creation of fields.
      • Altriva used this approach to create their Attribute Generator tool. The tool reads field definitions from Excel and programmatically creates fields in CRM.
    • Do not use the word "Status" for the name of a custom field because this can cause problems when importing solutions.

Field Data Types and Properties

These are the data types that you can choose from when defining a new field:
CRM Data Type
Metadata Type Name
SQL Data Type
Description
Space (bytes)
Single line of Text
String
nvarchar(n)
Short text of “n” characters (maximum 4,000).

Formats:
  • Email: Validate e-mail address
  • Text: Single line
  • Text Area: Multi-line
  • URL: Creates a hyperlink
  • Ticker Symbol: Creates a hyperlink
  • Phone: Supports auto-dialing through Microsoft Lync
IME Mode (active or inactive): "Input Method Editor"; Used for entering Chinese, Japanese, and Korean characters.
2 times n
Option Set
Picklist
int
Pick-list of options. Either specific to a field or a global list that is available to all fields (can be used with multiple entities). If the option set might be used for more than one entity then consider creating a global option set.

  • When creating an Option Set field, you can use an existing (global) option set (or create a new local option set), set a default option (or choose "Unassigned"). For each option added, set the label, value (integer) and description. The value CRM assigns is dependent upon the publisher under which the field is being added. If the option set prefix of a Publisher is 54,321 the first option set value that is used will be 543,210,000, then 543,210,001 and so on. The default Option Set Prefix on the Default Solution is 10,000.
  • Create global option sets for use with multiple entities. You can set a different default per entity.
  • When using field mapping for option set fields, make sure the integers for each option lines up (source to target).
  • While entering option set items, you can move them up and down and can sort ascending and descending
  • When option sets are used in charts for sorting, the numerical value is used. In views, the label is used for sorting.
  • If you delete an option set item, the underlying integer is still in the database for each entity record where the value was selected. Before deleting, it's recommended to do one of the following: Use Advanced Find to change the value for the unwanted item to something else; Modify the label with "Do Not Use"; Use a business rule or JavaScript to adjust the value on form load or display a message; Use a real-time workflow to tell the user to take corrective action.
  • Consider using a Lookup (1:N) to a custom entity instead of an Option Set if the options may change significantly over time.
4
Two Options
Boolean
bit
0 or 1. Displays Yes or No; True or False or any other two values.

The labels "Yes" and "No" are set by default; you can change these (e.g., change to "On" or "Off"). A default must be set; a null cannot be stored.

On a form, a Two Options field can appear as two radio buttons (rendered as a toggling field, more suitable for touch displays), a check box or a list. For the check box, the option values are not displayed on the form so it's best to set a descriptive field label (e.g., "Event Confirmed"). The list option is similar to the check box except the user can see the options rather than toggling them.
Up to 1
Whole number
Integer
int
Integer from -2,147,483,648 to +2,147,483,647 (configurable in that range). No decimal point.

Formats:
  • None: Value is displayed as a number.
  • Duration: Options such as 1 minute, 15 minutes, 2 hours up to 3 days. Users can manually enter a value (in minutes, hours or days). The entered value is stored in minutes but displays in the appropriate format (90 appears as 1.5 hours).
  • Time Zone: The field is displayed as a drop-down box that has time zone values.
  • Language: The field is displayed as a drop-down box that has language values (for installed languages). Internally, the value is stored as a number (1033 = English).
4
Floating Point Number
Double
float
Number with up to five decimal places. Range ± 100,000,000,000. Subject to rounding; inexact. Precision (number of digits to the right of the decimal point) can be set from 0 to 5. Don't use this type if exact values and comparisons between these values are necessary.
8
Decimal Number
Decimal
decimal
Number with up to 10 decimal places. Range ± 100,000,000,000. The value is exact; no rounding. Precision can be set from 0 to 10. Use when calculations must be precise.
13
Currency
Money
money
A number representing a currency value with up to four decimal places and in the range ± 922,337,203,685,477.

In SQL Server, adding a field of this type leads to four columns (currency being used, exchange rate, value, base currency value). Subsequent Currency fields only create two fields -- the field to store the value and the base value.

The Precision property has a default value of "Currency Precision". The field will store a value to the same number of decimal places as is set in the organization's currency settings. This can be overridden on each currency field by changing precision from 0 to 4. Alternatively, select Pricing Decimal Precision to use the organization's Pricing Decimal Precision setting.
8
Multiple Lines of Text
Memo
nvarchar(max)
Text that requires more than one line. Up to 1,048,576 characters.
2 times the number of characters used
Date and Time
DateTime
datetime
Stored internally as the Coordinated Universal Time (UTC) date and time. Displayed in recognizable local format and time zone. Formats include "Date Only" and "Date and Time". You can change the display format at any time without data being lost.
8
Lookup
Lookup
uniqueidentifier
A value (GUID) that links to another record. Represents a 1:N relationship. Shown on a form with a button to display list of records from which to choose. If you create a Lookup field for an entity, you can set the Target Record type and Relationship Name. However, it is recommended that you create a 1:N relationship instead of creating a Lookup field so that you can fill-in the other properties for the relationship.
16
Image
Uniqueidentifier
uniqueidentifier
A value (GUID) that links to an image record. This field type is new in CRM 2013. 24 system entities have an image field; 8 are enabled by default so the image appears on forms. You can have only 1 image field per custom entity. The schema name is always "entityimage" (no prefix). Image is shown on a form using a placeholder. User can click to set the image. Uploaded images must be 5,120 KB or less. Supported types/extensions: jpg, jpeg, gif, tif, tiff, bmp and png.
16

Other Types Used Internally in CRM

BigInt: Used mostly for an entity's VersionNumber field.
ManagedProperty: Used for some entities, such as for the iscustomizable field for the ConnectionRole entity.
Owner: This data type defines the owner for an entity record (ownerid). In SQL Server, an owner is stored as a uniqueidentifier (GUID)
PartyList: Used, for example, as the data type for the "to" and "from" fields for the Email entity. Relates to the ActivityParty entity.
Uniqueidentifier: Stores a GUID (uniqueidentifier in SQL); Mostly used as the type of field that stores the primary key value for an entity

Status (statecode)

  • The main state of the record. Most records have two states, active or inactive. Some entities have more than these two states (e.g., Case can have a status of active, resolved or canceled)
    • Custom entities can only have two states: Active or Inactive
  • Attribute metadata type is "State", Schema name is "statecode", Display Name is typically "Status" or ends with "Status" such as "Activity Status".
  • CRM creates this field automatically for custom entities. The field requirement level is always "Business Required".
  • Common label/description for value 0: Active, Open, Incomplete, Draft
  • Common label/description for value 1 or higher: Inactive, Canceled, Completed, Closed
  • In SQL Server, this is an integer column type and the field is set to NOT NULL meaning a field value is always required to save the record.

Status Reason (statuscode)

  • Sets the reason for the status (Why is the record in the state it's in?) E.g., In Progress, Problem Solved
  • Attribute metadata type is "Status", Schema name is "statuscode", Display Name is typically "Status Reason".
  • CRM creates this field automatically for custom entities. The field requirement level is set to "None" for most entities.
  • Each status reason belongs to a status. For each status, you can add new status reasons or edit the label of existing status reasons. You cannot select the integer values. Plan carefully due to sorting with charts.
  • The Status Reason field can be modified similarly to an Option Set field except on certain entities you can't add/change status reason options.

Attribute Metadata Properties

Each attribute in CRM has these metadata properties. See the CRM SDK for more details on these properties.
AttributeOf
Gets the name of that attribute that this attribute extends.
AttributeType
Gets the type for the attribute.
AttributeTypeName
Gets the name of the type for the attribute.
CanBeSecuredForCreate
Gets whether field security can be applied to prevent a user from adding data to this attribute.
CanBeSecuredForRead
Gets whether field level security can be applied to prevent a user from viewing data from this attribute.
CanBeSecuredForUpdate
Gets whether field level security can be applied to prevent a user from updating data for this attribute.
CanModifyAdditionalSettings
Gets or sets the property that determines whether any settings not controlled by managed properties can be changed.
ColumnNumber
Gets an organization specific id for the attribute used for auditing.
DeprecatedVersion
Gets the Microsoft Dynamics CRM version that the attribute was deprecated in.
Description
Gets or sets the description of the attribute.
DisplayName
Gets or sets the display name for the attribute.
EntityLogicalName
Gets the logical name of the entity that contains the attribute.
ExtensionData
Gets or sets the structure that contains extra data.
HasChanged
Gets whether the item of metadata has changed.
IntroducedVersion
Gets a string identifying the solution version that the solution component was added in.
IsAuditEnabled
Gets or sets the property that determines whether the attribute is enabled for auditing.
IsCustomAttribute
Gets whether the attribute is a custom attribute.
IsCustomizable
Gets or sets the property that determines whether the attribute allows customization.
IsManaged
Gets whether the attribute is part of a managed solution.
IsPrimaryId
Gets whether the attribute represents the unique identifier for the record.
IsPrimaryName
Gets or sets whether the attribute represents the primary attribute for the entity.
IsRenameable
Gets or sets the property that determines whether the attribute display name can be changed.
IsSecured
Gets or sets whether the attribute is secured for field level security.
IsValidForAdvancedFind
Gets or sets the property that determines whether the attribute appears in Advanced Find.
IsValidForCreate
Gets whether the value can be set when a record is created.
IsValidForRead
Gets whether the value can be retrieved.
IsValidForUpdate
Gets whether the value can be updated.
LinkedAttributeId
Gets or sets an attribute that is linked between Appointments and Recurring appointments.
LogicalName
Gets or sets the logical name for the attribute.
MetadataId
Gets or sets a unique identifier for the metadata item.
RequiredLevel
Gets or sets the property that determines the data entry requirement level enforced for the attribute.
SchemaName
Gets or sets the schema name for the attribute.

Sample CRM Form with all Data Types

wiki_fields1.png

.