Dataverse – Create Primary Column

When building applications in Dataverse, having a user-friendly auto-generated ID column can significantly enhance the usability and organization of your data. In this post, we’ll walk through how to set up an auto-generated Primary Column in a Dataverse table, using a Course Schedule example.

---

Why Use Auto-Generated IDs?

Auto-generated IDs provide:

• Consistency: Ensures every record has a unique and readable identifier.
• Automation: Removes the need for manual entry or additional workflows.
• Professionalism: Makes tables easier to navigate, especially for users unfamiliar with the system.

---

Step-by-Step Guide: Creating Auto-Generated IDs

1. Create a New Table

1. Go to Power Apps Maker Portal:
   • Navigate to Dataverse > Tables > + New Table.
2. Configure Table Settings:
   • Table Name: Course Schedule.
   • Primary Column Name: Set this to ID.
   • Primary Column Type: Default to Text during table creation.
3. Save the Table:
   • Complete the table creation with a simple Text type for the Primary Column.

---

2. Modify the Primary Column to Auto-Number

1. Navigate to the Table Settings:
   • Open the Course Schedule table.
   • Go to the Columns section.
2. Edit the Primary Column (ID):
   • Click on the Primary Column (ID).
   • Change the Data Type from Text to AutoNumber.
3. Customize the Auto-Number Format:
   • Prefix: Set to SCH- (e.g., for “Schedule”).
   • Number Format: Choose a sequential format like 0001.
   • Example Output: SCH-0001, SCH-0002, SCH-0003.
4. Save Changes:
   • Save the column configuration.

---

3. Verify Auto-Generated IDs

1. Add Sample Records:
   • Open the Course Schedule table and create a few records.
   • The ID column will now automatically generate values, such as:
     • SCH-0001
     • SCH-0002
2. View in Forms and Views:
   • The ID column is now the Primary Column, so it will display prominently in forms, views, and lookups.

---

How Dataverse Handles Auto-Generated IDs

• Primary Column: After converting to AutoNumber, the Primary Column becomes a read-only, automatically populated field for each record.
• System-Generated Unique Column: Dataverse automatically creates a system-managed unique identifier column (e.g., CourseScheduleID) in the background, ensuring every record has a globally unique identifier (GUID).

---

Example: Course Schedule Table

Column Name	Type	Example Value	Purpose
ID (Primary Column)	AutoNumber	SCH-0001, SCH-0002	User-friendly, auto-generated identifier.
Course Name	Text	Physics 101	Descriptive name of the course.
StartDate	Date	2024-12-01	The start date of the course.
Location	Text	Room 101	Location for the course schedule.
CourseScheduleID	System GUID	a1b2c3d4-e5f6-7890	Globally unique system-managed identifier.

---

Advantages of Using Auto-Generated IDs

1. Simplified Data Entry:
   • Automatically assigns an identifier without manual input.
2. Improved Usability:
   • User-friendly IDs like SCH-0001 make it easier for users to navigate the system compared to GUIDs.
3. Customization:
   • Prefixes and number formats can be tailored to fit your organization’s naming conventions.
4. Seamless Integration:
   • Works well in forms, views, reports, and automation workflows.

---

Best Practices for Auto-Generated IDs

1. Plan the Format:
   • Use meaningful prefixes (SCH-, CRS-) to categorize records.
   • Decide on a consistent numbering scheme (e.g., 0001 or YYYY-0001).
2. Test Before Deployment:
   • Verify the AutoNumber configuration with test records to ensure the format aligns with your needs.
3. Combine with Descriptive Columns:
   • Pair the auto-generated ID with a descriptive column like Course Name for improved clarity.
4. Leverage Lookups:
   • Use auto-generated IDs in relationships or lookups to maintain a consistent reference across tables.