Auto-Increment a Number in a New List Item

April 11, 2008 at 12:15 pm 15 comments

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.

About these ads

Entry filed under: SharePoint Designer, Web Application. Tags: , .

Introduction to my Weblog Defaulting a Hidden Field on a Form

15 Comments Add your own

  • 1. celerity12  |  May 7, 2008 at 4:50 pm

    This approach doesn’t work on document library, it compains document checked out although i have versioning turned off

    Reply
  • 2. ithurtswhenipee  |  May 29, 2008 at 3:09 am

    LastNumber set to 9999 or 999? According to my math 9999+1 = 10000 not 1000. Or is there some programatical reason for 9999?

    Reply
  • 3. ThiNg  |  June 11, 2008 at 7:26 pm

    Sorry about that, the number should be 999. I’ve updated the topic. Thanks for catching it!!

    Reply
  • 4. SPKW  |  June 30, 2008 at 3:54 pm

    Nice post!! Thank you..works great.

    Reply
  • 5. Kevin  |  October 23, 2008 at 9:24 am

    Hi,

    I cannot get the workflow set up. Can you explain the steps of the workflow please? When I want to set the variable to be the [nextNumber] i must specify a value instead of that the workflow will automatically get the value of that column.

    Thanks a lot.

    Reply
  • 6. ThiNg  |  October 23, 2008 at 5:55 pm

    @Kevin

    Hi Kevin,

    This should be a pretty straightforward setup but I can understand the frustrations. I just don’t think I understand your question. When you set the variable [nextNumber] it should get it’s value from the counter list you have created.

    If you are stuck, email me directly sanjman [at] msn [dot] com.

    Good Luck!

    Reply
  • 7. EricL  |  December 2, 2008 at 9:30 pm

    Hi,
    I need to increment a counter in a sharepoint list item when I submit an InfoPath 2003 document into a form library. Requirement is somewhat similar to your updating a “next value” in a list, but how can I do this from the “code-behind” of my InfoPath 2003 form?

    I already pull the counter value into my code-behind from a custom “environment variable” SP List. And I increment an internal value in the C#. But how do I update the SP List item?

    My requirement is merely to keep track how many total submits to a form library under certain conditions. Many different documents in the library with each possibly being submitted multiple times. Many different conditions.

    Seems like a good place to keep the counters is in SP List items.

    Any help would be greatly appreciated!

    Reply
  • 8. SClark  |  February 25, 2009 at 5:23 pm

    What did you do to ensure that no two(or more) users simultaneously populated their variable at the same time? (Step 6)

    Reply
  • 9. ThiNg  |  February 26, 2009 at 4:44 pm

    SClark,

    I work for a small company and up until recently I haven’t had any situations where two items could be created at the same time. However, this just happened in my program using a workflow. It created multiple items in a list and some ended up with the same id number.

    Because I can predict the workflows occurrence, I was able to configure it to loop through the items and apply the id when it was completed. So create a batch of items in a list, get the nextNumber, increment through the list and update the ids, set the LastNumber in the counter.

    If I was in a situation where multiple users might create something at exactly the same time, you may need to use the built in ID field. In that case, you should also create another field call ItemID (whatever name you want) and have your workflow set the ItemID to equal the list item ID field. This will help is the future when you need to do lookups or comparisons using the ID number.

    I’ll try to post some steps, if I ever have any free time.

    Reply
  • 11. VK  |  August 13, 2009 at 10:32 am

    Hi,

    Did you ever get round to producing an article on workflow design for this routine you setup?

    Reply
  • 12. Deb  |  January 2, 2010 at 5:10 pm

    I am and newby and I too need to know the step by step instructions. I can get this to work for me. I know I’m missing something.

    Reply
  • 13. Deb  |  January 2, 2010 at 5:10 pm

    I can’t get it to work for me I mean

    Reply
  • 14. wiss  |  April 9, 2010 at 7:47 pm

    I’m trying this approach on a document library and i’m always getting “List Item not found” under the “outcome” colum when i try to run the workflow.

    Does any one have a fix for incrementing library objects?

    Thank you!

    Reply
  • 15. LBS  |  April 15, 2010 at 1:26 pm

    I used a similar technique but kept the new number in a separate list so that the average end user didn’t have direct access to it.

    Thanks

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Trackback this post  |  Subscribe to the comments via RSS Feed


Recent Posts


Follow

Get every new post delivered to your Inbox.

%d bloggers like this: