Create autonumber columns (Microsoft Dataverse) - Power Apps (2024)

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

This topic explains how you can programmatically create an autonumber column and set a seed value for sequential elements. In addition, the topic 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, which indicates 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 creating an autonumber column, the FormatName Property or the Format Property values must be Text. Since these are the default values you will typically not set this property. You cannot create an autonumber column that uses any other special kind of format such as Email, Phone, TextArea, Url or any other existing formats.

The sequential segment is generated by SQL and hence uniqueness is guaranteed by SQL.

For model-driven apps, in the legacy web client, when adding a control on a form bound to an autonumber column, the control is disabled automatically and behaves as read-only in the form where end-users cannot edit the control. In Unified Interface, controls bound to an autonumber column need to explicitly be set as disabled. If you do not 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 which will have 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.1Accept: application/jsonContent-Type: application/json; charset=utf-8OData-MaxVersion: 4.0OData-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 ContentOData-Version: 4.0OData-EntityId: [Organization URI]/api/data/v9.0/EntityDefinitions(402fa40f-287c-e511-80d2-00155d2a68d2)/Attributes(f01bef16-287c-e511-80d2-00155d2a68d2)

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 will be 01, and the 100th row value will be 100. The number will continue 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 additional 0s to the initial value. It will not limit the absolute value. Random value placeholders will always be the same length.

Because the sequential values can get larger than the minimum length allotted for them, you should not adjust the StringAttributeMetadata.MaxLength Property to match the length of your formatted value. There is little value in doing this 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 explains you 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 requestvar attributeRequest = new RetrieveAttributeRequest { EntityLogicalName = entityName.ToLower(), LogicalName = "new_serialnumber", RetrieveAsIfPublished = true };// Retrieve attribute responsevar attributeResponse = (RetrieveAttributeResponse)_serviceProxy.Execute(attributeRequest);

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

//Modify the retrieved autonumber columnAttributeMetadata retrievedAttributeMetadata = attributeResponse.AttributeMetadata;//Modify the existing column by writing the format as per your requirementretrievedAttributeMetadata.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 API below to set the next number that are used for the sequential segment.

For example, when the length of the sequential number is 5, you may 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.1Accept: application/jsonContent-Type: application/json; charset=utf-8OData-MaxVersion: 4.0OData-Version: 4.0{ "EntityName": "new_Widget", "AttributeName": "new_Serialnumber", "Value": 10000} 

HTTP/1.1 204 No ContentOData-Version: 4.0

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.

Please see the Developer tools topic 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