In Dynamics CRM and as well as Microsoft Model Driven apps when creating a database there are several different types of attributes to pick from. Sometimes its easy to pick the right type, but there are some nuances between a few of them. Below is compiled list of the different data types available specifically in CRM and notes about some of nuances of them.
Use numbers and dates if you can. Numbers and dates can be searched better and be used in calculations. Storing dates as text like “Thursday” is not that useful for a report you might want later.
Use options sets and lookups when you want the user to pick from a list of options. Option sets are best used for giving the user a quick list of choices (I recommend less than ~30) that do not change often. So an example option set of relation types would be: spouse, uncle, aunt, and child. Lookups are better for data that has a lot of choices, or is changing frequently, or is from another entity/table in CRM.
Below are the data types available in CRM in greater detail.
Text
| Name | Description | Notes |
| Single Line of Text or Simple Text |
This field can contain up to 4,000 text characters. You can set the maximum length to be less than this. This field has several format options that will change the presentation of the text. These options are Email, Text, Text Area, URL, Ticker Symbol, and Phone. More information: More information: Single line of text format options Search Filters: Equals, Does Not Equal, Contains, Does Not Contain, Begins With, Does Not Begin With, Ends With, Does Not End With, Contains Data, Does Not Contain Data |
This is the #1 type. Best for holding some amount of text that does not need line breaks. Best used when you are collecting a single piece of information like a person’s first name or an account’s favorite food. Dev Note: blank text will automatically be converted to NULL |
| Multiple Lines of Text | This field can contain up to 1,048,576 text characters. You can set the maximum length to be less than this. When you add this field to a form, you can specify the size of the field. Search Filters: Contains, Does Not Contain, Begins With, Does Not Begin With, Ends With, Does Not End With, Contains Data, Does Not Contain Data |
This field is meant for lengthy text blocks, like descriptions or write-ups. It does not natively support any rich text features but internally is used by CRM to hold HTML and XML in some places. Search performance is also a bit slower than single line of text. Use single line of text unless you need to have more than 4,000 characters. “Equals” search is not available. Dev Note: blank text will automatically be converted to NULL |
Number
| Name | Description | Notes |
| Whole Number | Integers with a value between -2,147,483,648 and 2,147,483,647 can be in this field. You can restrict the maximum or minimum values in this range. This field has format options None, Duration, Time Zone, and Language that change depending on how the field is presented. More information: Whole number format options Search Filters: Equals, Does Not Equal, Is Greater Than, Is Greater Than or Equal To, Is Less Than, Is Less Than or Equal To, Contains Data, Does Not Contain Data |
A very commonly used number type that does NOT support decimals. Supports calculations. |
| Floating Point Number | Up to 5 decimal points of precision can be used for values between -100,000,000,000 and 100,000,000,000 can be in this field. You can specify the level of precision and the maximum and minimum values. More information: Using the right type of number Search Filters: Equals, Does Not Equal, Is Greater Than, Is Greater Than or Equal To, Is Less Than, Is Less Than or Equal To, Contains Data, Does Not Contain Data |
A very commonly used number type that does support decimals. Supports calculations. I generally recommend using decimal numbers. |
| Decimal Number | Up to 10 decimal points of precision can be used for values between -100,000,000,000 and 100,000,000,000 can be in this field. You can specify the level of precision and the maximum and minimum values. More information: Using the right type of number Search Filters: Equals, Does Not Equal, Is Greater Than, Is Greater Than or Equal To, Is Less Than, Is Less Than or Equal To, Contains Data, Does Not Contain Data |
A very commonly used number type that does support decimals. Supports calculations. |
| Currency | Monetary values between -922,337,203,685,477 and 922,337,203,685,477 can be in this field. You can set a level of precision or choose to base the precision on a specific currency or a single standard precision used by the organization. More information: Using Currency Fields Search Filters: Equals, Does Not Equal, Is Greater Than, Is Greater Than or Equal To, Is Less Than, Is Less Than or Equal To, Contains Data, Does Not Contain Data |
A commonly used field to hold currency (dollars, pesos, RMB, etc). Do use this field to collect currency data but do not use it for other types of numbers. This field has some side-affects on the record. Adding a single currency field will also add a Lookup field to the currency table automatically. It will also add internally two decimal like fields that hold a) the amount as the user wrote in which will be in the currency that is in the lookup field b) the calculated amount in the system’s base currency. Most people don’t need this extra field, but it is useful when doing multi-currency work and then being able to do reporting off just one currency. Supports calculations. |
Date
| Name | Description | Notes |
| Date and Time | This field has format options to display Date Only or Date and Time. Search Filters: On, On or After, On or Before, Yesterday, Today, Tomorrow, Next 7 Days, Last 7 Days, Next Week, Last Week, This Week, Next Month, Last Month, This Month, Next Year, Last Year, This Year, Last X Days, Next X Days, Last X Weeks, Next X Weeks, Last X Months, Next X Months, Last X Years, Next X Years, Any Time, Older Than X Days, Older Than X Weeks, Older Than X Months, Older Than X Years, Contains Data, Does Not Contain Data, In Fiscal Year, In Fiscal Period, In Fiscal Period and Year, In or After Fiscal Period, In or Before Fiscal Period, Last Fiscal Year, This Fiscal Year, Next Fiscal Year, Last X Fiscal Years, Next X Fiscal Years, Last Fiscal Period, This Fiscal Period, Next Fiscal Period, Last X Fiscal Periods, Next X Fiscal Periods |
Field can be set to be just a date or a date and time. In the main UI the time should always be converted into the user’s time zone (base on their user preferences). Internally the time is saved as GMT/UTC time. Supports calculations. |
Option Set / Lookup
| Name | Description | Notes |
| Option Set | This field provides a set of options. Each option has a number value and label. When added to a form, this field displays a control for users to select only one option. When this field is displayed in Advanced Find, users can use a picklist control to select multiple options to include in their search criteria. You can define a single, global option set and configure Option Set fields to use that single set of options. More information: Creating and editing global option sets Search Filters: Equals, Does Not Equal, Contains, Does Not Contain, Begins With, Does Not Begin With, Ends With, Does Not End With, Contains Data, Does Not Contain Data |
These are fixed drop-downs where you can only pick one item at a time. The global version can be reused between multiple entities/tables while the other is unique to that particular entity/table. See more details here For the Greater Good! Pick Lists I recommend that when in doubt, use global. |
| MultiSelect Option Set | This field provides a set of options, where multiple options can be selected. When added to a form, this field uses a control for users to select multiple options. When this field is displayed in Advanced Find, users can select multiple options from the list to include in their search criteria. You can define a single global option set and configure MultiSelect Option Set fields to use that single set of options. More information: Creating and editing global option sets Search Filters: NOT SEARCHABLE |
Basically the same as the Option Set except you can pick multiple. However while this is an elegant type it has problems because it is not searchable and not available in workflow or bulk edits. Use with caution. See more details here For the Greater Good! Pick Lists. |
| Two Options | This field provides two options. Each option has a number value of 0 or 1 corresponding to a false or true value. Each option also has a label so that true or false values can be represented as “Yes” and “No”, “Hot” and “Cold”, “On” and “Off” or any pair of labels you want to display. Two option fields don’t provide format options at the field level. But when you add one to the form you can choose to display them as radio buttons, a check box, or a select list. Search Filters: Equals, Does Not Equal, Contains, Does Not Contain, Begins With, Does Not Begin With, Ends With, Does Not End With, Contains Data, Does Not Contain Data |
You can consider this like a checkbox field. Technically there are three values: blank, true, and false. You can label what you want true and false to be. Typically shown as a toggle or radio buttons. |
| Lookup | A field that allows setting a reference to a single record of a specific type of entity. Some system lookup fields behave differently. More information: Different types of lookups Search Filters: Equals, Does Not Equal, Contains, Does Not Contain, Begins With, Does Not Begin With, Ends With, Does Not End With, Contains Data, Does Not Contain Data |
This is the core field of any relational database, it is essentially a foreign key in the database world. This type of record ties two records together. Internally its stored as a guid. |
| Customer | A lookup field that you can use to specify a customer, which can be an account or contact. Search Filters: Equals, Does Not Equal, Contains, Does Not Contain, Begins With, Does Not Begin With, Ends With, Does Not End With, Contains Data, Does Not Contain Data |
This is a “complex” lookup field that allows a connection to either an account or contact record. |
| Regarding | Search Filters: Equals, Does Not Equal, Contains, Does Not Contain, Begins With, Does Not Begin With, Ends With, Does Not End With, Contains Data, Does Not Contain Data | This is a “complex” lookup field that allows a connection to ANY type of record. These are not available to be added to entities/tables but are built into several of the stock entities/tables such as activities and cases. |
Special
| Name | Description | Notes |
| Owner | A system lookup field that references the user or team that is assigned a user or team owned entity record. Search Filters: Equals Current User, Does Not Equal Current User, Equals Current User Or Their Reporting Hierarchy, Equals Current User and Their Teams Or Their Reporting Hierarchy And Their Team, Equals Current User’s Teams, Equals Current User Or User’s Teams, Equals, Does Not Equal, Contains, Does Not Contain, Begins With, Does Not Begin With, Ends With, Does Not End With, Contains Data, Does Not Contain Data |
This is a system created field and cannot be manually added to a entity/table. Entities/tables marked as “owned” will get this field. It is essentially a lookup field that can either point to a user or a team in the system. CRM security is generally based around this field. |
| Unique Identifier | A system field stores a globally unique identifier (GUID) value for each record. | This is the unique record id. All tables have a field create automatically to store this value. This is the value that lookups use to relate two records together. CRM tries hard to keep this field invisible to the user. Only directly visible and searchable in APIs. Behind the scenes using a lookup is selecting this value. |
| Status | A system field that has options that generally correspond to active and inactive status. Some system attributes have additional options, but all custom attributes have only Active and Inactive status options. You can also include custom state transitions to control which status options are available for certain entities. More information: Define status reason transitions Search Filters: Equals, Does Not Equal, Contains, Does Not Contain, Begins With, Does Not Begin With, Ends With, Does Not End With, Contains Data, Does Not Contain Data |
You cannot create fields of this type, but every entity/table is automatically given one. When a record is inactive it will be locked and readonly to every user. You have to make the record active again to make any updates to it. |
| Status Reason | A system field that has options that provide additional detail about the Status field. Each option is associated with one of the available Status options. You can add and edit the options. Search Filters: Equals, Does Not Equal, Contains, Does Not Contain, Begins With, Does Not Begin With, Ends With, Does Not End With, Contains Data, Does Not Contain Data |
This goes hand in hand with the Status field. Also added automatically to every entity/table. this give you the ability to make “sub statuses”. So a Status of Active can be broken down into status reasons like “Draft” and “Processing”. A status of Inactive could be broken down into status reasons like “Won”, “Lost”, and “Closed”. |
Files
| Name | Description | Notes |
| Image | Each entity that supports images can have one image field. When an entity has an image field, it can be configured to display the image for the record in the application. More information: Image fields Search Filters: Not searchable |
Must be created with the UI or API, cannot be imported or exported via Excel |
| File | Search Filters: Not searchable | Must be created with the UI or API, cannot be imported or exported via Excel. This is not listed in the MS docs but does appear in CRM. |
More information found here: Types of fields and field data types
No responses yet