Study guide
1.3 Column Types, Formats, Primary Keys & Alternate Keys
Microsoft Power Platform Functional Consultant PL-200
Track your progress as you read
Finished sections get a check mark.
1 Chapter 1: Dataverse Tables, Relationships & Core Columns
1.1 Dataverse Table Types, Properties & Ownership 1.2 Table Relationships & Cascading Rules 1.3 Column Types, Formats, Primary Keys & Alternate Keys 1.4 Rollup, Calculated & Formula Columns with Power Fx
2 Chapter 2: Dataverse Data Management & Governance
2.1 Dataverse Search, Quick Find & Relevance Indexing 2.2 Auditing Configuration & Log Management 2.3 Duplicate Detection Rules & Settings 2.4 Bulk Record Operations, Data Import, Export & Templates
3 Chapter 3: Dataverse Security Model & Access Control
3.1 Business Units, Ownership Models & Modern BU Matrix 3.2 Security Roles, Privileges & Depth Levels 3.3 Teams Architecture: Owner, Access, and Microsoft Entra (AAD) Group Teams 3.4 Column-Level Security & Hierarchy Security Models
4 Chapter 4: Model-Driven App Fundamentals & Information Architecture
4.1 Model-Driven App Design, Site Map Navigation & Modern App Designer 4.2 Form Types: Main, Quick Create, Quick View & Card Forms 4.3 View Configuration, System Views, Personal Views & Filters
5 Chapter 5: Advanced Model-Driven App Components & Custom Pages
5.1 Custom Pages & Modern Commanding with Power Fx 5.2 Form Component Controls & Specialized UX Elements 5.3 Embedding Canvas Apps in Model-Driven Forms 5.4 Embedding Power BI Reports, Dashboards & Tiles
6 Chapter 6: Canvas Apps Structure, Formulas & Data Operations
6.1 Canvas App Architecture, Screens, Containers & Controls 6.2 Power Fx Formulas, Delegation & Data Source Limits 6.3 Variables (Context, Global), Collections & State Management 6.4 Triggering Power Automate Cloud Flows from Canvas Apps
7 Chapter 7: Power Pages Site Design, Navigation & Content
7.1 Power Pages Design Studio & Site Structure 7.2 Power Pages Templates, Styling & Responsive Layouts 7.3 Web Pages, Navigation Menus & Content Snippets
8 Chapter 8: Power Pages Data Integration, Forms & Web Roles
8.1 Power Pages Lists, Basic Forms & Advanced Forms 8.2 Table Permissions, Web Roles & Page Access Security 8.3 External Identity Providers & Authentication Configuration
9 Chapter 9: Power Automate Cloud Flow Fundamentals & Triggers
9.1 Cloud Flow Types: Automated, Instant, Scheduled & Desktop Overview 9.2 Dataverse Connector Triggers & Operational Filters 9.3 Standard Connectors, Custom Connectors & Connection References
10 Chapter 10: Cloud Flow Actions, Expressions & Logic Controls
10.1 Logic Controls: Conditions, Switches, Apply to Each & Do Until Loops 10.2 Variables, Data Operations & Dynamic Content Manipulation 10.3 Power Automate Expression Language (WDL / Functions) 10.4 Error Handling, Configure Run After & Scope Controls
11 Chapter 11: Business Process Flows & Guided Processes
11.1 Business Process Flow Architecture, Stages, Steps & Data Steps 11.2 Branching Rules & Multi-Entity Business Process Flows 11.3 BPF Action Steps, Workflows & Cloud Flow Execution 11.4 BPF Security Roles, Order & Process Lifecycle
12 Chapter 12: Classic Workflows, Business Rules & Modern Logic Extensibility
12.1 Classic Dataverse Workflows: Real-Time vs. Background (Async) 12.2 Business Rules: Scope & Actions 12.3 Low-Code Plug-ins in Dataverse: Automated vs. Instant 12.4 Troubleshooting Automations, Run History & Analytics
13 Chapter 13: Power Platform Environment Architecture & Security
13.1 Environment Types: Default, Sandbox, Production, Trial, Developer 13.2 Environment Governance, Groups, Routing & Capacity Management 13.3 Data Loss Prevention (DLP) Policies & Connector Classifications
14 Chapter 14: Application Lifecycle Management (ALM) & Solutions
14.1 Unmanaged vs. Managed Solutions & Solution Layering Mechanics 14.2 Solution Components, Dependencies, Publishers & Prefixes 14.3 Exporting, Importing, Upgrading & Patches 14.4 Solution Checker, App Checker & ALM Best Practices
15 Chapter 15: Enterprise Interoperability & Service Integration
15.1 Exchange / Server-Side Synchronization & Email Integration 15.2 SharePoint & OneDrive Document Management Integration 15.3 Microsoft Word & Excel Document Templates
16 Chapter 16: Copilot, AI Capabilities & Exam Strategy
16.1 Copilot Studio & Generative AI across Power Platform 16.2 AI Builder Prebuilt & Custom Models in Apps and Flows 16.3 PL-200 Exam Strategy, Time Management & Question Analysis
1.3 Column Types, Formats, Primary Keys & Alternate Keys
Key Takeaways Explain This Topic AI
Dataverse provides diverse column data types with specific formatting options, storage mechanisms, and platform behaviors.
Numerical types have distinct mathematical models: Decimal provides fixed-point precision for financial integrity, Float provides IEEE scientific floating-point approximation, and Currency maintains base-currency tracking via real-time exchange rates.
Date and Time behaviors (User Local, Date Only, Time-Zone Independent) dictate whether UTC offsets are applied; converting from User Local to Date Only or Time-Zone Independent is an irreversible, one-way operation.
Choice columns are stored as integer option codes, whereas multi-select Choices use bitmask storage with specific OData querying constraints.
Alternate Keys support up to 10 keys per table across 1 to 5 indexed columns, enabling external systems to perform idempotent upsert operations without internal GUIDs.
Last updated: August 2026
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:
<ColumnName> : Stores the value in the transaction currency selected on the record.
<ColumnName>_base : Stores the calculated equivalent in the organization's base currency, calculated via the ExchangeRate column against the linked TransactionCurrency record.
- Feature Whole Number Decimal Number Float (Floating Point) Currency
- Storage Type 32-bit Integer Fixed-point numeric IEEE 754 Binary Float Fixed-point + Base column
- Max Decimals 0 Up to 10 Up to 5 Up to 4
- Precision Model Exact integer Exact mathematical Approximate binary Exact with exchange rates
- Target Use Case Quantities, Counts Taxes, Financial Ledgers Physics, Scientific Data Revenue, Costs, Bids
- 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.