Demystified the Alternate Keys in Dataverse tables

When working with Dataverse, understanding Primary Keys, Primary Name Columns, and Alternate Keys is essential for designing efficient and user-friendly applications. This blog post explores the differences between these concepts, their use cases, and how alternate keys can significantly improve our workflows, especially in scenarios like external integrations and relational lookups. We will use two Dataverse tables Student and Admission Record to understand them.

Please note that “This text content of this blog post was drafted with the assistance of ChatGPT and I have reviewed every word and sentences and verified the content with practical implementation and screenshots.”

Table of Contents

1. What is a Primary Key?
2. What is a Primary Name Column?
3. Differences Between Primary Key and Primary Name Column
4. What is an Alternate Key?
5. Differences Between Primary Keys and Alternate Keys
6. How to Create an Alternate Key in Dataverse
7. Scenarios Where Alternate Keys Are Better Than GUIDs or Primary Keys
8. Additional Usage of Alternate Keys with External Data Sources
9. Conclusion

What is a Primary Key?

A Primary Key is a system-generated unique identifier for each record in a Dataverse table. It is essential for maintaining uniqueness and enabling relationships between records across tables.

Key Features:

• Type: Typically a GUID (Globally Unique Identifier).
• Purpose: Ensures that every record in a table is uniquely identifiable.
• System-Generated: Created automatically when a table is created.
• Backend Use: Used in relational lookups, APIs, and system operations.
• Immutable: The Primary Key cannot be changed once a record is created.
• Column Name: It is the same as the table name.

Examples:

• For a table called Student, the Primary Key is the column Student which contains GUID like f9318676-17cc-ef11-a72e-7c1e520ed992.

What is a Primary Name Column?

The Primary Name Column is a human-readable field that acts as the default identifier for a record in the user interface of Dataverse. While it is not a unique identifier by default, it represents the record in views, searches, and other UI interactions.

Key Features:

• Type: Usually a text field (e.g., Name, Student Name).
• Purpose: Makes records easily identifiable to users in the UI.
• User-Friendly: Designed for human readability and usability.
• Customizable: Can be renamed or changed to another column type (with some restrictions).

Examples:

• For the Student table, the Primary Name Column is Student Name, showing values like Stephen or James.

Differences Between Primary Key and Primary Name Column

While the Primary Key ensures system-level uniqueness, the Primary Name Column makes records human-readable and user-friendly in the interface. They serve complementary but distinct purposes in Dataverse.

What is an Alternate Key?

An Alternate Key is a user-defined unique identifier for records in a Dataverse table. It provides a meaningful way to identify records using one or more columns. Alternate keys are particularly useful for integrations, lookups, and scenarios where human-readable identifiers are needed.

Example:

In the Student table where each student has a unique Registration Number (logical name: blog_registrationnumber), we can define an alternate key on the Registration Number column. Now, instead of using the GUID to reference a student, you can use Registration Number. For example, in an API call, you can use /students(blog_registrationnumber="RID-101") to fetch the student directly.

Characteristics of an Alternate Key:

• Defined by one or more columns in a table.
• Enforces uniqueness for the selected columns.
• Optimized for lookups and relational references.
• Used in integrations and APIs to interact with records without needing GUIDs.

Differences Between Primary Keys and Alternate Keys:

How to Create an Alternate Key in Dataverse

Steps:

• Navigate to the Table:
  • Open the Student table in Dataverse.
• Create a New Key:
  • Go to the Keys tab and click + New key.

• Select the field(s) to define the key (e.g., Registration Number).

• Save the key.

Scenarios Where Alternate Keys Are Better Than GUIDs or Primary Keys

To demonstrate the utility of alternate keys, let’s consider a real-world example involving two tables: Student and Admission Record. The Admission Record table has a lookup column referencing the Student table. We will try to update a an admission record with the student name.

Scenario Without Alternate Keys

When working without alternate keys, relationships between tables are established using GUIDs. Here’s how the process typically works when we try to update a look up field without using alternate key. It usually takes 3 steps: First get the student record using the regisration number, extract the GUID from the record and use the GUID to update the student field.

• Obtain the Student’s record:
  • Query the Student table using a known attribute (e.g., blog_registrationnumber) to retrieve the GUID.
  • Example in Power Automate:
    • Add a “List rows” action to filter by Registration Number.
    • OData query: blog_registrationnumber eq 'RID-101‘

• Extract the GUID from the response.
  When the flow is executed at this point successfully, we see the id of the student with Registration Number RID-101 at @odata.id.

• Use the GUID in the Admission Table:
  • When updating the record in the Admission Record table, set the Student lookup field using the GUID.
    We will provide the value dynamically in the format: /lookuptable_setname(row id)
    blog_students is the set name. We can get this name from the table tools.

Hence, /blog_students(Odata Row ID from dynamic content).

Once this flow is executed successfully, we will see the Admission Record e4b2cd8b-9a8f-4e96-b1db-74f806aff77a has Student field populated.

• Challenges:
  • Extra Query Overhead: Every reference requires querying the Student table.
  • Complex Logic: Additional steps to fetch GUIDs increase flow complexity.
  • Error-Prone: GUIDs are easy to mishandle, leading to incorrect relationships.

Scenario With Alternate Keys

By introducing an alternate key, such as Registration Number Alternate Key, the process becomes significantly more efficient. Here’s how it works:

• Reference the Student Using the Key:
  • When updating a record in the Admission Record table, we will use the Registration Number field for which an alternate key has been created above to set the Student lookup field.

  Example in Power Automate

  • We will not use List Rows action and control loop anymore. In the Update a Row action, we will directly use the Alternate Key to provide the student Registration Number.

The modified flow is shown below:

Here the difference we can see is that instead of passing the GUID, we are now using the Registration Number field and passing the human-readable value to it.

When this flow is run, we can see that the second Admission Record has been updated with the student Smith whose registration number is 102.

Benefits:

• No GUID Lookup: Eliminate the need to fetch GUIDs.
• Simplified Logic: Reduce the number of queries and complexity in flows.
• Human-Readable Identifiers: Use meaningful keys like Registration Number for clarity.

Additional Usage of Alternate Keys with External Data Sources

Below are some of the additional usage I have found out from across the internet and through ChatGPT.

1. Data Integration with External Systems

• Scenario: When integrating Dataverse with an external system (e.g., ERP or CRM), the external system may use its own unique identifiers, such as EmployeeID or OrderNumber.
• Benefit: By defining alternate keys in Dataverse that match these external identifiers, you can directly interact with records without converting or querying GUIDs.
• Example: An external system sends updates using OrderNumber. The alternate key OrderNumber in Dataverse allows direct referencing like /orders(OrderNumber="ORD12345") in API requests.

2. Data Migration

• Scenario: During data migration from legacy systems into Dataverse, alternate keys ensure that existing unique identifiers (e.g., LegacyCustomerID) remain valid and enforce uniqueness.
• Benefit: Reduces the need to remap legacy identifiers to GUIDs, simplifying the migration process.

4. Custom API Calls

• Scenario: In scenarios where external systems make API calls to Dataverse endpoints, alternate keys provide a more intuitive way to reference records.
• Example: Instead of providing a GUID for a record, the external system can pass the alternate key (/blog_students(blog_registrationnumber="RID-101")), simplifying the API design.

5. Cross-System Validation

• Scenario: When validating data consistency between Dataverse and external systems, alternate keys act as common identifiers for comparison and synchronization.

Conclusion

Using alternate keys in Dataverse can greatly simplify data operations, improve integration workflows, and make relational references more intuitive. While Primary Keys (GUIDs) serve as the backbone for internal record management, alternate keys provide a human-readable, efficient alternative for external integrations and user-facing scenarios.

By implementing alternate keys in your Dataverse tables, you can ensure streamlined data handling and optimized performance across all your applications.

Let me know what you feel about this post in the comment section or if you would like to see the use of Alternate Keys in any other scenarios. Thank you!

Check James Yumnam’s original post https://jamesyumnam.com/2025/01/07/demystified-the-alternate-keys-in-dataverse-tables/ on jamesyumnam.com which was published 2025-01-07 11:40:00