Software Engineer

My photo
Colombo, Sri Lanka
B.Sc.Special(Hons)in IT, MCTS, MCPD, MCP

Friday, September 7, 2012

Create Auto-Increment number column in SharePoint List

Hi Everyone,

This is a common problem faced by lot of guys who has a little programming knowledge. I am going to explain a simple way to crate a auto-increment number column in a custom list without programming. Every time insert an item to the list this will add a unique auto-increment number to column to track the record.

Start out by creating a new column in the SharePoint list you want to have the  auto-increment number in. This must be a number column. Name this whatever you like. The list that i'm using is " Help Desk Tickets" and my column is "Ticket No"


Then create a custom list with any name you want. My one is "Counter".


Counter list has 4 columns Title, Counter, Last Number, Next Number. Title is default Title column. Counter is Single Line of Text column. Last Number is number column and Next Number is a calculated column.

After creating the Counter list I am going to add a new item to the list.


Remember Last Number should be the number BEFORE the one you want to start on. When you added the new item notice the Next Number it now counts +1 from the Last Number.

OK. Then go to the list you are wanting the auto-generated number in, in my case it is "Help Desk Tickets". Open the list in SharePoint Designer. Click "List Workflow" button in the top left corner.


Give a name to the workflow. My workflow is "CounterWorkflow". In the workflow Start Options tick that "Start workflow automatically when an item is created". If you want you can tick "Start workflow automatically when an item is changed" also. It will give a new auto number to changed items too.

Before we start writing the workflow I will create a local variable. To create it click on the "Local Variables" button. Click Add button. Click Name and name it something. I am using "mNumber" as the name and Type is Number.


Click OK. Now you have created a variable called mNumber. This will be used to store our Next Number information so it can be manipulated without actually touching the Next Number fields.

Lets write the workflow.

1. Click on conditions and in common conditions add "if current item field equals value" condition.
In that condition click the "field" and add Title. Click "equals" and add "is not empty".

2. After that click on Actions and in Core Actions add "Set Workflow Variable". Click on "workflow variable" and add "mNumber". Then click on the "value" and click the function button. Select the "Data Source" as "Counter" and "Field From Source" as "Next Number". In "Find the list item section" in message box "Field" is "Title" and type the value as "LTL Folder Number". It looks like following.


When you click on the OK button it will give a warning like this.


Don't worry about it and click on "Yes".

3. Then add another Action. Click on Actions and in List Actions add "Set field in current item". In the action click on "field" and add the number column you want to increment automatically. In my case it is "Ticket Number". Then click on "value" and click on function button. Select the "Source" as "Workflow Variables and Parameters" and "Filed" as your variable. So my field is "mNumber".


4. OK lets add our last action. Click on Actions and in List Actions add "Update List Item". Click on "This List", In the message box select your auto-number list. My one is "Counter". Click "Add" button and select "Set This Field as" "Last Number". Then click on function button in "To This Value".


In the popup select "Workflow Variables and Parameters" as "Data Source" and my variable "mNumber" as "Field from source". You can select your variable.


Then click "OK". Now it looks like this.


Next click on "Field" and choose "Title". Type "Value" as "LTL Folder Number", our new item in the list counter.Now it looks like this.


When you click on the OK button it will give a warning. Don't worry about it and click on "Yes".

We now have a workflow that will pull Next Number from your Counter list. It will then copy it in to the variable and apply the variable to your main list column. Then it will copy the variable back over to Last Number, causing Next Number to increment by 1 or whatever value you choose.

Finally the workflow looks like this.



You have now created a new list and workflow. Now your main list will have a column that auto counts in increments of 1 each time the item is created or based on your conditions.

So now when I add new items to my "Help Desk Tickets", "Ticket No" is auto generated as following.


Have A Nice Day My Friends.

Regards,
Gihan Maduranga.

65 comments:

  1. Thank you. Nicely done with snapshots.

    ReplyDelete
  2. Thank you so much. I followed the steps and get the required desire. I really appreciate it.

    ReplyDelete
  3. Nice. But I don't have SharePoint Designer. Do you know if it is possible without it ?

    ReplyDelete
  4. This post was very well written and extremely helpful. I only wish I found it sooner, would have saved me a lot of pain.

    ReplyDelete
  5. Excellent! This post saved me a lot. Now I made a small change (hopefully is an improvement. Instead of using the NextValue counter in a different list, I just added it as a new column in the same list. The result is basically the same, but with fewer hoops.
    Thanks a lot

    Raul

    ReplyDelete
  6. SharePoint Designer is free and is setup for client installation. My question as I have not yet tried this - is this dynamic, and by that I mean if I have to delete items from the list for whatever reason will the list update the numbering?

    ReplyDelete
    Replies
    1. No this is static. If you delete several items from the list, Nothing happens to the other list items. When you adding new items it will just count from the last number.

      - Gihan.

      Delete
  7. Excellent. Thanks. :)

    ReplyDelete
  8. Hi Gihan,

    Good one actually i have few columns which i want to populate from the list is that possible

    ReplyDelete
  9. Hi, nice tutorial. Used it until I realized i can use the ID column in my views that does the exact same thing automaticaly.

    ReplyDelete
  10. @Magnamux; that is true, however you have no control over the autonumbering. For instance: I can't start at a given value and when an item is deleted it also deletes the corresponding number. That might be undesirable... This excellent tutorial gives you full control!

    ReplyDelete
  11. One of the best tutorials on this that I've seen. Well done!

    ReplyDelete
  12. I have a scenario , I want to delete a row at randomly in the sharepointlist, but it must not disturb the autoincreament scenario, for eg: if I delete the 104 row or list item, the list must update automatically the ticket number.. how can I achieve this?

    ReplyDelete
  13. I am having a problem where last and the first list item has the same value. For e.g. the first list item is 8 and then followed 2,3,4,5,6,7, and ending with 8. So, if the last item is 9 the first item will change to 9.

    any solution..???

    ReplyDelete
  14. don't worry I got it fixed

    ReplyDelete
  15. Many many thanks! This has helped me a lot.

    ReplyDelete
  16. When I add a new item, the 'ticket' field alternates between 0 and 1 for each new item. It does not increment. I re-created the workflow twice.

    ReplyDelete
  17. When I add a new item, the 'ticket' field alternates between 0 and 1 for each new item. It does not increment. I re-created the workflow twice. (re-posted after signing in)

    ReplyDelete
  18. I'm having the same problem, Randy. I'm using a counter list to update an infopath document library list. Did you ever fix it? Thx

    ReplyDelete
  19. I have not been able to get it to work yet. I am not using Infopath. It's a custom list, not a document library. And, I'm on SP 2010, if that makes a difference

    ReplyDelete
  20. I am facing an error "Failed on Start" or "Workflow Cancelled by System account". When i create a new item, Values are not getting automatically increased instead i am getting the above error and the value shows as "notitle". Please advise

    ReplyDelete
  21. Hi, when I add a new item, I get Error Occurred. No other more helpful error messages. Is there anything you suggest that I check? THanks

    ReplyDelete
  22. I have narrowed it down to Next Number. When I change the lookup to Last Number with the data type of Number it works. Next Number is a calculated field but outputs to Number with 0 decimals as shown in the screenshot. Once I set the lookup to Next Number my workflow is Canceled.

    ReplyDelete
    Replies
    1. with the following error: Coercion Failed: Unable to transform the input lookup data into the requested type.

      Delete
    2. Problem solved. Lookup Last Number then do a calculation action to add 1. Therefore the Next Number is not required.

      Delete
  23. Anyway we can change the Ticket to be related to the username/ login? count the number of items 4 each user in the list & add 1 to it?.
    Thank you.
    for example : User1-1 ; User2-1 ; User1-2 ;User3-1...

    ReplyDelete
    Replies
    1. hi jihane & jihan,

      I have the same issue, but in my case it is related to the year, I mean : 2016_1, 2011_1, 2016_2, 2011_2, 2008_1...

      We need to create a liste to archive old reports with related informations, but reports must be inserted with an incremental code that describe the year and the nature of the report.

      Frankly I dont know exactly what to do,I think that we will need counters for each year and this is not practical, the list will be large and the workflow too.

      do you have an idea?

      thank you in advance.

      Delete
  24. This comment has been removed by the author.

    ReplyDelete
  25. This comment has been removed by the author.

    ReplyDelete
  26. This is great! Thank you for get this set up with the screen shots. I have a question: I am taking the item and copying it to a different library, however, the new number is not copying. Do you know what I can do so that I can copy this new "incremented number" to another library

    ReplyDelete
    Replies
    1. Disregard. I figured out my issued. I needed to re-publish by workflows. Thanks again for this technique!!

      Delete
  27. Belay this. Start with MS Access table designer. Set up your columns there bearing in mind the formats allowed by SharePoint. Set your primary key to auto-number and ensure that the table recognizes the column as the primary key (you'll see the key beside the column name). Add data if you like. Export the table to your SharePoint site as a document list. The new list will then have two auto-number fields, _ID, and what you just created. NOTE: If moving a legacy table to SharePoint, the conversion re-indexes your primary keys back to zero; bye-bye master/child relationship. Restore the relationships by joining the list column "Old ID" on the foreign key in the child table and run an update query replacing the old FK value with the re-indexed value. Bada-bing, bada-boom, full speed ahead. You now can empower MS Access as a front end and SharePoint as a data farm. Have a nice day.

    Dean Peters

    ReplyDelete
  28. Thank you, this is helpful.

    ReplyDelete
  29. Thank you very much, this was very helpful!

    ReplyDelete
  30. This was a great guide, very easy to follow. But i am having issues with my list not having the "Ticket No" field updated after the workflow is run. Did the guide, step by step and still having issues.

    Any and all help is appreciated! Thanks

    ReplyDelete
  31. Hi Gihan,
    Is it not possible to accomplish the same result without using a workflow? What if the workflow stops for whatever reason? Can one not create an auto increment number column using lookup parent/child lists with lookup columns?
    Just wondering.
    Thanks.

    ReplyDelete
  32. What a waste of time. I cant get this to work at all. Is this a joke?

    ReplyDelete
  33. Great.Thank you very much, its was helpful..!

    ReplyDelete
  34. the workflow works perfectly but some time it gives following error if i try to make some entries very quickly,
    "the workflow could not update the item, possibly because one or more columns for the item require a different type of information."
    What if i want my id in following formate. AssetPC0001,AssetPC0002 and so on..

    ReplyDelete
  35. Hi Gihan,

    Can you write the same workflow in SharePoint designer 2013?

    Regards

    ReplyDelete
  36. Thanks it Worked for me with MOSS 2007.

    ReplyDelete
  37. I get this error when creating a new record in the Help Desk Tickets list: "Coercion Failed: Unable to transform the input lookup data into the requested type."

    I have verified all columns are the proper type as described in the instructions. Any ideas? Thanks!

    ReplyDelete
  38. This comment has been removed by the author.

    ReplyDelete
  39. Worked for me with SPD 2013. Thanks!

    ReplyDelete
  40. Excellent blog. I used SPD 2010 and it worked. To understand the logic, I imagined the Counter list fields as variables.

    ReplyDelete
  41. Hi,
    Your post is ok for one item added.
    Have you the solution for a multiple uploading ?
    Thanks in advance

    ReplyDelete
  42. Works quite well for me in SharePoint 2013 Online. however I need to press F5 a couple of times for it to trigger the workflow and insert the new number in my Ticket Number field. I built the workflow using SPD 2013. I just had to interpret the screenshots that you posted and translate them to the 2013 interface.

    ReplyDelete
  43. Holy Camoly! This worked! Of course, I had to refamiliarize myself with SPD, and then to realize that simply saving the workflow was not enough ~ it had to be published, too. Thank you!

    ReplyDelete
  44. Thanks. One suggestion. Switch the order of the updates -- updating the counter before you set the ticket number.

    I implemented this for a document library. I ran into an issue where the workflow could not complete because the document was locked (still being edit mode by the user.) Meanwhile, another document was created and read the counter. Once the first document was released, both workflows completed. However, both updated with the same Next Number. The result was duplicate ids.

    By updating the counter first, Next Number was updated before the lock and delay of the first workflow and before the second workflow read the counter.

    ReplyDelete
  45. So this works, very well, when it is meant for single submissions at a time, gives the same autoincremented number for multiple entries that are added at the same time,

    Do you have any solution that can help with multiple submissions at the same time?

    ReplyDelete
  46. Hi Gihan,

    Many thanks for your tutorial about generating an auto incremental number column. This is a great tutorial. However can you explain how we can upgrade this workflow to generate auto numbers according to the groups, like APAC 1, EMEA 1, US 1. Not only as 1,2,3,4 and so on.

    Thanks,
    Chiranthaka

    ReplyDelete
  47. Thank you so much for your Post, i used your above solution but when i edit the item its auto generating new number for current item.

    For example, if my next number is 11 and it will be taken by the newly created list item, but when i edit the same list multiple times it will auto increment the list item and from 11 it will jump to 17 or 19.

    ReplyDelete
  48. Thanks Gihan, this works great and a very good tutorial even i managed to build the workflow.
    I have one problem when i open a excisting record and add some new item(s) then the number = +1). Where does it go wrong?

    ReplyDelete
    Replies
    1. I found the solutions. It was in the startoptions of the workflow. Thanks.

      Delete
  49. Worked fantastic for me. Prior to this I was pulling my hair out. Thanks very much!

    ReplyDelete
  50. Great solution and awesome screenshots! Thank you so much for putting this out there. 5 years later and people are still using it :)

    ReplyDelete
  51. may i know how to encounter if multiple users triggered the workflow at the same time? i have tried it, it will duplicated the running number id

    ReplyDelete
  52. I have same problem . in case of bulk upload, IDs are being duplicated

    ReplyDelete
  53. Many many thanks Gihan Maduranga for this post. The explanation was thorough and also the steps. I will try it on Office365 SharePoint list too. Once again thanks!

    ReplyDelete
  54. Thank you for the excellent tip.

    ReplyDelete