How to create auto increment column in SharePoint List

In this SharePoint tutorial, we will discuss different ways to create an auto-increment column in the SharePoint list. We will see how to create an auto-increment number column in a SharePoint list.

We will see how to create an auto-increment column in the SharePoint list using SharePoint designer workflow, event receiver, calculated column, Microsoft Flow, and using Custom JavaScript code.

The event receiver code will work in SharePoint 2013/2016 to create an auto-generated id column in SharePoint 2013/2016 lists, this code will not work in SharePoint Online.

Below are the five ways to create auto increment no or column in SharePoint.

• Using SharePoint Designer Workflow
• Using Calculated Field
• Using SharePoint Event Receiver
• Using Microsoft Flow
• Using Rest API and JavaScript code

Here I have a SharePoint Ticket Details list and my requirement here is to create PNR NO automatically (without user input).

In the above list, the PNR NO is the auto-generated column where SharePoint will update this field auto metrically so we need to hide this column while creating an item.

So we do not allow to user to enter any value in PNR NO column field. The below screen will show you to hide a PNR NO in a list.

Now the PNR NO column is hidden in the New form in the SharePoint list. It doesn’t allow the user to enter any value manually.

Create auto increment column in SharePoint list using SharePoint designer workflow

Here we will simply create a SharePoint 2013 designer workflow in a list and create an auto-generate no based on the item id column. When a new will add, same time workflow will fetch the ID of the particular item and will generate the auto-generate no. Next, it will update in the PNR column.

Steps for creating Workflow:

Step 1: Open your SharePoint Designer -> Go to List workflow in top of the designer and In the option choose your list “Ticket Details”.

Step 2: Next enter the name of your workflow and choose the SharePoint 2013 workflow as the Platform type. If you do not see the SharePoint 2013 workflow platform, then you can check to Install and Configure Workflow Manager for SharePoint 2016 in Windows Server 2012 R2 Step by Step tutorial.

Step 3: Once you click on OK button, you will redirect to workflow design page where you can design your workflow.

Step 4: Now you have to use Set Field in Current Item to update the list item field.

Step 5: Now you have to set PNR NO as a update column and use ID as a unique ID for your PNR NO.

Step 6: Next end the workflow in the Transition stage and go back to the workflow check start workflow automatically an item created. Next, deploy the workflow.

Step 7:Next go to your list and add an item and see your auto-generate column will be created in PNR NO field as per the below image.

Auto generate SharePoint list column using Calculated field

Using this we can accomplish it without doing any programming or workflow but it is a simple way of doing it. By using the Calculated column in SharePoint List we can create the auto-generate field. We can create it by choosing the column type as Calculated.

We can also use string value instead of only no in the calculated value.

Note: From my point of view we shouldn’t use the calculated column here because the ID will be created after Item will be created so once you insert an item in a list, it will show your wrong value in the list, again you have to refresh the list if you want to see the exact value in the autogenerate field.

Create auto increment column in SharePoint list using Event Receivers

We can also create an auto-generate no in Item added event receiver in the SharePoint list but we need to find the last Item ID and based on that we will increment the no by 1 and update in the autogenerate field.

public override void ItemAdded(SPItemEventProperties properties)
        {
            SPWeb web = properties.OpenWeb();
            bool allowUpdates = web.AllowUnsafeUpdates;
            this.EventFiringEnabled = false;
 
            try
            {
                web.AllowUnsafeUpdates = true;
                /*get the current list*/
                SPList list = web.Lists[properties.ListId];
                var LastItemID = 0;
                var objQuery = new SPQuery
                                   {
                                       Query ="<OrderBy><FieldRef Name='" + ID
                                       +"' Ascending='False' /></OrderBy><RowLimit>1</RowLimit>",
                                       Folder = list.RootFolder
                                   };
 
                SPListItemCollection colItems = list.GetItems(objQuery);
                if (colItems.Count > 0)
                {
                    LastItemID = int.Parse(colItems[0][ID].ToString());
                }
 
                var currItem = properties.ListItem;
                currItem[PNR-NO] ="PNR-000-" + LastItemID + 1;
                currItem.SystemUpdate(false);
                 
            }
            catch (Exception ex)
            {
                Trace.WriteLine(ex.Message);
            }
            finally
            {
                this.EventFiringEnabled = true;
                web.AllowUnsafeUpdates = allowUpdates;
            }
        }

Create auto increment column in SharePoint list using Microsoft Flow

If we are using Modern SharePoint Online list then we can also use simple Microsoft Flow to create an auto-generate no for a list based on the Item ID.

We can use the Update item action to generate the auto-generate column for the SharePoint list.

Auto generate ID column in SharePoint 2013/Online lists using JavaScript and Rest API

We can also use JavaScript coding to generate the auto-generated column in SharePoint. Here I am using a simple REST API to get the last ID of the Item. We can apply this code in while adding an item in a list like newform.aspx page inside a script editor Web part.

<script src="https://code.jquery.com/jquery-3.1.1.min.js"></script>
<script type="text/javascript" defer="defer">
var site="site Name";
$( document ).ready(function() {
$.ajax({
                   url: site+ "/_api/web/lists/getbytitle('List Name')/items?$top=1&$orderby=ID desc",
                   method: "GET",
                   headers: { "Accept": "application/json; odata=verbose" },
                   success: function (data) {
                        if (data.d.results.length > 0 ) {
                             $("[Custom Column Name='Title Required Field']").val(data.d.results[0].ID);
                        }       
                  },
                  error: function (data) {
                      alert("Error: "+ data);
                 }
          });
});

</script>

These are the above steps to create an auto-generated column in SharePoint in a list. You can use any of those based on the requirement.

You may like the following SharePoint tutorials:

• SharePoint Workflow history list URL
• SharePoint Online image column
• SharePoint list operations using rest API
• SharePoint list delete title column