SharePoint lists come pre-equipped with a unique naming convention for each list item. The unique ID on any SharePoint is a GUID, a “Globally Unique ID”, which identifies the item as unique from anything else in the system. (Lists also have GUIDs, as do Sites, Views, Workflows… everything has its own GUID, which is how SharePoint identifies all of its pieces. It is also why workflows built-in SharePoint Designer aren’t portable – when its start is triggered by the updating of an item in a list, it is actually triggered by the update of an item in the list with the GUID that was specified on creation. But I digress…)
The problem with GUIDs is that, in order to be truly unique, they are long and not really human-readable (nor are they intended to be).
I recently had a requirement to create a unique ID auto-incrementing for items in a list, in a particular format. (In this case it was the ListNameAbbreviationYear-IDNumber, for example LNA2010-0001, LNA2010-0001, LNA2010-0002, LNA2010-0003, etc.).
There were a number of approaches I could have taken with this, as this particular list was an InfoPath form library.
Approach One – “Pure” Workflow
This approach works for both InfoPath form libraries and all kinds of SharePoint Lists. I actually used this approach on a subsequent request (once word got around that one group had an auto-incrementing list, everyone wanted one!). The unique numbering system requires the creation of a SharePoint list (which for convenience sake we will call “Unique Number List”), that has only a few fields. Since workflows can’t manipulate strings worth a damn (see my post Use Calculated Columns to Close Technical Gaps in SPD Workflows – Another Solution), for this approach I use a calculated column in the list to create the name string. I changed the Title field name to NextNumber, added a calculated column called FollowingNumber, and a calculated column called NextUniqueID.
Calculation for FollowingNumber:
=NextNumber+1
Calculation for NextUniqueID:
=CONCATENATE("LNA",YEAR(Modified),"-",LEFT("000000",6-(LEN(TEXT(NextNumber,"0")))),TEXT(NextNumber,"0"))
or
="LNA"&YEAR(Modified)&"-"&LEFT("000000",6-(LEN(TEXT(NextNumber,"0"))))&TEXT(NextNumber,"0")
When you create a single item in the list with a value of “1” in NextNumber, this code yields a value of LNA2010-000001.
Let’s pull this formula apart, shall we? First of all, the reference material you will need is here and here.
You could use either the CONCATENATE function, or you could join the strings with an ampersand. The result is the same, which you use is up to your personal preference.
Next, let’s examine TEXT(NextNumber,"0"), which is used twice. NextNumber is, of course, the value of the NextNumber column of the list item. The “0” is the format parameter of the TEXT output – in this case, an integer. If we had wanted a dollar value, the format would be “$0.00”; if we had wanted a decimal, would could have made the format “0.0”. So this would take a number like 1 and turn it into the string 1, or the number 53 and turn it into the string 53.
From the beginning then: we have a string “LNA”, to which we append the value of the YEAR the list item was modified, followed by a dash. Then comes the tricky part.
We want the number value to be padded with zeroes so that the number is X characters long (in this case I have set the length to 6). LEFT() gets the leftmost characters of the first argument (“000000″). The number of characters is the second argument, 6-(LEN(TEXT(NextNumber,”0”))). This argument starts with the total length we are going for, and reduces it by the length (LEN) of the TEXT string I explained before.
So, for the examples I gave above for the TEXT strings, 1 and 53, the LEN values are 1 and 2. Subtracted from 6, this leaves 5 and 4. The LEFT() function returns “00000” and “0000” respectively, for the examples. This string is appended to our “LNA2010-” string, followed by the number (1 or 53 from the examples).
You can tell my programming background, since I explicitly recast the numbers as strings. You could get away with a slightly simpler ="LNA"&YEAR(Modified)&"-"&LEFT("000000",6-LEN(NextNumber))&NextNumber, but then you are relying on the SharePoint environment to handle things gracefully, which can rarely be depended on… 🙂
So now we have our Unique Number List, which has the next number, the unique name, and the number
The workflow is configured to start on item creation in the list you want unique numbering. It gets NextUniqueID and saves it as a workflow variable, then updates list item in the Unique Number List, setting NextNumber equal to FollowingNumber. Then the workflow sets the Title (or other field) of the new item to the workflow variable (the value of the NextUniqueID from Unique Number List from before we changed it).
Approach Two – Pure InfoPath
This uses a similar “Unique Number List”. The only difference is it does not use the calculated column called NextUniqueID. It still has the Title field name changed to NextNumber, and the calculated column called FollowingNumber.
This approach requires setting Submit Options Rules (Tools >> Submit Options >> Rules >> Add) and/or rules for your own custom submit button (if you have one), as well as a data connection to the “Unique Number List”. You will also need a field in the form data source for FormNextNumber and FormID.
Set up the data connection (Tools >> Data Connections >> Add >> Create a new connection to >> Receive Data >> Next >> SharePoint Library or List >> List URL >> Next >> Unique Number List >> Next >> select the NextNumber field >> Next >> uncheck “Store a copy” if checked >> Next >> Name your connection >> Finish).
The Rule for the Submit button(s) has the condition FormNextNumber is Blank.
The first action is to Query using data connection: ConnectionName (Add Action >> Query using data connection >> select Connection Name >> OK). There is a reason to do this at submit rather than on frm opening. Suppose it takes an hour to fill out the form (beacuse the user is slow or got interrupted). How many others will open forms in the meantime? Better to make it happen on submit, where you are unlikely to have a “collision”.
The second Action is to set a field’s value: FromNextNumber = @NextNumber (Add Action >> set a field’s value >> Field: FormNextNumber >> Value: fx >> Insert Field or Group >> Data Source: ConnectionName >> myFields >> datafields >> Unique Number List >> : NextNumber >> OK >> OK >> OK >> OK)
On the FormNextNumber field, set a Rule (Properties >> Rules and Merge >> Add). The condition is FormNextNumber is not blank. The Action is set a field’s value. The field is FormID. Click for fx button for the value.
Here is the formula:
concat("LNA", substring-before(Date, "-"), "-", substring("0000", 0, 5 - (string-length(FormNextNumber))), FormNextNumber)
Look familiar? Once you have selected the FormNextNumber field, the UI replaces the field name with an underlined period “.“, so don’t be surprised by that. It is basically a shorthand for “self”.
You create by starting with Insert Function, selecting concat, then replacing the fields with the text and functions to complete the formula. One thing to note, “Date” in my formula is a read-only form field called Date, which has a default value of today().
For a pure InfoPath solution, you will also need to change the value of the item in the Unique Number List from the InfoPath form, so you would need to do some code behind to make that work, which I won’t go into. It is much easier to use the third approach…
Approach Three – Hybrid
You can combine these approaches to have the workflow do some of the work, and InfoPath do some.
For instance, as I just mentioned, you can set the workflow to update the Unique Number List, which is easier than doing the code behind in the InfoPath form, with the InfoPath form doing the rest.
Other Variations and Considerations
You can also make the unique name field on the target list a calculated column like the NextUniqueID by feeding the NextNumber (either via a workflow or the InfoPath data connection) to another column in the target list.
One thing that it is important to remember is the SPD workflows run in the context of the user filling out the form or adding the list item, so anyone who had rights to add list items needs to be able to edit the item in the Unique Number List. But what you don’t want is for users to go to the list and manually change the number!
One way to control this is to set the default view to use the version of the NextNumber field without the “(linked to item with edit menu)” or “(linked to item)”. In order to lock this down fully, only users you trust to not mess things up should have either the Manage Lists or Manage Personal Views permissions on the list.
To get rid of the Manage Personal Views permission, you will have to either remove the permission from the Contribute permission level (which will affect your site collection), or create a custom LimitedContribute permission level (give it your own name if you like) and apply that to the Unique Number List.
Finally, the unique number as I have it needs to be reset each year. There are a number of ways to accomplish this as well, from a manual process (ummm, no) to having the workflow check the Last Modified date and compare it to today(), check to see if the years match, and, if they don’t match, reset the number.
Hopefully this will get you going in the right direction. Get creative, as your business needs may vary! If what I have said is unclear, or you run into problems, post a comment below, and I’ll do my best to help. And if you get value from this, you can comment on that below, as well!
Need more info? I just posted an update with screenshots!, and a new post using an event handler!
The Custom Unique Automatic ID Trilogy
Add a Unique Auto-Incrementing Column to a SharePoint List
Auto-Increment Workflow Screenshots
Autonumbering Event Handler
Working It Out 
Comments on: "Add a Unique Auto-Incrementing Column to a SharePoint List" (87)
There is some discussion on the SharePoint 2010 Network LinkedIn group, including a reference to another blog post from a couple of years ago with a very similar solution to mine (without the concatenation).
[…] Add a Unique Auto-Incrementing Column to a SharePoint List […]
[…] My Top 10 SharePoint Posts […]
[…] Add a Unique Auto-Incrementing Column to a SharePoint List […]
I’m new to this and struggling a bit with the lack of details with the last section in option 1 – can you help me shed some light?
“The workflow is configured to start on item creation in the list you want unique numbering. It gets NextUniqueID and saves it as a workflow variable, then updates list item in the Unique Number List, setting NextNumber equal to FollowingNumber. Then the workflow sets the Title (or other field) of the new item to the workflow variable (the value of the NextUniqueID from Unique Number List from before we changed it).”
Any help is greatly appreciated.
D
This is a SharePoint Designer workflow.
When you create a SPD workflow, the first option in the workflow wizard is a decision about which list/library you are attaching the workflow to, and a choice of what condition causes the workflow to start – new item, edit item, or manually. In this case, when a new item is created.
Next, in the workflow steps, pick set a workflow variable (I’m not at my work computer, so bear with me if I don’t have the exact terms correct right now, I’ll correct this response tomorrow morning, but it should show you the way). Give the variable a name, and set the value to NextUniqueID from the Unique Number list. The next step is to update the value of field in a list item – the list is the Unique Number List again, and the field to update is NextNumber. Give it the value of FollowingNumber. Next, update a field in the current item – the field is yout Title field (or whatever field you are generating a unique number for), and the value you assign to that field is the value of your workflow variable (which came from what the NextUniqueID field used to be before the workflow changed the value of NextNumber).
Hopefully this helps, let me know if it doesn’t.
hi Jim.
I am new to it, can you please elaborate more to it, perhaps through screenshots.
I have a second post in the series that shows screenshots, appropriately titled Auto-Increment Workflow Screenshots. Let me know if the process is still unclear.
This is exactly what i am looking to do. Workflows are great. I too am not a developer. Have you had a chance to validate the workflow steps? I keep getting errors in the custom list. I am using infopath to create a user database and need to create a unique id (example vs-10000).
We are using this workflow process on both Infopath form libraries and lists in our production environment (and I’m about to implement on another document library). What errors are you getting?
Thanks for the quick response Mr. Adcock. I have three columns that i am using. 1. title = billet number, 2. following number, 3. unique number. I followed the directions from above. I created the workflow with following steps.
1. set variable to equal unique number
2. update billet number = following number
3. this step is where i get lost. i think update billet to unique number.
Any help would be great.
So:
1. You have captured the unique number you want to apply to your list item.
2. You have updated the item in the unique number list (the separate list that holds the next unique number in the sequence)
2a. By updating this the uniuque number field in the unique number list has been recalculated, correct?
3. Have you applied the unique number from the workflow variable to the list item? (Update list item, current item, set your Title (or whatever your target field is) to the value of the workflow variable)
your ideas would work. But got alittle complicated for me. But thanks for heading me down the right path. Here is my InfoPath solution to this issue.
http://rnbergren.blogspot.com/2011/09/needed-one-up-auto-increment-number-in.html
Elegant solution! I’ll need to look at the applicability of the MAX() formula in my auto-incrementing solutions. I’ve only recently started using MAX() in spreadsheets, and hadn’t thought of being able to use this in InfoPath, and don’t know if it is available in InfoPath 2007. (Rod’s solution uses InfoPath 2010).
Rod – Noticed something today – your solution is only for lists, isn’t as easy to implement for document libraries. Too bad, it is a great solution!
What about this solution and document libraries? I tried it today on a document library. There are 2 date fields in the row, whose values get deleted when my workflow runs. For the life of me I cannot figure out why. Is there something I am not seeing that this solution will not work on document libraries?
Ray –
Can you provide some more details about the problem you are having?
Sure. I did just as this post suggests, with the special list to contain the unique number. Then I wrote the WF (against a document library), where I created a field to contain the unique ID. The WF does exactly what it is supposed to do, update the unique value on the document library list then update the unique number list in preparation for the next new document to be posted. All is fine, except that there are 2 date fields in the document libary list. The contents of these date fields are blanked out by the WF, even though they had data in them and there is no code int he WF that would do this. I have a lot of experience with SPD WFs and just cannot figure out why this is happening. I can only surmise that there must be something unique about document libraries that causes this NOT to work. The document library does not require a check out. I also tried inserting a 1 minute duration in the WF thinking that the system may need to post the new document (check it in the first time), then the WF runs. But that did not change the problem.
Have sent you an e-mail requesting screenshots. If you can reply with them, I’ll try to figure out the issue.
Jim, after several suggestions, what finally worked was the following. At the beginning of the WF, I stored the 2 date values in temp variables. Then at the end of the WF, I wrote the contents of the temp variables back into their respective fields in the list. This worked. The work around is great, but I convinced that this is a bug in SP. Clearly somthing in a SP event is causing date values to be wiped.
Thanks very much for your help. It is greatly appreciated. It’s nice to know there are SP experts willing to help and share. Thanks so much!!
Ray
Just to add detail – there were two date columns on the target library. Users input data for the two date fields when uploading a document to the library.
The workflow correctly provided the unique number to the document, but after the workflow completed, the two date fields had lost their data.
This seems to be an SPD workflow issue. It feels like the WF is grabbing the before properties of the fields (prior to data entry by the user), applying the steps of the WF, and applying those changes to the “before” values, then pushing all that back to the document (thereby overwriting the dates with empty values).
I have no hard evidence to show that this is so, and it is weird that the issue only applies to date fields.
I’m glad the work-around we concocted worked out!
Jim,
Not to confuse more, but interestingly when the WF ran for documents previously loaded into the library and with the 2 dates previously specified, these 2 date fields are also summarily wiped by the WF. So it doesn’t appear to be a WF issue of getting the before properties. This would be a valid assumption for a new document, but doesn’t hold true for pre-existing documents. And for the pre-existing documents, the WF properly updated the unique value, but it wiped the contents of the 2 date fields.
Ray
Freaky! Now I really wish I had a 2007 environment to test this against.
But the workaround we figured out still works?
Yes sir, the workaround works great. And I am only using it for new documents posted. Thanks again for your help.
Hi Jim, Can I ask how you would make this work for a standard list view without infopath or workflow? Just a manual data entry list.
With a standard list, you would still need workflow, unless you were ok with missing numbers if a list item got deleted, and for the numbering being limited to that list (you couldn’t use the same numbering scheme on multiple lists and still have unique numbers across all)..
You might create a calculated column (using the kind of formulae shown) and referencing the [ID] column… something like “String” & “000” & [ID].
I have decided to go with the workflow, but please excuse my ignorance. I cannot get this to work. I have created the seperate Unique Number List, and that does as it is expected to, I can create a new entry and it populates FollowingNumber and NextUniqueID with the calculated columns.
I have another list that as I add and save a new item, I want an item field to populate with a newly generated value in the Unique Number List.
In SPD, I created a workflow action and set a variable to NextUniqueID on the Unique Number List
(Source:Unique Number List, Field:NextUniqueID)
Then it springs a section called “Find the List Item”
(Field:Unique Number List:NextNumber, Value:FollowingNumber)
I added another action “Update List Item”
This is where I am falling over.
It is set to
List: My Main List
I added a value assignment thus:
Field:MyListUniqueId, Value:(Source:Unique Number List, Field:NextUniqueID)
Then it springs a section called “Find the List Item”
(Field:Unique Number List:NextUniqueID, Value:FollowingNumber)
Finish
In my Unique Number List, a new column is created with the workflow status, which shows an error with “Error updating a list item – List item not found”
I think I am confusing myself 🙂 I hope you can put me right.
Thanks
I think I see where your problem is. When you are doing the lookup fromt he workflow to the Unique Number List, the workflow asks you to identify which item in the unique number list you are going to get the NextUniqueiD from. At the moment, there should only be one item in the list, so you’d think that it would be easy for the workflow to find the item, right? I mean, duh, there’s only one item there, right?
Except that isn’t how it works. You still have to tell the workflow how to identify the item you are looking for in that list. You appear to be trying to identify the item by its value in FollowingNumber field. The only problem is that number is going to change, so it isn’t going to find the list item you are looking for – that number no longer exists.
Instead think of it this way: for the sake of clarity of discussion, we’ll say that the Main List is “Invoices”. Lets say, in the future, your end users LOVE the unique numbering and want to add unique numbering to “Contracts” (another list/library), specifically, its own number system separate from the Invoices. You’d need TWO items in your unique number list to keep track of the separate unique numbers (or a whole separate list, which isn’t the best way to design it, IMHO). In this scenario, you would need to be able to identify WHICH unique number was being called for, even if the workflow didn’t need it anyway. You need a column in your Unique Number List to identify which unique ID that list tiem creates. In the example here, you might create a column called “List” and the values would be “Invoices”, “Contracts” and so on as you add clients to your unique numbering system.
Since the workflow requires some sort of identifier that isn’t going to change, and you need a column that identifies which list item is being requested, you’ll be taking scalability into consideration if you make the unique column the name of the unique ID client, “Invloices”, “Contracts”, “Main List” or whatever.
I’ll try to get some screenshots to make sure I am being totally clear and post them later today. Since I have a new group that wants unique IDs for their documents, the timing is pretty good. 🙂
If I am misunderstanding the problem, let me know, I’ll work to get you fixed up. We seem to have an opposing time zone issue that will slow us down, but I’ll stick with you until we get it right.
Hey Jim,
Thanks heaps for the great post and I look forward to some screenshots. I’m hung up on the exact Value and Source options of the workflows and variables. Particularly the update list item.
Question for you: Is there any way to get this number to restart once the year changes? I imagine the only solution would to be to create an if function that states that if 2012 you Subtract 1,000,000.
Thoughts?
Thank you,
Ian
There are several of ways to do this. Initially I just left it to do manually. Naturally, when 2011 rolled around it got forgotten and I had to do a little manual renumbering to fix the problem after new items were submitted. (The year in the calculation was dynamic, so after 2010-CAPA0035 came 2011-CAPA0036 (and 37 and 38) instead of 2011-CAPA0001 etc.)
So I set up a manually-started SPD workflow to reset the counter to zero (Pause Until Date, set the date – hard coded – to 2012-01-01 12:00:01 AM), with an e-mail to myself noting the reset had been done and to modify the workflow and manually start the flow again. Neither optimal not good practice, but it was a very quick fix.
In thinking about your question, I think I have figured out a better way. Add a calculated column (date and time) on the Unique ID list called “Reset Date” that calculates the first day of next year. Set up a workflow in the UniqueID list that starts when an item is modified. First step is to pause until the value of the “Reset Date” calculated colunm. After the pause, it resets the number and stops.
Result: every year, in the early morning of January 1, the workflow wakes up and resets the number, then ends its run. The first time a new item is created in the target list, the UniqueID item gets updated, restarting the workflow set for the first of the next year.
Dates are stored in SP in the format : yyyy-mm-ddThh:mm:ssZ T/Z are literals
The calculation for “Reset Date” should look like this:
=CONCATENATE(YEAR(Modified)+1,”-01-01T00:00:01Z”)
Thoughts?
Hey Jim!
I have a problem that i don’t it’s as hard as is discribed above.
I just want a list column that is like ID(auto-incremental when an item is crated, and allways plus 1) but with a diference. The start number is diferent from one(1)!
Can this be done with just workflows or calculated columns or both?
Tank you in advance,
André
If you don’t mind missing ID numbers in your list, you can use the [ID] column (one of the built-in columns in a list) to generate a number. Create a calculated column where the formula is =[ID]+n (where “n” is the seed number you want to start with). Anytime you delete an item from the list, that [ID] number is gone forever, and your math will cause there to be a missing number in your calculated itentity column. (You can even use the formulae I have provided to create non-numeric calculated identity values).
But there will be gaps if an item is deleted.
I just discovered something weird about using the [ID] column to generate a number while I was validating that the above works (I want to be sure I am providing correct info so I don’t make a fool of myself on the world wide web). I created a list, added a calculated column with the formula =[ID]+41 because I’m a big geek and think the number 42 is too funny not to use anytime I can. Then I created two list items. The calculated values both showed as 41. WTF? The [ID] column doesn’t show up as available for calculations in the “Change Column” or “Create Column” pages, and for a moment I was worried I couldn’t use the column. Or maybe I was using the wrong column name. So I went to “Modify This View”, and sure enough [ID] was there. So I added it to the view to make sure that the numbering of the ID column was 1, 2, 3, etc. So here is the odd part. By adding it to the view, the calculation worked as it was supposed to and the the numbers were 42 and 43, respectively. I guess something like changing the view was needed to get SharePoint to actually make the reference to the [ID] column.
Sorry for not having the images yet, I have had a couple of other priorities jump up. I’m going to try to have them up tomorrow. Thanks for your patience, everyone!
André, if you have any other questions or problems, let me know.
Hi Jim!
Tk u very much for the quick answer!
That’s a very good idea! It will work for me ! 🙂
The odd part is about having to show the column for that to work!
But i will do some tests and i will coment on that later! 🙂
Hi Jim!
I was testing what you sugested and i know what is the problem but i don’t know how to fix it!
What happened to you, hapeened to me also, and after showing the column ID, sure enough, the column changed to how it was suposed to work!
The i created more items in the list, and it hapened again!
I tried to change something in that column, and opened the edit. I haven’t changed anything but the column had changed again to how it was suposed!
Tried it again and it always worked when i go and try to edit column..!
Am i getting crazy?!
Tk u very much for your patience!
Interesting. This is VERY similar to a problem MVP Matt McDermott demonstrated in his presentation at the Central Texas SharePoint User Group meeting this week.
He was demo-ing customization of SP2010 search results, and showed how to add tabs for new search pages in the search center. He’s got a blog post on the subject, but the short of it is, in order to successfully reorder tabs on the search pages, you have to reorder the list, but that alone doesn’t do it… you have to then open and save one of the items to make the reorder change actually work.
This may also be related to the issue with the timer job that does the calculations (referred to deep in my post on calculated columns). I’ll try to get a little more testing done to see what circumstances trigger the calculations to be done. As in Matt’s solution, it may require automatically opening and saving the item – meaning a workflow to update the item (but not actually changing anything in the item data) – to reliably make sure the item is correctly numbered.
Hi Jim!
Finally i made it! I did it all by workflow, without calculated collumns, and it is working like a charm!
It’s just a simple workflow that sets the variable “Number” to the number i want “31”.
Then it does a calculation adding the variable to the number and stores it in a workflow variable “num”.
Then just sets Number as Num!
Tank you for your help 🙂
Hi Jim 🙂
I see that’s a very good ideia!
I will try to do that in my test records and i will say if it worked.
Tk U 🙂
Thanks Jim, this was very helpful for what I was trying to do. I didn’t use the work flow just used the ID column to get the number increase. You have made a hard request much easier again thank-you
Richard
Below is what mine looked like for anyone who may be interested
=”Text You wish to appear “&LEFT(“000000″,6-(LEN(TEXT(ID,”0″))))&TEXT(ID,”0”)
The only downside to using IDs is it is unique to the list, not to the site, and if you delete an item, you have a skipped number, which were not acceptable options in the requirements I was given for my projects. Glad I could help!
What is likely happening in Richard’s case, is that he will have to resubmit the calculated column for the Custom ID’s to write to the list items because the ID is not actually created until after the record is written to the database. So until you re-save the calculated column, all new records will simply display without the ID in your custom field.
While I appreciate that Jim’s article is about not using the Unique Sharepoint ID number, and while I will still try the suggested approach at a later date on another project, my immediate needs were met by using the system ID and a hack of Jim’s calculated column and have it write the number to the list item immediately by using a simple workflow.
Create two columns in your list.
One called “CaptureID” (single line of text), this field will capture the ID number that the system generates;
and
one called “CustomID” (calculated column), this field will be used to display the customised ID (of course you can call these columns anything you like) and update the Calculated column with the following formula:
=CONCATENATE(“ITEM”)&LEFT(“00000″,5-(LEN(TEXT(CaptureID,”0”))))&CONCATENATE(CaptureID)
This will write: ITEM000## (where ## is the system ID) to the record
Open Sharepoint Designer and create a new workflow, label it ListIDAutoNumberWrite (or whatever), this workflow will capture the unique ID after the record has been written to the database and add it to the field CaptureID
Workflow Settings:
– attach to the list that your CaptureID column is in
– automatically start this workflow when a new item is created
> Next
– Step Name = “whatever you like”
– Conditions = None
– Actions = Update List Item, click on “this list link”
– List = Current Item
> Add
– Set This Field (CaptureID)
– To this Value / Formula = Source:Current Item, Field:ID
– OK, OK, OK
– Finish
I then hid the field CaptureID from edit, display and newform using a little bit of javascript jiggery pokery, because although the system ID is not editable, the CaptureID column is, and I don’t want my customers mucking around with the customised number.
Thanks Jim for an eye-opening article. I’ll let you know how I get on with your nextnumber solution.
Jim,
It would be much better to create a content type for a list with “auto title” and do the work in an event receiver. A workflow is a huge consumer of server resources, whereas an event receiver is light. You can also protect the newly generated title by setting it to read-only.
Dov
There is a balance here – the technical proficiency and access levels needed to create an event reciever solution vs. server performance.
For the time and resources allocated to the particular project this solution was for, the workflow solution was the “best” solution. Perhaps I should create a solution for codeplex that creates a site column, and adds the event reciever. There has been a LOT of interest in this blog post.
New post with code for an event receiver is up!
Jim,
I have one question. Does SharePoint reassign the Deleted Item ID? I observed that if we are saving the Item using object model, SharePoint reassigned the Item ID. Does it true?
As far as I know, when you delete an item from a list, the ID of the deleted item remains unused, but I haven’t tested this throughly. It may be that, after the item has beed deleted from the site collection recycle bin, the ID becomes available for use in the list, but I don’t believe so.
Hi jim,
=”LNA”&YEAR(Modified)&”-“&LEFT(“000000″,6-(LEN(TEXT(NextNumber,”0″))))&TEXT(NextNumber,”0”) but every time its start from LNA2012-000000
it doesn’t increase…
Are you using the workflow to increment NextNumber? That formula is only to format the number into your custom format.
Hi Jim,
actually I am using
“LNA”&YEAR(Modified)&”-“&LEFT(“000000″,6-(LEN(TEXT(ID,”0″))))&TEXT(ID,”0”)
to generate GUID. I get LNA2011-000000 when I add new row. Is it happening because ID can’t be referenced when using calculated column? How do I avoid that.
Thank You in advance
Yeah, you can’t reference the ID column in a calculated column. My guess is that, until you actually save a new item, the ID column is empty. You could use the ID column with workflow though… set up a WF to run on create new item, set a field (“MyID”, for example) to equal the value of ID, and set the calculated column to get the value from MyID rather than at ID.
thanks,
i just create one new column in list and used this formula..because i want result like
LEN2012-0001..
Increase every time when i entered data entry..
Does the numbering system restart at the next year? E.g. LNA-2012-0001, LNA-2012-0002, LNA-2012-0003,… would 2013’s first entry be LNA-2013-0001 or LNA-2013-0004?
Thanks for the assist!
LNA-2013-0001, we have a separate workflow that waits until 00:01 of 1/1/YY and resets the number back to 1.
Nice article with helpful info but the entire part involving the workflow’s involvement in this process isn’t explained at all. How are you setting up your workflow, especially the variables, in your first approach explanation? If you could explain that as well as you did the set-up of the sharepoint list side, I’d be off and running… From the comments above, that seems like this is the area that most people had questions concerning. Thanks for the post!!
I explained the WF setup in my reply to Dan’s comment, pretty close to the top of the comments list. Was there something I was being unclear about?
Yes, actually. First, I appreciate your quick response. My confusion is that when I try to follow those instructions, I find that in the step where I’m assigning the variable the value of “NextUniqueID”, I’ve got two areas of the workflow wizard that I have to complete: One being the (Lookup Details) “Choose the data source and field to perform the lookup on:” which is my Unique Number List with the field of NextUniqueID, but the second part (Find the List Item) “Choose a field from the selected list and a matching value to identify the specific item you want fromt he list:” also requires a Field and Value pair. I’m confused as to what I’m supposed to enter here. I can’t go forward to the next step to Update w/o completing this one first (obviously). Am I missing something?
Sorry for the delay in response. I decided after writing several hundred words that the response needed to be its own blog post, which, while I had hoped to finish over the weekend, is still in progress. I’m polishing up the rough edges (like my poor spelling), and should have it posted this evening. Screenshots and everything!
Post is up!
[…] most viewed post to date (it will hit 8,000 page views Wednesday), Add a Unique Auto-Incrementing Column to a SharePoint List, has generated a number of questions, including regarding specific steps for setting up the […]
How about replacing
LEFT(“000000″,6-(LEN(TEXT(ID,”0″))))
with
TEXT(ID,”000000”)
That works for ID’s 1-9, but add another digit and you encounter this:
0000009
00000010
00000099
000000100
The LEFT & LEN expressions are to determine the length of the ID number to be assigned, and then padding the number length to have a consistent string size.
0000001
0000010
0000100
0001000
etc.
This was part of the requirement I was given. Different business requirements will yeild different formulae. If you don’t need a consistent number of digits, then you won’t need the added complexity, and you can go with your formula (and won’t even need to pad with zeroes).
[…] instructions for setting up an SPD workflow to provide an automated custom numbering scheme from an earlier post. That earlier post focused more on the calculated column code to generate the number pattern that […]
I have followed the screenshots and am having no luck. I think what is happening is where you put “LNA’ in your Lookups, I am not sure what to put there. Is this supposed to reflect the LNA ID field (you only put LNA so I am not sure if you are typing that in or choosing it from somewhere).
Help
The text “LNA” stands for “List Name Abbreviation“. This text is entirely up to the identification requirements of your business users. If you are doing Change Request forms, you might use “CR” or “Change” or “Request” or “ChangeRequest” in place of the letters LNA. A list of invoices could be “INV”, a list of widgets could be “W” or “Widget”. It is simply a text identifier for what kind of item the item identified is. (It is also optional. One of our kinds of numbers is just – as we call it – a dumb-ber, a simple six-digit number with no dates or prefixes).
The field in the screenshots called “LNA ID” is a single line of text field that will hold your custom ID number. You can call it “Custom ID” or “CompanyName ID” (using your company name, or department name if you like).
Hope this helps, if not let me know and I will try to clarify further.
Thank you , I understand what you are saying and I am using a “CWB” identifier in my calculation feild. My question is around the screenshots that use LNA as the bottom item in the lookup. See picture https://dlairman.files.wordpress.com/2012/02/wf6a.jpg
The last thing in that picture is LNA and i am not sure if you are choosing that from somewhere or Typing it in and if you are choosing it – is it the field LNAID from your list?
Thanks again for the quick reponse
Ah, I see. No, that is text, not a lookup (that would have meant more screen shots!) 🙂
The Title field on the Unique Number List is used to identify which item in the list is being used for its NextUniqueID. Even if you only have one item in the list, you need to identify for the workflow which item you want. (This does make it convenient to host multiple numbering schemes in the same list as well, but I digress.)
I used LNA as the value in the Title field on the item in the Unique Number List to identify that this item is the unique number for the LNA list.
That got you covered?
Sorry, it has been a difficult week and I just want to be sure I understand.
1. It makes no difference what you put there?
2. It does make a difference
If 2. how does the system know that LNA is meaningful since the actual field you use in the list is LNA ID (not LNA).
Not a problem, I’m here to help as much as I can. We’ll work though it until we get it worked through.
The three letters of text “LNA” in the last field on the image wf6a is text to identify which item in the “Unique Number List” you are trying to update. It is a tag, a label, if you will. That tag can be anything (in your case, the prefix “CWB”), but it has to match – the text you put in that box and the label you affix to your Unique Number List item (via text in the Title field).
So your list item in the Unique Number list would have a Title of “CWB”, and the text in the “Value” box in image wf6a (and wf7a, too) would also be “CWB”.
6a, in essence, is saying, look for the item in the Unique Number List that has the Title “CWB” (the value of the title is equal to the string “CWB”), and the get the value of the field called “Next Unique ID” from that list item (so I can store it in the workflow variable).
If you were to create a second list that needed its own unique numbering system, say “CWB2”, you would have a second item in the Unique number list, this with “CWB2” as its title, and the workflow for that list would use the text “CWB2” in 6a and 7a.
The name of your ID field on your target list isn’t particularly relevant, though you do have to select it in the lookup (images wf8 and wf8a).
Clear as mud now?
Sorry- I just recreated the whole thing from the beginning, using the exact title’s and names of all that you used and no love from the workflow. I appreciate your kind attempt to help me but I am apparently not getting it.
I keep getting “Error Occured” in the workflow.
I am using lists and no a library and sharepoint 2010 so maybe there is just something that does not translate.
Thanks so much for helping me get this all working, talk about going the extra mile. It is a good solution from a genuinely good guy!
Thanks again,
Debi
Turns out part of the isssue was the need to have a list item in the Unique Number List to hold the unique number. I’ll be reviewing my post to make sure that is made clear.
Then once that was resolved, the workflow actions that read from and update the Unique number List needed to be deleted and recreated. SPD seemed to have been holding on to some sort of prior configuration information that made it throw errors when the workflow was run.
Glad I could help!
[…] Add a Unique Auto-Incrementing Column to a SharePoint List « Working It Out. […]
[…] – 12 days. Three other posts joined the 1k+ page views club, and the leader of the pack, Add a Unique Auto-Incrementing Column to a SharePoint List surpassed twelve thousand page […]
Hi Jim,
I wanted to ask you how to make the column that gets our LNA-0001 style formatting, read only?
It is appearing in the new item field and i sure don’t want anyone to modify it.
please help, i am in a fix
Regards
Anmol
[…] and career management, and I wrote a lot about my presentations. The success of the Year Two post Add a Unique Auto-Incrementing Column to a SharePoint List led to two follow-up posts. And a couple of run-ins with malware yielded two surprisingly […]
Hi Jim,
I would like to know the formula if i want the column to be like this.ECO-DC-T-001.
[…] (3) The unique ID on any SharePoint is a GUID, a “Guaranteed Unique ID”, which identifies the item as unique from anything else in the system. (link) […]
Interesting that my post got referenced by a grammarian…
Hi Jim,
Will this approach work in SharePoint 2010 and if not what could I use to achieve a similar result
I have used this approach successfully in SP2010 and SP2007. I also used the event handler approach in SP2010. Which one you use depends on the requirements for your solution.
Thanks for the great tutorial, I learned a lot from the chain of comments as well. I am trying to incorporate a modified version of this auto-increment column to determine an order from column values. Essentially I am trying to mimic the large/small commands in excel based on the number of entries. My engineering mindset is probably overcomplicating it, but between playing around with workflows and calculated columns (and searching the web for help) I haven’t made much progress.
Essentially I have a projects list which have a priority value, these are meant to be unique but I want the functionality so if a user enters a duplicate value, the remaining items in the list auto-increment their priority value accordingly (very similar to the functionality you see in the modify view screen when reordering the displayed columns). Any help or insight is greatly appreciated!
I’ve been giving thought to using alternate methods to determine the next number in a sequence, so your question comes at a good time.
A possible way to implement this might be a workflow that starts on change. Let’s say you create an item and it’s Priority ID is 15, bottom of the list of 15 items. later, it is determined that it should be prioritized higher on the list, at 7, so you edit the item and change the Priortity ID to 7. This starts the workflow that looks for an item in the list with the number 7 and changes it to 8. This modification of the former number 7 kicks off a workflow on that item that looks for 8 and changes it to 9.
The hurdle to this is making sure you don’t change the current item again (making sure the workflow doesn’t identify the new item you modified to 7 as the number 7 it needs to move to 8, and so on). I’m pretty sure you could do that with a list column that stores the old value, and have the workflow search on that value. When the workflow sees you have changed the Priority ID, it blanks the “Prior Value” column, then looks for a list item with a prior value of 7, and modifies that. That change kicks off the second item’s wf which sees that the value has changed, blanks the “Prior Value” column, and so on. After the first wf has updated the old number 7, it then updates it’s “Proir Value” to 7.
This might work better on large lists as compiled code rather than as an SPD workflow.
Alternatively, you can skip reinventing the wheel and check out this codeplex project and see if it meets your needs: SharePoint 2010 Order List Field
I hope this helps!
I like your approach, thanks! The codeplex project would be helpful, but due to company restrictions I can’t use that kind of custom code. I’m not sure where I’ve gone wrong with the workflow, but all it’s doing is reporting an error. I’m sure it’s something simple I’m overlooking but I’ve combed through my workflow a dozen times and can’t figure it out. Your expertise would be greatly appreciated (thanks again in advance). Here’s the breakdown:
Columns:
Priority ID
Prior Value
Priority child (calculated column of Priority ID + 1)
Step 1
If Current Item:Priority ID equals Current List:Priority ID
Update item in Current Item
(where Current Item is Prior Value = 0)
then Update item in Current List
(Field Priority ID to Current List:Priority Child)
Data Source:Current List
Field: Priority child
Find Field: Prior Value
Find Value: Current Item: Priority ID
Find the List Item Field: Prior ID
Value: Current Item:Priority ID
It seems to me that Step 1 would be
If Current Item:Priority ID NOT equals Current Item.Prior value (in other words, the ID has been changed)
Set variable “Old Value” = Current Item.Prior value
Update Current Item set Prior Value = 0
Update Item in current list where Prior Value = variable “old value”
Set current item.prior value = current item.priority ID
Does that make sense?
Aha! Your tip definitely helped. I now have the workflow working correctly if the Priority ID is shuffled either up or down by one. However, if priority moves more than one it is behaving erratically (ex. #4 moves to #2, it ultimately will be reshuffled back to #4 and the previous #2 & #3 both become #2). I also created another column named Priority ID – 1, which is just like it sounds, so I could increment to lower values. I thought based on the update it would continue running in a loop (workflow set to run automatically on change) but it seems to quit even when there are duplicate Priority IDs. I’m sure this will be very apparent to you, but it’s not looping how I’d expect (my thanks again!!):
Step 1 (If new Priority ID doesn’t conflict with the list, update Prior ID to match)
If Current Item:Priority ID not equals Current List:Priority
Update Prior Value to Priority ID
Step 2 (increment list up) – If Current Item:Priority ID is less than Current Item: Prior Value
Update Prior Value to -2 (dummy)
then Update item in Current List: Priority ID to Priority Child, where Prior Value = Current Item:Priority ID
then Update Current:Item Prior ID to Priority ID
Step 3 (increment list down) – If Current Item:Priority ID is greater than Current Item: Prior Value
Update Prior Value to -3 (dummy)
then Update item in Current List: Priority ID to Priority ID – 1, where Prior Value = Current Item:Priority ID
then Update Current Item:Prior ID to Priority ID
I think you may be overcomplicating it.
In my previous reply, one of the steps was “Update Item in current list where Prior Value = variable “old value””
But I didn’t specify what to update it to – it should change the Priority ID of the identified item to Priority ID +1
So it would work something like – you have added a 12th item in the list, and set its priority to 7. The WF identifies the item with PriorityID = 7, and changes it to 7+1, (aka 8). This sets off the WF on the former #7 (now #8), which looks for the existing number 8, and updates it to 9, 9 gets updated to 10, and so on. Works the other way, too… PriorityID 5 gets changed to 9, so it finds the existing number 9 and changes it to 10, 10 gets updated to 11, and so on.
(Done this simply, you will end up with gaps in the priorities, which you will have to handle, as well as what happens to the WF on the item with the last PriorityID in the list.)
Auto reset number to 1 each year!
Column name: FollowingNumber
Formula: =IF(YEAR(Modified)=YEAR(TODAY()),NextNumber+1,1)