Demystifying the AutoNumber Column in Power Platform Dataverse

Dataverse
Written By Nandit Dhania

Raise your hand if you've ever stared blankly at the Dataverse AutoNumber column, wondering how it really works. Yeah, me too. Let's figure this thing out together!

The AutoNumber column in Dataverse is a handy feature that automatically generates unique sequential values for each new record created in your table. While seemingly simple, understanding its nuances can save you from potential headaches down the line.

Setting Up an AutoNumber Column

  1. Navigate to your Dataverse table: In the Power Apps maker portal, go to the "Tables" section and select the table you want to modify.

  2. Create a new column: Click on "+ Add column" and choose "AutoNumber" as the data type.

  3. Configure the format: You have three options:

    • Number: Generates a simple sequential number (1, 2, 3...).

    • Date and time: Includes a date and/or time component (20230811-1, 20230811-2...).

    • Custom: Allows you to define a prefix, suffix, and the number of digits. For example, "CON-{RANDSTRING:4}-{SEQNUM:4}" would generate values like "CON-aBcD-0001".

  4. Save your changes: Don't forget to save your table!

Here are a few examples:

Number Format

Date Format

Custom Format

How it Works

Behind the scenes, Dataverse uses an internal algorithm to generate and assign the next available number in the sequence. This ensures uniqueness, even across multiple users creating records simultaneously.

Common Use Cases

Unique Record Identifiers: AutoNumber columns are perfect for creating human-readable, unique IDs for your records (e.g., order numbers, invoice numbers).

Maintaining Order: The sequential nature helps in maintaining a chronological order of record creation.

Integration Scenarios: AutoNumber values can be useful when integrating with external systems that require unique identifiers.

When NOT to Use AutoNumber Columns

As Primary Keys: While tempting, avoid using AutoNumber columns as your primary key. Dataverse already assigns a globally unique GUID to each record, which is a more robust and reliable identifier.

For Mutable Data: If the value needs to change after the record is created, an AutoNumber column is not suitable.

Across Multiple Tables: Each AutoNumber column maintains its own independent sequence. If you need a consistent numbering system across multiple tables, consider alternative approaches.

Things to Consider

Sequence Gaps: Deleting records can create gaps in the numbering sequence. Dataverse doesn't automatically backfill these gaps.

Performance: While generally performant, extremely high-volume scenarios might require careful consideration of AutoNumber column usage.

Customization Limitations: You cannot directly modify the value of an AutoNumber column after a record is created.

Limitations

No Rollback: Once a number is generated, even if the record creation fails, the number is considered used and won't be reused.

Limited Formatting Options: While you have some control over the format, it's not as flexible as creating a custom text field and managing the numbering logic yourself.

In Conclusion

The AutoNumber column in Dataverse is a valuable tool for many scenarios. By understanding its capabilities, limitations, and best practices, you can leverage it effectively in your Power Platform solutions.

Nandit Dhania