1.3 Column Types, Formats, Primary Keys & Alternate Keys

Column Types, Formats, Primary Keys & Alternate Keys

Columns (formerly attributes) define the discrete data elements stored within Dataverse tables. As a Power Platform Functional Consultant, selecting the precise column data type, format, and behavior is critical. Incorrect data type selection can introduce numerical rounding discrepancies, timezone conversion errors, or integration bottlenecks with external enterprise systems. This section covers core data types, date/time mechanics, polymorphic lookups, and Alternate Keys.

---

1. Column Data Types & Formats

Dataverse categorizes columns into fundamental primitive types, each offering specialized format controls.

+-----------------------------------------------------------------------------+
|                         DATAVERSE COLUMN DATA TYPES                         |
|                                                                             |
|   [TEXT TYPES]             [NUMERICAL TYPES]         [TEMPORAL TYPES]       |
|   - Single Line (4K)       - Whole Number            - User Local           |
|   - Multiline (1MB)        - Decimal (Fixed-point)   - Date Only            |
|   - Rich Text (HTML)       - Float (Scientific)      - Time-Zone Independent|
|                            - Currency (Dual-field)                          |
|                                                                             |
|   [OPTION TYPES]           [LOOKUP TYPES]            [IDENTIFIERS]          |
|   - Choice (Single)        - Standard 1:N Lookup     - Primary Key (GUID)   |
|   - Choices (Multi-select) - Customer (Polymorphic)  - Alternate Keys (1-5) |
+-----------------------------------------------------------------------------+

Text Types

• Single Line of Text: Stores alphanumeric text up to 4,000 characters (default: 100). Formats include: Plain Text, Email, URL, Ticker Symbol, Phone, and Text Area.
• Multiline Text (Memo): Stores plain text up to 1,048,576 characters (1 MB). Used for lengthy descriptions, notes, and unformatted logs.
• Rich Text: Enables rich formatting (bold, italics, headings, bulleted lists, hyperlinking, and embedded images) stored as HTML content.

Numerical Types & Precision Models

Choosing the correct numerical type is vital for mathematical fidelity:

• Whole Number (Integer): Stores integers from -2,147,483,648 to 2,147,483,647. Formats include: None, Duration (stored as minutes, displayed in human-readable increments like '15 minutes' or '2 hours'), Time Zone (displays drop-down list of world time zones), and Language (LCID).
• Decimal Number: Stores fixed-point numbers with exact precision (up to 10 decimal places, range from -100,000,000,000 to 100,000,000,000). Best For: Financial ledgers, currency sub-units, tax calculations, and precise engineering dimensions where rounding errors cannot be tolerated.
• Floating Point (Float): Stores numbers using the IEEE 754 binary floating-point standard with up to 5 decimal places. Best For: Scientific measurements and statistical metrics where fractional approximations are acceptable.
• Currency: Represents monetary values. When a Currency column is created, Dataverse automatically provisions two database columns:
  1. <ColumnName>: Stores the value in the transaction currency selected on the record.
  2. <ColumnName>_base: Stores the calculated equivalent in the organization's base currency, calculated via the ExchangeRate column against the linked TransactionCurrency record.

---

2. Date and Time Behaviors

Date and Time columns in Dataverse support three distinct Behaviors. Understanding these behaviors is essential for managing multi-national enterprise deployments.

+-----------------------------------------------------------------------------+
|                      DATE AND TIME BEHAVIOR COMPARISON                      |
|                                                                             |
|   [USER LOCAL]                                                              |
|   - Stored in UTC in SQL database.                                          |
|   - Converted to user's personalized time zone in UI / apps.                |
|   - Example: Follow-up Appointment (8:00 AM NY = 1:00 PM London).           |
|                                                                             |
|   [DATE ONLY]                                                               |
|   - Stores calendar date (YYYY-MM-DD) without time or time zone offset.     |
|   - Identical calendar date across all world time zones.                    |
|   - Example: Birthdays, Employment Start Dates, Anniversaries.              |
|                                                                             |
|   [TIME-ZONE INDEPENDENT]                                                   |
|   - Stores exact date and time (YYYY-MM-DD HH:MM).                          |
|   - No UTC conversion applied; renders identical time in all zones.         |
|   - Example: Hotel Check-in (15:00), International Flight Schedule.         |
+-----------------------------------------------------------------------------+

[!CAUTION] One-Way Behavior Conversion: You can transition an existing column from User Local to Date Only or Time-Zone Independent. However, this is a ONE-WAY conversion. Once converted to Date Only or Time-Zone Independent, you can NEVER revert the column back to User Local.

---

3. Choice, Choices & Lookups

Choice (Single Select) vs. Choices (Multi-Select)

• Choice (Option Set): Allows the user to select exactly one option from a predefined list. Stored in the database as an integer value.
• Choices (Multi-Select Option Set): Allows the user to select one or multiple options. Stored as a comma-separated string of integers using bitmask evaluation.
• Local vs. Global Choices:
  • Local Choice: Exists exclusively within the specific table/column.
  • Global Choice: Maintained centrally in the solution and reused across multiple tables and forms (e.g., standard Country lists, Industry classifications).
• Choices Limitations: Multi-select Choice columns cannot be used as Alternate Keys, cannot be used as a Primary Name column, have limited filtering capabilities in classic workflows, and require specialized Microsoft.Dynamics.CRM.ContainValues operators in Web API / OData queries.

Lookups & The Polymorphic Customer Column

• Standard Lookup: A 1:N foreign key pointing to exactly one target table (e.g., Parent Account points to Account).
• Customer Column: A specialized polymorphic lookup that can reference either an Account OR a Contact within a single column. The underlying system automatically maintains the target entity type.

---

4. Alternate Keys & Integration Architecture

In standard Dataverse operations, records are uniquely identified by their 128-bit GUID primary key. However, when integrating Dataverse with external enterprise systems (such as SAP, Dynamics 365 Finance, Salesforce, or legacy SQL databases), the external system rarely knows the internal Dataverse GUID.

+-----------------------------------------------------------------------------+
|                        ALTERNATE KEY UPSERT WORKFLOW                        |
|                                                                             |
|   [EXTERNAL SYSTEM] ---> PATCH /api/data/v9.2/accounts(cr123_erpnumber='E9')|
|                                     |                                       |
|                                     v                                       |
|                           [DATAVERSE ENGINE]                                |
|                                     |                                       |
|                   +-----------------+-----------------+                     |
|                   |                                   |                     |
|                   v                                   v                     |
|           [RECORD EXISTS?]                     [RECORD NOT FOUND?]          |
|                   |                                   |                     |
|                   v                                   v                     |
|        [UPDATE EXISTING RECORD]             [CREATE NEW RECORD]             |
+-----------------------------------------------------------------------------+

Alternate Key Mechanics

• Definition: An Alternate Key allows a consultant to designate 1 to 5 columns on a table to act as a unique natural business key (e.g., AccountNumber, SSN, or a composite key of StoreNumber + ProductSKU).
• Supported Data Types: Single Line of Text, Whole Number, Decimal Number, and Date and Time (User Local / Date Only).
• Database Indexing: When an alternate key is created, Dataverse initiates an asynchronous background database job to create a unique clustered index on the underlying SQL table.
• Status Lifecycle: Pending -> In Progress -> Active (or Failed if duplicate data already exists in the table).

The Upsert Operation

Alternate keys enable Upsert operations via the Dataverse Web API and OData endpoints:

• Instead of making two API calls (first querying by business key to retrieve the GUID, then updating or creating), the integration sends a single PATCH request using the alternate key.
• If a record with that alternate key value exists, Dataverse updates it. If it does not exist, Dataverse creates it.

Alternate Key Constraints

• Maximum Count: A table can have a maximum of 10 active alternate keys.
• Uniqueness Enforcement: Once an alternate key is Active, Dataverse strictly enforces uniqueness. Any attempt by a user or automated flow to insert a duplicate value will throw a database constraint error.