Create autonumber columns

With Microsoft Dataverse, you can add an autonumber column for any table. To create autonumber columns in Power Apps, see Autonumber columns.

This article explains how you can programmatically create an autonumber column and set a seed value for sequential elements. In addition, the article shows how to set the sequence number for the next record if you need to reset the seed at any time later.

You can create an autonumber column in the same way you create a string column using the StringAttributeMetadata Class except that you use the AutoNumberFormat Property. Use the AutoNumberFormat property to define a pattern that includes sequential numbers and random strings by composing placeholders that indicate the length and type of values that are generated. The random strings help you to avoid duplicates or collisions, especially when offline clients trying to create autonumbers.

When you create an autonumber column, the FormatName Property or the Format Property values must be Text. Since these values are the default values, you'll typically not set this property. You can't create an autonumber column that uses any other special format such as Email, Phone, TextArea, Url, or any other existing formats.

SQL generates the sequential segment guarantees uniqueness.

In model-driven apps using Unified Interface, controls bound to an autonumber column need to explicitly be set as disabled. If you don't set the initial column value on the form, the value is set only after you save the table. Autonumbering can be applied to column values in views, grids, and so on.

Examples

The following examples show how to create a new autonumber column named new_SerialNumber for a custom table named new_Widget that has a value that looks like this: WID-00001-AB7LSFG-20170913070240 using the Web API and the Dataverse SDK for .NET.

POST [Organization URI]/api/data/v9.0/EntityDefinitions(LogicalName='new_widget')/Attributes HTTP/1.1
Accept: application/json
Content-Type: application/json; charset=utf-8
OData-MaxVersion: 4.0
OData-Version: 4.0

{
 "AttributeType": "String",
 "AttributeTypeName": {
  "Value": "StringType"
 },
 "Description": {
  "@odata.type": "Microsoft.Dynamics.CRM.Label",
  "LocalizedLabels": [
   {
    "@odata.type": "Microsoft.Dynamics.CRM.LocalizedLabel",
    "Label": "Serial Number of the widget.",
    "LanguageCode": 1033
   }
  ]
 },
 "DisplayName": {
  "@odata.type": "Microsoft.Dynamics.CRM.Label",
  "LocalizedLabels": [
   {
    "@odata.type": "Microsoft.Dynamics.CRM.LocalizedLabel",
    "Label": "Serial Number",
    "LanguageCode": 1033
   }
  ]
 },
 "RequiredLevel": {
  "Value": "None",
  "CanBeChanged": true,
  "ManagedPropertyLogicalName": "canmodifyrequirementlevelsettings"
 },
 "SchemaName": "new_SerialNumber",
 "AutoNumberFormat": "WID-{SEQNUM:5}-{RANDSTRING:6}-{DATETIMEUTC:yyyyMMddhhmmss}",
 "@odata.type": "Microsoft.Dynamics.CRM.StringAttributeMetadata",
 "FormatName": {
  "Value": "Text"
 },
 "MaxLength": 100
}

HTTP/1.1 204 No Content
OData-Version: 4.0
OData-EntityId: [Organization URI]/api/data/v9.0/EntityDefinitions(00aa00aa-bb11-cc22-dd33-44ee44ee44ee)/Attributes(11bb11bb-cc22-dd33-ee44-55ff55ff55ff)

string conn = $@"
    Url = {url};
    AuthType = OAuth;
    UserName = {userName};
    Password = {password};
    AppId = 51f81489-12ee-4a9e-aaae-a2591f45987d;
    RedirectUri = app://58145B91-0C36-4500-8554-080854F2AC97;
    LoginPrompt=Auto;
    RequireNewInstance = True";

var service = new CrmServiceClient(conn);
// var service = new ServiceClient(conn);

var widgetSerialNumberAttributeRequest = new CreateAttributeRequest
  {
    EntityName = "newWidget",
    Attribute = new StringAttributeMetadata
      {
        //Define the format of the column
        AutoNumberFormat = "WID-{SEQNUM:5}-{RANDSTRING:6}-{DATETIMEUTC:yyyyMMddhhmmss}",
        LogicalName = "new_serialnumber",
        SchemaName = "new_SerialNumber",
        RequiredLevel = new AttributeRequiredLevelManagedProperty(AttributeRequiredLevel.None),
        // The MaxLength defined for the string column must be greater than the length of the 
        // AutoNumberFormat value, that is, it should be able to fit in the generated value.
        MaxLength = 100, 
        DisplayName = new Label("Serial Number", 1033),
        Description = new Label("Serial Number of the widget.", 1033)
      }
  };
  service.Execute(widgetSerialNumberAttributeRequest);

AutoNumberFormat options

These examples show how you can configure the AutoNumberFormat Property to get different results:

The random string placeholders are optional. You can include more than one random string placeholder. Use any of the format value for datetime placeholders from Standard date and time format strings.

String length

The table shows the string length value for the random and sequential placeholders.

For sequential value placeholders, the MIN_LENGTH is a minimum length. If you set the value to be 2, the initial value is 01, and the 100th row value is 100. The number continues to increment beyond the minimum length. The value in setting the length for sequential values is to establish a consistent length for the autogenerated value by adding more 0s to the initial value. It doesn't limit the absolute value. Random value placeholders are always the same length.

Because the sequential values can get larger than the minimum length allotted for them, you shouldn't adjust the StringAttributeMetadata.MaxLength Property to match the length of your formatted value. There's little value in setting this property and it could cause an error in the future if the value exceeds the MaxLength value. Make sure you leave enough room for a realistic range of sequential values.

Update autonumber columns

If you create an autonumber column with an incorrect configuration or you want to modify an existing autonumber column, you can update the column the AutoNumberFormat value.

The following code snippet demonstrates how to retrieve, modify, and update the autonumber column using the SDK for .NET:

To modify an existing autonumber column, you must retrieve the column using the RetrieveAttributeRequest Class.

// Create the retrieve request
var attributeRequest = new RetrieveAttributeRequest
  {
    EntityLogicalName = entityName.ToLower(),
    LogicalName = "new_serialnumber",
    RetrieveAsIfPublished = true
  };
// Retrieve attribute response
var attributeResponse = (RetrieveAttributeResponse)_serviceProxy.Execute(attributeRequest);

After retrieving the autonumber column, you need to modify and update the column.

//Modify the retrieved autonumber column
AttributeMetadata retrievedAttributeMetadata = attributeResponse.AttributeMetadata;
//Modify the existing column by writing the format as per your requirement
retrievedAttributeMetadata.AutoNumberFormat = "CAR-{RANDSTRING:5}{SEQNUM:6}";  

// Update the autonumber column            
var updateRequest = new UpdateAttributeRequest
  {
    Attribute = retrievedAttributeMetadata,
    EntityName = "newWidget",
  };
// Execute the request
_serviceProxy.Execute(updateRequest);

Set a seed value

By default, all autonumber sequential values start with 1000 and use 0 as the prefix depending on the length. In this way, the length of the value is always same. If you want to change the initial value, you need to change the initial seed value using the SetAutoNumberSeed message to set the next number that is used for the sequential segment.

For example, when the length of the sequential number is 5, you might want to start with an initial value of 10000 instead of the default value of 00001. If you want to choose a different starting value after creating the autonumbering column, use the SetAutoNumberSeed message. Use the SetAutoNumberSeedRequest class when using the SDK assemblies and SetAutoNumberSeed Action when using the Web API.

The AutoNumberSeed message has the following parameters:

SetAutoNumberSeed Examples

The following samples set the seed to 10000 for an autonumber column named new_SerialNumber for a custom table named new_Widget.

POST [Organization URI]/api/data/v9.0/SetAutoNumberSeed HTTP/1.1
Accept: application/json
Content-Type: application/json; charset=utf-8
OData-MaxVersion: 4.0
OData-Version: 4.0

{
 "EntityName": "new_Widget",
 "AttributeName": "new_Serialnumber",
 "Value": 10000
} 

HTTP/1.1 204 No Content
OData-Version: 4.0

string conn = $@"
    Url = {url};
    AuthType = OAuth;
    UserName = {userName};
    Password = {password};
    AppId = 51f81489-12ee-4a9e-aaae-a2591f45987d;
    RedirectUri = app://58145B91-0C36-4500-8554-080854F2AC97;
    LoginPrompt=Auto;
    RequireNewInstance = True";

var service = new CrmServiceClient(conn);
// var service = new ServiceClient(conn);

//Define the seed 
SetAutoNumberSeedRequest req = new SetAutoNumberSeedRequest();
req.EntityName = "newWidget";
req.AttributeName = "newSerialnumber";
req.Value = 10000;
service.Execute(req);

Community tools

Auto Number Manager

Auto Number Manager for XrmToolBox is a community driven tool for Dataverse that provides a UI to set, update, and remove autonumber format on new or existing columns.

See the Developer tools article for community developed tools and anm.xrmtoolbox.com for more information about Auto Number Manager.

See Also

Work with table definitions using code
Customize table definitions