SharePoint: Simple Sample Approach to Autonumber

Below is a formula for a quick and dirty autonumber for lists in SharePoint. There are a variety of ways to generate this field. Some solutions use workflow or external lists, but I prefer this method because of its simplicity.

Note: Because this solution depends on the record’s ID column, it generates the autonumber *after* a record has been added, so if you need the autonumber to appear in the “New..” form, this solution is not for you. My project, which required the Request Number to be in “WR-0000” format, was fine with having it generated after the fact.

To create my Request Number, I created a new calculated column, named it Request Number and entered the following formula:

=CONCATENATE(“WR-“,REPT(0,4-LEN(ID+1)),ID+1)

This formula combines WR- with leading 0’s and the ID number that’s built into SharePoint lists. The output looks like so:

Thanks to my main SharePoint man, Trevor, for giving me this entire solution. You should start a blog, yo ;)

Chrissy is a PowerShell MVP who has worked in IT for nearly 20 years, and currently serves as a Sr. Database Engineer in Belgium. Always an avid scripter, she attended the Monad session at Microsoft’s Professional Developers Conference in Los Angeles back in 2005 and has worked and played with PowerShell ever since. Chrissy is currently pursuing an MS in Systems Engineering at Regis University and helps maintain RealCajunRecipes.com in her spare time. She holds a number of certifications, including those relating to SQL Server, SuSE Linux, SharePoint and network security. She recently became co-lead of the SQL PASS PowerShell Virtual Chapter. You can follow her on Twitter at @cl.

Posted in SharePoint
26 comments on “SharePoint: Simple Sample Approach to Autonumber
  1. Pascal says:

    I cannot get this formula to work since the ID is always 0 when an item is added to the list

  2. Cindy B. says:

    Hello, when I used the formula (modified version) for the first it worked just fine. the numbers were showing on the list. However, the next time I open the SharePoint, numbers for all items end in -0001 and I have to go to the list settings and re-save the formula so that the numbers are shown correctly every time I open the list. Is there a way I could fix this issue?

    My version: =CONCATENATE("12-001-",REPT(0,4-LEN(ID+1)),ID+1)

    Thank you,

  3. Ben says:

    Wow. What a simple solution.

    Thanks!

  4. M FIx says:

    if items are deleted and new ones are added the numbers do not correct so this is more of a static field – do not delete scenario. has anyone heard of a more dynamic id column that will auto number and auto update after items have been deleted…?

  5. Raghu says:

    Hi All,

    How do i generate a auto incrementing number to display in sharepoint list newform?

    Thanks,
    Raghu

  6. Raghu says:

    Ho do you display ID in the NewForm.aspx

  7. Rosa says:

    Yep this option is not functional because it doesn't work for new items :(

  8. TroyC says:

    AWESOME! Great solution! thanks!

  9. Secret says:

    That you for your yoru comment. I was able to add a string before the sharepoint auto gen numbers. I am using Sharepoint 2010. Extremely Helpful!

  10. Lily says:

    I am also running into the same issue as the Cindy, Seema and others. Is there a solution?

  11. Ronald says:

    Just what I was looking for. After looking at “solutions” that ran for pages and pages, the KISS principle still regeins supreme. Thanks for your post.

  12. Mike says:

    It generates the autonumber *after* a record has been added. I am ok with scenario,no need for new form, however how can you trigger this auto generate of numbering ?

    When I save my form, nothing happens.

    Thank you in advance.

    • Keith Hudson says:

      Recent versions of SharePoint (2013, Online) do not allow you to use the ID column in a calculated column. I get around it by adding a single line of text field to my lists called SPID, which I populate with a workflow that fires when a new item is created, and whose only job is to set SPID equal to ID. Then I can use SPID in a calculated field like the one shown in this article with no problem.

      • Keith Hudson says:

        Simply substituting SPID for ID in the formula given in the article did not work, so I used this formula instead:
        =”DHHS-“&REPT(0,4-LEN(SPID))&SPID

        I am using SharePoint Online, and it is 8/16/16 today.

  13. Mike says:

    Ronald,

    How did you get it work ?

    Every time, shall I save manually the Calculated value [Column] in order to update the correct request numbers ?

    Please help me.

  14. Mike says:

    Ronald,

    How did you get it work ?

    Every time, shall I save manually the Calculated value [Column] in order to update the correct request numbers ?

    Please help me.

  15. Ronald says:

    Sorry Mike, I got all excited and then I really checked the results, and it is renumbing the list when a new document is added. Let you now when I “really” get it working right.

  16. Pam says:

    Has anyone figured out a workaround to this? I am having the same problem.

  17. Sir Poon says:

    As other stated above, when a document is edited and re-saved the ID will likely change. Also, what happens when you export this list/library and import it into a different site collection…or restore it from backup…or move it to a new farm…? The only way I might entertain using this is if a workflow or timer job came along and permanently wrote this value into a Organizational ID Number field or some such.

  18. John says:

    Hi, how do I convert an autonumber to a text, the Idea is, I want to create an auto number so that I can create a lookup. For the lookup to work, it means once the autonumber has been created, I need to convert it to a text… so the lookup can work.

  19. Scott says:

    This can be frustrating but the solution I use runs the workflow after the item is created. To be sure it works all the time, I simply created a field (NewID) that gets sets every time a record is created. The workflow is simply’Set NewID to Current Item:ID’. The field ‘NewID’ is used in my calculated field (for example, calculated field is =”Record-“&TEXT(Created,”yyyy”)&”-“&NewID

  20. JC says:

    Scott, Great post. thx!

  21. Joe says:

    Hmm… List item ID’s won’t update until after the list item has been created, therefore is the reason this calculated field won’t work unless the formula is continually refreshed. Looks like Workflows are the way to go:

    https://social.technet.microsoft.com/Forums/en-US/dd2299e2-8465-4eff-9378-349156a2c3ca/id-not-updated-in-calculated-column?forum=sharepointgeneralprevious

  22. Anu says:

    This does not work if you want a new ID number to be created using the generic ID column. Much easier to create a simple one step workflow to set field.

Leave a Reply

Your email address will not be published. Required fields are marked *

*