Auto-Increment a Number in a New List Item

The Problem

The web application I am designing requires several different auto-incrementing numbers. The customer account numbers, support ticket numbers, etc. must auto-matically increment when a new item is created. A quick web search will show a lot of people recommending the ID field (created by SharePoint) as the solution.

Disclaimer: I found this idea on a website or forum somewhere. I can’t locate the original source. If anyone knows the author of the original concept, let me know so I can give them credit and link to them.

Edit: Here is a link to the original forum post: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2232817&SiteID=1

I had two problems with this solution:

1. The number needs to start at 1000 not 1.
2. I need to be able to reset the number or change it if needed without loosing the existing data.

Other considerations:

1. Users can’t change the number.
2. The number has to be unique.

The solution I found seems to meet all of my criteria. This process is still in the design stage and has not been tested by users.

The Theory

A user creates a new item in a list. The system looks up a table of numbers and assigns the next available number to the new item. The system then updates the table of numbers.

In Practice

NOTE: I don’t use spaces in my list or column names and then I change the labels in the actual forms (I’ll explain that in a future post)

1. Start by creating a custom list. I called mine “SystemCounters”. This list will store all of the counters for my site.
2. Create a column called “Counter”, column type: Single Line of Text.
3. Create a column called “LastNumber”, column type: Number. Configure the column settings as Required and having 0 decimal places.
4. Create a column called “NextNumber”, column type: Calculated. Configure the column settings as Formula: “=LastNumber+1” (without the quotes), Data type returned as: Number, and having 0 decimal places.
5. Configure other list settings as needed. I like to disable attachments and make sure the list is not included in searches (advanced settings).
6. Open the list and create some new items. I created a new item as ClientID and set the LastNumber to 999.

You now have a table which will hold as many counters as you would like. You can create counters for almost any list, for example create a counter for issue numbers or support tickets.

How do you use the new counters?

Putting the counters into play will require that you have access to SharePoint Designer or Visual Studio. If you are attempting to create a web application without one of those two programs, you are in for a lot of pain and suffering. SharePoint Designer (SPD) will allow you to easily create workflows and edit forms for your application. I highly recommend the investment in purchasing the license.

1. Create a new custom list. For this example, we will create a list to track new clients. NOTE: if you create the list as a Contact list you will be able to easily sync it up with Outlook.
2. Create a new column called “ClientID”, column type: Single Line of Text or Number.
3. Create the rest of the columns you need to track for new clients. For example you could create FirstName, LastName, Company, etc. 
4. Open SPD and create a new workflow. Set the workflow to run in the Client Database and run everytime something is created.
5. Create a variable for the Client ID Number.
6. Set the workflow to populate the new variable with the contents of the NextNumber field from the SystemCounters list, where the Counter Name equals ClientID. NOTE: I have summarized this into one line of text, but it could be very complicated to new users. I would recommend reading the online help tutorial for creating workflows. I will try to create an article on workflow design in the future.
7. Tell the workflow to set the ClientID on the current item to equal the Client ID variable.
8. Now tell the workflow to set the LastNumber field in the SystemCounter list to equal the Client ID variable.
9. Save the workflow and we are rocking!

What happens?

NOTE: Personally, I use SPD to open the new item form for the Client Data list and set the Client ID field to type of label (or remove it completely). This prevents users from entering their own numbers in the Client ID field. Any number they entered would be overwritten by your workflow, but it will save you support emails like: “Where is my customer numbered 9999?”

When a new client is added to the database (list), the user enters the name and contact info and clicks save. The workflow kicks into action when the new item is created. It looks up the NextNumber field in the System Counters and finds the number 1000 (LastNumber was 999, NextNumber is LastNumber+1). It sets the variable to be 1000 and then populates the ClientID on the new item to be 1000. The workflow now sets the LastNumber in the SystemCounters to be 1000, which makes the NextNumber field automatically recalculate to 1001.

You now have a fully functioning Counter system and Auto-Incrementing number.

Those of you who pay attention might have realised that this doesn’t guarantee that the Client Database contains only unique numbers. I could create a client with a ClientID of 1000, set the counter back manually, and create another client with a ClientID of 1000. I’ll show you how I tackled that in another post.

Thanks for reading.