SharePoint: How to Create an Auto-Incrementing Number Field For Use in a Custom ID – Part 1
Dessie Lunsford
Points-of-Sharing
Emily from the STP forums posed an interesting problem regarding having an auto-numbering system that could meet the needs of her organization. In this series, we’re going to look at an approach of how to build out an auto-incrementing number field (not the system-generated item ID field) and apply it to a custom numbering scheme.
Requirements
For this scenario, we need the following:
A number field that automatically increments after each new item. It will be used in a lookup to allow a user to upload a series of documents in an associated library and only have the most recent number available in which to choose from. The idea is that as a number is associated with an item, we want to remove it from the source list (so it doesn’t show up in the lookup dropdown of choices), then make the next number available.
A custom ID that needs to be made up of several different pieces of information based on its project code, a document type, the auto-number, and a version code, with its final format being as “xx.xx.yzzz.A” (“xx.xx” = project code, “y” = document type, “zzz” = auto-number, “A” = version).
Custom versioning that will allow us to associate versions of the same project without using the built-in versioning abilities of a document library (i.e. multiple versions of the same project document living together with a “Version Letter” change being the difference in the overall Project ID).
The Approach
With the above in mind, we’re going to tackle this with two separate strategies that will each contribute to the overall end-solution:
How to create an auto-incrementing number?
How to build our Project ID with associated versioning ability?
To handle the auto number, we’re going to create a list that houses the number, and then build out a workflow to increment it when needed.
Building the Master List
The first list we’ll need to create is our “Auto ID Master” list which will contain the number we’ll be referencing in our lookup.
Create a new custom list with the name “Auto ID Master”.
Create a new column called “Next Number Available”, make it a “Number” type then add it to the list.
Create another new column called “Next Number Calc”, make it a “Calculated” type and add in the following formula:
=[Next Number Available]
Make sure its return type is set for “Single Line of Text” (this is required so our lookup will find it), then add it to the list.
Next, we’re going to create our actual “Projects Library” so we can start to see how to use our “Next Number Available” column and how to automatically increment its value. Before we do this though, make sure that after you’ve created the “Auto ID Master” list, that you DO NOT CREATE ANY ITEMS ON THE LIST YET!!!!!
I’ll explain why on this later, but for now leave the list alone (and in fact, wait until the end of the series where we test things) and move on to creating our library.
Building the Project Library
Create a new document library called “Projects Library”.
In order to be able to see the auto number and use it when we upload a new document, we need to create a column that is a “Lookup” to the master list. So, create a new column called “New Project”, make it a “Lookup” type with the following:
In the “Get information from” dropdown, choose “Auto ID Master”.
In the “In the column” dropdown, choose “Next Number Calc” (note how we only see the calculated column from the master list and not the number column – lookups only see fields that have a return type of text, which is why we created the calculated column so we could expose the “Next Number Available” column data to lookups).
Part of how our workflow is going to work and increment our auto number involves being able to delete the item on the master list then recreate it with the next number. Because of this, once we delete the item on the list, our lookup ‘value’ will disappear because the “ID” of its source will be no longer in existence. To get around this, and also have the benefit of being able to later look at this value when uploading new versions of the projects documents, we need to create another field that is updated from the workflow only, and can then be used in further processing of our final “Project ID” value and associated versions.
Create another new column in the library called “Auto Number” with all defaults (“Single line of text” and so on). Before clicking “OK” to save and add the column, in its “Default value” section, type in the text “(Automatically filled in)”. This will give us a visual clue that we haven’t started the workflow yet.
With this column added, we now have enough information to move on to SharePoint Designer and build out the workflow to automatically increment the “Next Number Available” field, but since that is only part of the end result, we’ll need to add in several more columns to our library first, before building the actual workflow.
We next need to create a new column to hold our project codes (another requirement of our ending "Project ID" value).
Create a new column called “Project Code” and make it a “Choice” type with the following choices (these are just samples of what you could use):
01.01
02.01
03.01
04.01
05.01
Make the display type “Drop-Down Menu” and the default Value “01.01”.
Next, we need to specify what type each new document is (i.e. “Parts”, “Tools”, and “Reports”), so we’ll create a column where we can choose from a list of choices.
Create another new column called “Document Type” and make it a “Choice” type with the following choices:
1
2
3
Make the display type as “Drop-Down Menu” and set its default value to “1”.
Also, to make it more descriptive as to its purpose, add in the following description:
Parts = 1
Tools = 2
Reports = 3
Next, create another new column called “Version Code” and make it a “Choice” type with the following choices:
A
B
C
Also, to make it more descriptive, add in the following description:
A = Design Phase
B = Testing Phase
C = Final Protocol
Make the display type as “Drop-Down Menu” and set its default value to “A”.
Next, since we’ll need a way in which to create new versions of project documents and associate them with existing projects, we need to create a ‘Lookup’ to the existing “Auto Number” field in the document library.
Create another new column called “Existing Project” and make it a “Lookup” type with the following:
Set the “Get information from” drop-down to “Projects Library”.
Set the “In this column” drop-down to “Auto Number”.
The reason we’ll use this column is because it is unique to related documents and will allow us to specify which project to associate new versions with.
Now that we have all our fields, we need to build the final “Project ID” value which is nothing more than a concatenation of the values we’ve defined.
Create another new column called “Project ID” and make it a “Calculated” type with the following formula:
=[Project Code]&"."&[Document Type]&[Auto Number]&"."&[Version Code]
What this formula accomplishes is to format our final “Project ID” in the initial requirement of
“xx.xx.yzzz.A” (where “xx.xx” = project code, “y” = document type, “zzz” = auto-number, and “A” = version).
By using a “Concatenation” technique of combining each column value with an ampersand (&) and a period (.) in between each section of the ID, we can build out the number as needed.
Summary
So far, we’ve built the master list ("Auto ID Master") that will contain our auto-incrementing number, and a library ("Projects Library") in which we’ll be able to upload our project files.
In the next article we’ll begin building out our workflow that will perform the auto-incrementing functionality and aid us in dynamically building out our final "Project ID".
Till then…
– Dessie
Dessie Lunsford
Points-of-Sharing
- SharePoint: How to Create an Auto-Incrementing Number Field For Use in a Custom ID - Part 1
- SharePoint: How to Create an Auto-Incrementing Number Field For Use in a Custom ID - Part 2
- SharePoint: How to Create an Auto-Incrementing Number Field For Use in a Custom ID - Part 3
- SharePoint: How to Create an Auto-Incrementing Number Field For Use in a Custom ID - Part 4
- SharePoint: How to Create an Auto-Incrementing Number Field For Use in a Custom ID - Part 5
Greetings Dessie,
We are starting to use ShPt (WSS) for some of our projects. I have a site with several project entries on a custom list and want to try to use a project ID in it so it increments each time I add a new project entry… I started looking around and I came across your article today. What luck. The project ID would be XXXXXyyyynnnnn where XXXXX is project area (code), yyyy is start date calendar year, and nnnnn is sequential number. I think your method will work and I have followed your instructions so far. Looking forward to the next steps. Any idea when you might post it?
Thanks again for your ideas.
Regards,
Mark Rago
Mark,
I dont have the specific publish dates for the rest of the series, but the next one should be up next week.
- Dessie
NIcely done Dessie and very timely. I was able to auto-number a custom list in SPS2003 by creating a custom list and then adding a computed field to the schema.xml file. The computed field referenced the ID field and used a display pattern to display my autonumber.
I was going to see if something similar could be done in MOSS but probably won’t have to now!
Thanks for sharing!
Wait…so let me get this straight. You’re creating a new list, with a bunch of columns plus a workflow and who knows what else to build a fragile house of cards (have you ever heard of Rube Goldberg? ) that is only usable from one site instead of a simple custom field type that would be reusable AND bulletproof. There is definitely a place for no-code solutions in SharePoint, but this is not one of those situations. If I were in charge of this environment, I would rip this out and do it right-it’s not hard.
Dave,
> I would rip this out and do it right-it’s not hard
As a developer with the ability to deploy a solution to the server, your point is well taken. You can see from the majority of the responses here, however, it WILL be a useful solution for those that don’t have that alternative.
Mark
(Before anyone jumps on Dave, he’s a friend and his point is well taken. In situations that would allow development, his statement is correct, although stated with a bit of frustration.)
Mark,
Yes, frustration, not meant to piss anyone off. Consider this, though, just because the only tool you have is a hammer doesn’t make every fastener a nail. Sometimes the right answer is “you need custom code to do that.” Even if you “can” do something without code doesn’t mean that you “should” do it without code. As someone who has been called in to fix SP environments that have been damaged by some no code solutions, I can tell you that there is a reason why that is true.
However, I definitely agree that there is a place for no code solutions in SP-moreso than in most products. The right mantra is “the right tool for the job” and not having the ability to write or deploy code is not always a valid reason to rule out that tool.
We should talk to the Best Practices Conference folks and have a panel discussion on when to use code and when to look at no code. Or if you’re interested, maybe this is an article series for EUSP…
Jay,
You should like this one then :)
Stay tuned for the rest of the series.
- Dessie
Sounds good, Dessie. Thanks for the update.
mr
This was an issue that we were planning on addressing in the next few weeks. This article will definitely help move that portion of the project ahead much faster. Thanks for posting it!
Dave,
As a developer myself, I do understand your point and agree with you on the need to use the right “tool” for the right job. But as Mark mentioned, not everyone has access or the ability to deploy custom code…and yes, I know that it may be considered a “tired” excuse (how many times have we all heard that in SP’s lifetime), but in reality there are a great number of SP users out there that either dont have a Dev on staff that can create these “simple” solutions for them, ot they dont have the budget to bring in someone temporarily that can.
As somone who works at a state college, the need to look at solutions that are cost-effective is paramount to my job. And again yes, I am a developer, so realistically I could build out anything I wanted via code (and have, since I currently fill all roles in our SP deployment including server/farm admin and lead dev), and thats fine for me…but for the audience that doesn’t have the benefit of my working for them, I’d prefer to give them options.
In the “specific” scenario I’m working through in this series, the concept is to be able to provide a “code-free” solution to the problem at hand. Could it be worked out easier through code? Umm…of course, thats not in dispute. Hell, you could argue the same for many of the solutions my fellow authors and I have presented here throughout the past few years…but then you’d be missing the point of this site, and in that case we should be directing each and every user of SharePoint (Admins, Devs, End-Users et al) to sites like TechNet for each and every question that ever comes up and hope that the answer is not over-geeky (not bashing them…the moderators and volunteers that provide answers there are awesome and are a great resource).
Sorry, I just dont see the point of continuously stating the obvious when it comes to “Code or No-Code”. And again, I’m not disputing the fact that many things can be done rather simply (from a programmers point-of-view) through code. What I’m disputing is the requirement that just because its simpler through code that you automatically have to do it that way.
Tired of this excuse or not, the reality is that not everyone can deploy custom code…period.
- Dessie
I fully agree with Dessie that “not everyone can deploy custom code” or for that matter write custom code. I am not a developer or programmer but many a times am required to use SharePoint (our Intranet is on SharePoint) to deploy workable solutions “out-of-the-box” quickly. I have resorted to using SharePoint Designer a lot of times to “work around” some of the requirements required by the users.
I am also thankful to all who like Dessie, have shared their knowledge on such work-arounds (without coding) on the Internet. I have learned and benefited a lot from their posts.
Thank you Dessie and everyone!
Dessie, thanks for the great article. I tried to apply the solution to a custom list instead of a doc library, so newly added items in the list get automatically numbered 1.1 , 1.2, 1.3 and so on. But I didn’t succeed; can I accomplish this with only calculated column or do I need to follow all the steps?
Claudia,
This works just fine with a list as well, and can be trimmed down a bit since you wont have to wait for the item to be created first like you do with a doc library. You’ll still need the workflow, but it can be simplified some.
I’ll see about writing another article that covers the differences so you have a better guide for that scenario.
- Dessie
> maybe this is an article series for EUSP
I think it’s way past time to have this discussion, so if you’d like to write the initial article and get the conversation going, it would be appreciated.
> We should talk to the Best Practices Conference folks and have a panel discussion
Bill should be coming up for air any day now, since his book is due. I’m more than willing to chair that discussion. I think it’s needed and appropriate for his conference.