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".
in the "Select Range" dropdown list which will enable you to select the particular rows and columns of excel spreadsheet you want to import in the custom list. Please don't forget to select the row that contains name of columns because these will become the field names in the custom list. 
, which will open the full view of "Import to Windows SharePoint Services List" dialogbox with the updated "Select Range" as you have selected in the excel spreadsheet. 
