Dataverse for Architects: Designing with Alternate Keys in Mind

In Microsoft Dataverse, data integrity and uniqueness are critical for building scalable, enterprise-grade apps. While Dataverse ensures every row has a system-generated GUID as its primary key, real-world business scenarios often require natural identifiers like Customer Numbers or Email Addresses to enforce uniqueness.

This is where Keys in Dataverse—both primary and alternate—become essential. In this guide, we’ll deep dive into:

  •  What keys are in Dataverse
  •  Technical details for developers
  •  Strategic insights for architects
  •  Real-world use cases
  •  Limitations and best practices

What Are Keys in Dataverse?

A key in Dataverse defines a column (or set of columns) that enforces uniqueness in a table.

This means no two rows can have the same value for the key column(s).

There are two types of keys in Dataverse:


Primary Key (System-Managed)

  • Every Dataverse table automatically includes a GUID primary key (e.g., `contactid`, `accountid`).
  •  Assigned automatically when a record is created.
  •  Immutable – once created, it never changes.

Example:

`accountid = 15f2b5d0-34d1-4a18-8e42-44c9b33e43a2`

Alternate Keys (User-Defined)

  • Alternate keys let you enforce uniqueness on one or more columns of your choice.
  • They’re great for business identifiers like Customer Numbers, Emails, or SKU Codes.



How to Create Alternate Keys

Step-by-Step

1️⃣ Go to [Power Apps Maker Portal](https://make.powerapps.com)

2️⃣ Select your table (e.g., Account) → Keys tab

3️⃣ Click + New Key

4️⃣ Choose column(s) to define as unique

5️⃣ Save and Publish


Supported Column Types:

  •  Single Line of Text (max 450 chars)
  •  Whole Number
  •  Decimal Number
  •  Lookup (relationships)



 


Best Practices

  • Use alternate keys for external system IDs (e.g., SAP Customer ID).
  • Prefer single-column keys for simplicity and performance.
  • Avoid using fields that frequently change as keys.
  • For composite keys, keep the number of columns minimal.
  • Always test large imports or API calls for duplicate key errors.

Real-World Use Cases

  •  🛒 E-commerce: `SKU` as alternate key for Product table.
  •  🏥 Healthcare: `PatientID` as alternate key for Patient table.
  •  📦 Supply Chain: Vendor or Purchase Order Numbers for integration with ERP.
  •  📧 CRM: `emailaddress1` for deduplication of Contacts.

Summary

Keys in Dataverse are a must-have tool for both developers and architects:

  • Developers – For clean APIs, upserts, and cross-system references
  • Architects – For maintaining data integrity and future-proofing solutions

By mastering keys, you can build enterprise-grade, scalable solutions with confidence.

Comments

Post a Comment

Popular posts from this blog

Effective Strategies for Debugging Plugins in Dynamics CRM

Image
In a recent interview, I was asked about debugging plugins in Dynamics CRM. The interviewer specifically wanted to know my approach to plugin debugging. In response, I mentioned using the Plugin Registration Tool profiler. However, I later realized there are multiple methods to debug plugins in Dynamics CRM/Dataverse, each essential for efficient troubleshooting. Debugging plays a crucial role in development, and having a solid understanding of various debugging approaches can help streamline issue resolution in Dynamics 365. Here, I'll explore different techniques for debugging plugin code. There are below options to use in the plugin in Dynamics CRM/Dataverse ITracingService : In Dynamics CRM plugin development, ITracingService is a service provided within the plugin's execution context that logs information for tracing and debugging. It allows developers to capture real-time. Plugin Profiler : A plugin profiler in Dynamics CRM (or Dataverse) is a debugging tool in the Plugi...
Read more

Connecting the Dots: FetchXML and Web API Integration in Dataverse

Image
 In Dataverse, FetchXML and the Web API are two powerful mechanisms for retrieving data and interacting with entities. Here's a brief overview of each and how they can be integrated: 1.    FetchXML :      -   Definition:   FetchXML is a proprietary query language used in Microsoft Dynamics 365 and Dataverse for querying data from entities.    -   Usage:   FetchXML queries can be used to retrieve data based on complex criteria, perform aggregations, and filter records.    -   Features:   FetchXML supports various query expressions, including conditions, sorting, grouping, and aggregation functions.    -   Integration:   FetchXML queries can be executed programmatically using client-side scripting, server-side code, or through tools like the Dynamics 365 Web API. 2.    Web API :      -   Definition:  ...
Read more

Exploring the Differences: Managed vs. Unmanaged Solutions in Dynamics CRM/Dataverse

Image
In Dynamics CRM/Dataverse, solutions are central to Application Lifecycle Management (ALM) , providing a structured way to manage, package, and distribute customizations across environments. Microsoft uses solutions to transfer apps and customizations by exporting them from one Dataverse environment and importing them into another.  Each solution is authored and maintained by a publisher and includes components like Power Apps (canvas and model-driven), flows, entities, forms, connectors, web resources, and configurations—excluding business data. Solutions streamline ALM practices within Microsoft Power Platform. In Dynamics CRM/Dataverse, there are two types of solutions: Managed and Unmanaged . When planning an implementation, it’s important to understand the advantages and disadvantages of each to make an informed decision. Let’s explore both types to determine the best approach for development and deployment. In Dynamics 365 CRM, a managed solution is a fully packaged solution ...
Read more

L1, L2, and L3 Support Explained

Image
  L1, L2, and L3 Support Explained In IT and software support, L1 (Level 1), L2 (Level 2), and L3 (Level 3) represent different tiers of technical support, each handling issues of varying complexity. 1️⃣ L1 Support (Level 1 - Frontline Support) Role: First point of contact for users/customers Responsibilities: Handles basic issues such as password resets, user access, and general troubleshooting. Uses a knowledge base or predefined scripts to resolve common problems. Escalates complex issues to L2 if not resolved. Communicates with customers via phone, chat, or email. Records tickets in a helpdesk system (e.g., ServiceNow, Jira, Zendesk). 🔹 Example: A user is unable to log in due to incorrect credentials → L1 resets the password. 2️⃣ L2 Support (Level 2 - Technical Support) Role: More in-depth troubleshooting and problem-solving Responsibilities: Resolves moderate to complex technical issues that L1 cannot handle. Analyzes logs, configurations, and system...
Read more

Decode and Fix: “This Data Type is Unsupported for Evaluation” in Power Apps

Image
 One common frustration when building Canvas Apps over Dataverse is encountering this cryptic error:  “This data type is unsupported for evaluation” This message typically appears when Power Apps cannot process or render a specific Dataverse column type within a control or formula. While it may seem vague, it’s usually tied to specific patterns involving complex column types or runtime data structure mismatches. In this blog, we'll explore common causes, scenarios, and practical fixes from both a developer and architectural standpoint. Root Causes of the Error 1. Polymorphic Lookups Dataverse supports polymorphic relationships (e.g., `Owner`, `Customer`, `Regarding`) that can point to multiple entity types. These lookups can't be directly evaluated in Power Apps unless you cast the reference explicitly. Use: If(     IsType(ThisItem.Customer, Account),     AsType(ThisItem.Customer, Account).Name,     AsType(ThisItem.Customer, Contact).FullName ) 2....
Read more

Model-Driven Apps Command Bar: A Guide to PrimaryControl and SelectedControl

Image
The command bar in Power Apps model-driven apps is a toolbar that lets users perform actions. You can customize it by adding buttons that run your own logic, either using Power Fx or JavaScript. This logic can work with a single data record or multiple records, depending on where the button is placed. The CrmParameter in the ribbon of Dynamics CRM (now Dynamics 365) is a special parameter used in the ribbon's button definitions or commands. It provides contextual information about the entity or record within the CRM system when the ribbon button is clicked. Purpose of CrmParameter CrmParameter is primarily used to pass specific CRM-related data to a custom JavaScript function, URL, or command action. It helps in retrieving dynamic data based on the current context, such as entity IDs, selected records, or even the page's user interface settings. 1. PrimaryControl Definition : Refers to the main form context or entity record currently being displayed or interacted with in the ap...
Read more

How to Write and Understand a Dynamics CRM Plugin

Image
 Here’s a sample plugin code in Dynamics CRM written in C#, along with a detailed explanation of each line. This plugin will update the "Description" field of an entity whenever a record is created. Sample Plugin Code using System; // For basic .NET types using Microsoft.Xrm.Sdk; // Provides key interfaces for CRM plugin development using Microsoft.Xrm.Sdk.Query; // For performing queries against CRM data namespace SamplePluginNamespace // Custom namespace for the plugin { public class SamplePlugin : IPlugin // Plugin class implementing the IPlugin interface { public void Execute(IServiceProvider serviceProvider) { // STEP 1: Retrieve the context of the plugin IPluginExecutionContext context = (IPluginExecutionContext)serviceProvider.GetService(typeof(IPluginExecutionContext)); if (context.MessageName != "Create" || context.St...
Read more

Stop Struggling with Plugins: Learn IOrganizationService the Smart Way

Image
Have you ever deployed a Dataverse plugin, only to watch it fail silently or act unpredictably? Most of the time, the missing piece is understanding how to communicate with Dataverse the smart way—using IOrganizationService . In the world of Dataverse, every plugin waits for its turn when something changes. The plugin looks around and wonders, “How do I talk to the database?” That’s when IOrganizationService steps in like a trusted messenger, ready to carry out requests and bring back results. “ Need to create a record? I can handle that,” it says. “ Want to update or delete data safely ? Leave it to me.” It even speaks the secret language of Dataverse messages like Assign and SetState . But it never works alone— IOrganizationServiceFactory brings it to life, deciding whether it should act as the user or as the powerful system account. Working together with IPluginExecutionContext , it always knows who triggered the action, which record is involved, and when to respond. What is IOrgan...
Read more

How Every Plugin Action Travels Through Request, Service, and the Pipeline

Image
In Dataverse plugin development, a request is simply a message that tells Dataverse what action to perform. This could be a basic action like Create, Update, Delete, Retrieve , or a special action like Assign or SetState . Understanding requests is important for developers and architects because they affect how the plugin runs, how transactions work, and how multiple record updates stay consistent. Think of it this way:  A request says what needs to happen  A service (`IOrganizationService`) is the worker that does the job Together, they are the core of how plugins work in Dataverse. They:  Allow you to design plugins that work in a single transaction  Make sure changes are consistent and traceable  Work smoothly with the plugin pipeline to follow business logic For architects:   Service = the executor   Request = the action   Plugin pipeline = the traffic controller that manages transactions and triggers during plugin execution. Service (`IOrgan...
Read more

Plugin Execution Pipeline Demystified: A Must-Know for CRM Architects

Image
If you design or operate Microsoft Dynamics 365/Dataverse solutions, understanding the Plugin Execution Pipeline is essential. Plugins are the primary extensibility mechanism for implementing server-side business logic. This guide explains the pipeline in plain, technical terms, shows where and how plugins run, and lists practical guidance for reliable, performant solutions. 1) What is the Plugin Execution Pipeline? The pipeline is the ordered set of stages and execution contexts in which server-side events (messages) are processed. When an operation (Create, Update, Delete, Assign, Retrieve, etc.) occurs, Dataverse builds a request execution pipeline and executes registered plugin steps at configured stages. Plugins can inspect and modify data, cancel operations, and trigger side effects. 2) High-level flow (what happens during an operation) Client/API call (UI, Web API, SDK) initiates an operation. Platform core logic performs pre-checks and builds the execution context. Plugin ste...
Read more