SharePoint: How to Create an Auto-Incrementing Number Field For Use in a Custom ID – Part 5
Dessie Lunsford
Points-of-Sharing
In the previous article we finished the build of our "Auto ID" workflow and deployed it to our "Project Library".
In this article we’ll finish off the rest of the customizations needed on our library, then test the functionality we’ve built to make sure everything works as expected.
Getting Started
To start, the first thing we need to do is modify the column ordering to make them appear in a more logical fashion on our "Edit" form page.
In the "Projects Library" go to its "Settings" page and under the "Columns" section, click the "Column Ordering" link.
Set the order of the fields as:
Title = 1
Name = 2
New Project = 3
Existing Project = 4
Project Code = 5
Document Type = 6
Auto Number = 7
Version Code = 8
Project ID = 9
Run Main Workflow = 10
Next, we need to modify the default view of the library so it only shows the fields we’ll need and to configure the “Grouping” needed to allow for our custom versioning.
Before we do this however, we need to create one more column that’s sole purpose is for the grouping view (this will let us group by project).
Create a new column called “Project ID View” and make it a “Calculated” type with the following formula:
=[Project Code]&"."&[Document Type]&[Auto Number]
Since our final “Project ID” value will contain the “Version Code” along with the rest of the ID values, we can’t group on it by itself (each version would be a completely new value, so the grouping wouldn’t work), so by having a value that doesn’t include the version, we can safely use this column in our grouping to associate each related document.
To apply the grouping, and set which fields we actually want to view, go to the default view and choose “Modify this view” (or just click the view name under “Views” if you’re still in the library settings page), and set it as follows:
Uncheck all but those listed below and in the following order:
Type (icon linked to document) = 1
Name (linked to document with edit menu) = 2
Project ID = 3
ID = 4 (this is only going to be used for initial debugging and can be removed later)
Next, while still in the edit view page (or go back to it if you’re not still there), scroll down and expand the “Group By” section and select “Project ID View” in the “First group by the column” dropdown then click “OK” to save the modified view.
The final step in the customization is to modify the "Edit" forms page with a CEWP and a small JavaScript that will clean up the values listed on the "Existing Project" dropdown list.
Since this field is a "Lookup" to the "Auto Number" field, each document will have a value that will be listed on it. The problem we run into is that when adding in new version documents, they will have an "Auto Number" that is already in existence in the library, so we’ll wind up with duplicate values listed in the dropdown. Functionality-wise, this isn’t important because when adding a new version we could choose any of the values listed and things would still work fine, but if you wind up having many versions added to existing projects, the dropdown list will eventually become a bear to scroll through (duplicate after duplicate) just to get to the value you want. The script we’ll be adding will simply parse through the values displayed in the list and remove any duplicates making it cleaner to navigate.
Since this is a system page, we don’t have the "Edit" option in the browser (yet), so we need to open it in SharePoint Designer and add in the html needed, then the CEWP with the script.
Open SharePoint Designer, connect to your site and expand the "Projects Library" folder in the "Folder List" then expand the "Forms" folder.
Double-click the "EditForm.aspx" file then in the html (code) view, scroll down to line #55, put your cursor after the "</ZoneTemplate></WebPartPages:WebPartZone>" line and press "Enter" to add in a new line then paste in the following:
<WebPartPages:WebPartZone runat="server" ID="Bottom" Title="loc:Bottom"><ZoneTemplate></ZoneTemplate></WebPartPages:WebPartZone>
This will add in a new web part zone below the list view where we can add in our CEWP.
Click "Save" and switch to the "Design" view to see the new zone added to the bottom of the page (click "Yes" on the "Site Definition Page Warning" message that pops up).
In the new "Bottom" zone, click on the "Click to insert a Web Part" link then in the "Web Parts" list that opens (by default in the upper right-hand corner of SPD), click-and-drag a "Content Editor Web Part" into the zone.
Next, in the code view, go to line 86 and look for the "<![CDATA[]]>" section.
Replace the section with the following:
Old code:
<![CDATA[]]>
New code:
<![CDATA[<script type="text/javascript">
var opt = document.getElementsByTagName('select');
for(var i = 0; i < opt.length; i++)
{
if(opt[i].title == 'Existing Project')
{
for (var j = 1; j < opt[i].options.length; j++)
{
if ((opt[i].options[j + 1] != null) && (opt[i].options[j + 1].text == opt[i].options[j].text))
{
opt[i].options[j] = null;
j -= 1;
}
}
}
}
</script>]]>
Once you have the script added, the last piece is to hide the CEWP from displaying its title (since it just has script in it, the only part of the web part that will display is its title) by setting its "Chrome" to "None" (alternatively, you could set its visibility to "Hidden" – either will work fine).
In SPD, switch to the "Design" view, right-click on the "Content Editor Web Part" and in the menu that opens, select "Web Part Properties".
Expand the "Appearance" section and in the "Chrome Type" dropdown, select "None" then click "OK" to close the properties page.
Now that we’ve added in our script, we won’t see any duplicate items on the "Existing Projects" dropdown list.
Save the page.
Before we leave SPD and move on to the testing phase, you may be asking why didn’t we just do this in the browser? Well, the answer is simply because until we’ve added something to the library, we don’t have access to the "Edit" page. We could have waited to do this last bit of customization until after we added in our first document, but since we were already in SPD, it’s simple enough to go ahead and add in the script here.
To test things, close SPD and return to our site in the browser.
If you’ve been following along throughout this entire series so far, both the "Auto ID Master" list and the "Projects Library" should be empty (if they’re not – because you decide to jump ahead a bit, you’ll have to add/delete items in both places until you get to the point where both the list and library are empty, and the next item you could create in the "Auto ID Master" list will be one "System ID" number higher than that of the last item deleted in the "Projects Library". If you have difficulty getting things sorted out on this, post a comment and I’ll help you get them back in sync).
In the "Auto ID Master" list, create a new item with "Next Available Number" for its "Title" and "100" for its "Next Number Available" value.
Next, go to the "Projects Library" and upload a document.
For these tests, I’ll be using a series of test documents for new projects and new versions named appropriate to their version ("A" indicates new project, "B" or "C" indicates new version of existing project).
Remember, we do have two separate workflows attached to this library and as soon as we upload a document the first workflow will start – even before we exit the "Edit" form page, the workflow is already running because the document will already be added to the library.
Once you’ve uploaded a new document, you’ll be taken to the "Edit" page where you can specify its details.
In the "Title" field add in an appropriate value.
The "Name" field should already be populated, but you can change it if needed.
Select the value from the "New Project" dropdown (this should be "100").
In my example, I’ll leave the "Project Code" and "Document Type" fields as is.
Ignore the "Auto Number" field – this will always be populated by the workflow.
Since this is a new project, we’ll also leave the "Version Code" as the default "A" value.
Finally, select "Yes" in the "Run Main Workflow" dropdown in order to have our "Auto ID" workflow start.
Click "OK" to save and exit the "Edit" page.
After each workflow has completed, the "Project ID" is built with each piece (“Project Code”, “Document Type”, “Auto Number”, and “Version Code”) and is added to a group based on the "Project ID" minus the version letter.
Add in a second "New Project" and you’ll notice that the value in the "New Project" dropdown is now one number higher than it was last time.
Depending on how long it takes the workflows to complete, you may on occasion see the temporary state of "In Progress" listed for the "Auto ID" workflow which results in the "Project ID" having a value of "xxxx" listed in its "Auto Number" section. If this happens, just refresh the page and you’ll see things completed.
To test the "Version" capabilities, upload another document but instead of choosing a value from the "New Project" dropdown, select a value from the "Existing Project" dropdown.
Adding in more documents, you’ll see that as each new document is added, if it’s a "New Project" it will get its own group, and if it’s a new version of an "Existing Document", it’ll get added to an existing group.
As a final note, depending on how you want to organize the documents in the projects, you may need to modify the grouping to be based on just the "Project Code" if you wind up having multiple "Document Types" within the same project. In that case, project codes in the form of "02.01.1255.A", "02.01.2255.A", "02.01.3255.B" would all fall under the group "02.01", so modify the list and views as needed to handle your type of scenario (you can also create new calculated columns for use in other views as well).
Summary
What we’ve accomplished throughout this series is a way in which to add in extra functionality that didn’t exist in a default site.
Since the default "System ID" value is not immediately available after creating a new item, it can be difficult to base an auto-numbering system on it alone. To get around this limit, we created our own custom ID scheme that is built on several different pieces of information that we define after a document has been uploaded to the library.
The workflows we built also allowed us to get past a specific drawback on document libraries that makes workflow initiation on item creation problematic since the only time we get a chance to edit the document properties and give it the required bits of information is after its already been uploaded.
The solution in this case, was to build an initialization workflow that on item creation would wait for a specific value, then perform an update. With this as the trigger to start a second workflow that performed the bulk of our actions to auto-increment our custom number and finally provide the necessary details to allow our calculated columns to build out our final "Project ID", we accomplished all steps necessary to meet our initial requirements of our custom "Project ID" and versioning.
Till next time…
– 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
Huge thanks to Dessie for working throug this with me! It was tremendously educational. I plan to start testing this solution later this week. This example will also provide proof to leadership that we should take SharePoint seriously, particularly as an option for automating business processes – quite a task when IT is even surprised that I would ask for access to site columns. Thanks again for the fabulous learning opportunity!
All the best,
Emily
Emily,
This is the part of working with SharePoint that I really enjoy the most…working on solutions like this one.
It was a fun one…and I’m glad I was able to help :)
- Dessie
Fantastic series! I’ve been convinced Sharepoint out of the box can do 90% of what I need to automate dozens of business processes, but every time I talk to one of our developers, they say I need a custom workflow built in Visual Studio. Posh I say! If I have to wait for one of our developers to have time to build me a workflow (and assuming I can find one who will actually listen to my requirements before running off and building a workflow), I’ll be old and gray before I get anything done.
Although its late at night and I certainly won’t remember everything I did come tomorrow morning, I followed the steps you set out and got the Auto ID working like a charm.
I’ll be able to digest this over the next few days to complete 2 or 3 current projects without a VS custom workflow, I’m sure. Three cheers for Dessie!!
Keith,
I’d be interested in hearing about what types of projects you’d be looking at to apply this towards. Even though the solution I covered was designed more specifically towards one scenario, I’m always curious about what other applications users come up with based on the individual concepts and ideas covered.
Btw, how did the series read? Was it easy to follow along with? I’m always looking to improve on how I present material, so I wanted to make sure that on longer series’ like this one that I break each piece at a good place, and that the entire series itself is easy to follow.
- Dessie
Your instructions were clear, and the screenshots were very helpful in guiding me through how to use SPD. I thought the length of each piece in the series was good. I actually worked through all the pieces in the series at one sitting, but having it broken in to pieces made each piece manageable, as I found myself frequently scrolling back to double check the instructions.
I’m new to Sharepoint, and I’ve been somewhat frustrated trying to figure out how to accomplish in Sharepoint what I already know how to do in MS Access using the command button wizard and a little tweaking of the code behind forms. None of the resources I’ve checked out, including both books and websites, seems to address HOW to use Sharepoint to solve real-life problems without spending countless hours learning Sharepoint from the ground up. Your series did exactly that , and in doing so it also 1) taught me how to use Sharepoint Designer to create a custom workflow, 2) exposed me to the capabilities of SPD workflows, and 3) gave me several ideas of how to work around some of the limitations of Sharepoint 2007.
For instance, I’m currently working on a Sharepoint site to manage our use of Microsoft SA training vouchers. In the past, management has indicated that they want to divide the use of the vouchers equally between the 3 main groups in our IT shop, but has not created a mechanism for doing so. I am building a prototype in sharepoint that will allow the CIO to tell me how many vouchers each VP group gets to use, then lets managers enter a training request that will be routed automatically to the appropriate VP for their approval, IF the VP has enough vouchers left in his/her bank.
I’ve built a workflow in SPD designed to fire when a new training request is created, which uses a condition to test if the VP’s remaining voucher bank equals or exceeds the number of requested vouchers, and if so, sends an email to the VP requesting approval. It also updates the status field in the training request to show the request has been sent to the VP.
However, I now need to learn how to troubleshoot a workflow. The new items I have created in the Training Request list show that the workflow completed, but the status field did NOT change as designed, and I can’t find where the workflow logged its successful completion.
Any hints?
Also, I want the sharepoint site to send an email to the student, after the training course has been taken, asking them to log in to the site and evaluate the course, so we can scorecard the providers. Do you have any hints for working around the fact that Sharepoint 2007 does not have an automatic way to fire workflows based on a date?
Keith,
First, thanks for the comments…your second paragraph really sums up the entire point of why I write articles, so I appreciate the kind words.
Next, for your workflow that giving you problems, have you tried adding to the log after each step to see where its having problems? In SPD choose the “Log to History List” option under “Actions” and add in a string message that lets you know what action is being logged (i.e. “Step 1: training Req successfully created”, “Step 2: Voucher test successful”, “Step 3: Email Sent”…etc.).
Once the workflow had ran, you can click on the “Completed” link (added to the view after the first run of the workflow) to see its history…you should then see all your log entries listed there.
For the status field not updating, the above debugging should help you see whats being processed in the workflow and see whats being skipped. One simple test you could try is reordering some of the actions to see if that fixes it (depending on the actions, they may need to be processed in a certain order to work correctly). Maybe move the update of the status field up a level or two and see if that takes care of it.
For the last part of sending a follow up email to the students, you can use a “Pause Until Date” action to have the workflow wait until a specified date before sending the email.
See this series I wrote awhile back on sending an email on a specific date: http://www.endusersharepoint.com/2010/01/11/how-to-get-an-spd-workflow-to-run-at-a-specific-time-employee-vacation-reminder-part-1/
Also, have you looked at the “Employee Training Scheduling and Materials” template”? It already has workflows built-in for follow up emails to students after training completion…you may want to take a look at the workflows in that template to see how they created the follow up email reminder. A caution though, the OOTB template for it is broken so if you want to create a site off it for use, let me know and I’ll send you a series of links from both myself and Eric Alexander on how to fix it (I posted several articles on how to fix what was broken then he extended it to make the site more functional).
- Dessie
I am in a need for something like this, however. I wonder if there is any other way much simpler and still OOB? What about SPS 2010, does it bring anything to the table?
Thanks
Moe
Moe,
Its possible that there may be an easier approach in SP2010, not sure though as I havent had a chance to explore different options in it yet.
I’ve been experimenting with the use of an external content type to manage the auto-incrementing ID field, but havent had much success yet…I’ll post something about it if I can get the idea to work.
- Dessie
A great series. I am in the process of testing it out now. I do have a question about adding the web part zone. Is this really necessary? Manually editing the page unghosts it, right? I tried something different. I added the little trick to get the page into edit mode.
From my allitems view (Project Library), in the URL, I replaced:
AllItems.aspx with EditForm.aspx?toolpaneview=2
this opened the page in edit mode and allowed the addition of a CEWP. I don’t see any reason why this would not work in this case.
Great job, going to test, will post my results.
I have had a successful test.
In the part where you discussed the IDs I was a bit cloudy, but I think that was me. Although I did not jump ahead, I did rush at the end and did not completely follow your directions, so I wound up having to add/delete items. Rereading what you wrote I understand what you meant. ID’s need to match, thats what we were setting up in the workflows.
Anyway great job with your directions.
Larry,
You are correct in using the modified URL to access the edit page, and I should have mentioned that as an alternative approach.
The reason I did it through SPD directly was just to show that you can do it there…and in my case, I added a new web part zone to hold the CEWP, and that had to be through SPD.
Thinking back, I proabably just should have used the modified URL to make it easier, but either approach works.
Good catch!
- Dessie
This has been a huge help.
I just finished testing and am hoping you can help me.
An Auto ID has not been established and all I get is the state of “Failed on Start (retrying)” listed for the “Auto ID” workflow and the “Project ID” has the value of “xxxx” listed in its “Auto Number” section. The whole purpose of this exercise was to get these Auto IDs working…
I have refreshed multiple times and waited for longer than an hour and the Auto ID still states “Failed on Start (retrying).” Do you have any idea of where I went wrong?
I am back tracing my steps, but have not seen anything different from what you have.
Your help would be greatly appreciated.
Helena
Helena,
At first glance, it sounds to me like the ID’s are out of sync between the projects library and the auto ID master list. Can you check to see if this is the case?
First thing is to remove the workflow from the library. Do this by going to the library settings, then click the “Workflow Settings” link, then click the “Remove a workflow link”. Click the radio button under the remove column for any workflows listed then click “OK” to remove them.
Next go to the “Auto ID Master” list and add the “ID” column to the default view (if it wasn’t already).
Go back to the “Project Library” and do the same (add the “ID” column to the default view).
Compare the ID’s. If they are different, create then delete as many as it takes until the ID in the “Auto ID Master” list is one higher than the one in the “Projects Library”. Once they are, you can go back to SPD and re-attach the workflow to the library and test it again. On the next item you add to the library, it’s ID should now match the ID in the Master list. One the workflow has completed, the ID of the item in the Master list should once again be one higher than that of the last item added to the Projects library.
Let me know if this fixes it.
- Dessie
Hello Dessie,
Your instructions fixed the “Failed on Start (retrying)” problem! I get the “Completed” status now as you are supposed to. You were right on the money about the ID’s being out of sync.
The only other issue I have is that the Auto Number does not display as part of Project ID or Project ID View. I have written those calculations over and over again and everything shows up except for the [Auto Number].
Do you have any idea to why that is happening?
Thank you very much for taking time to help, you had great directions.
Helena
Helena,
When you add the “Auto Number” column to the view, does it have a value in it?
- Dessie
No, there is no number in the Auto Number column.
Helena
Ok, that would mean that in the workflow where you update the current item, no value is being assigned to the column.
Open up the workflow in SPD and verify that in the 5th action in the top section (should appear as “then Update item in Projects Library”) that you are setting the “Auto Number” field to the workflow variable “CurrentNumber”.
This value should be set first thing in the actions for the upper portion of the workflow.
If it is being set and you’re assigning the column to the value, add in a “Log to history list” action immediately after the first action that sets the “CurrentNumber” variable to the value of “Auto Id Master: Next Number Available plus 0″. In the “log” action, click the “this message” link then the “fx” button and choose “workflow Data” in the “Source” dropdown and “Variable: CurrentNumber” in the “Field” dropdown.
After clicking finish and attaching the new version of the workflow to the library, add in a new document and wait for the workflow to complete. Once completed, click on the “Completed” link in the view to see the details of the workflow…you should see something in the log entry for the variable value.
Do you see a number in the value? If not, then the variable is not being set first thing in the actions.
- Dessie
I did as you said and I still don’t see the number in the value.
I wish I could post a screen shot in this comment box for you to see that what I have is exactly what you have. (plus the “Log to history list” action).
I wonder if this has somethign to do with me only having MS Online Services.
I will keep trying and tweaking in order to get the Auto Number to work properly.
Helena
Helena,
So it sounds like there is a value in the log entry?
You can post screenshots to flickr or some other image hosting site then put a link here.
MS online service shouldn’t have anything to do with this since its purely a SharePoint / SPD thing.
See if you can get some screens posted online somewhere and put a link to them here…we’ll get this figured out :)
- Dessie
Dessie,
Please see the pictures that I added to flickr http://www.flickr.com/photos/51471449@N08/
Let me know if you need additional screen shots.
THANK YOU SO MUCH!
Helena
Helena,
So far what I see in the screenshots does look correct, but since you named the “Projects Library” as “Initial Upload” and it appears that you’re using numbers for the versions instead of alpha characters, I’d almost have to see a complete breakdown of everything (all workflow steps broken out, column details in the list and library as well as all formulas used) to be able to see what may be wrong (there may be other subtle changes I cant see directly from the screenshots).
In the walkthrough I detailed, I’ve personally followed through its steps over 20 times in testing, and used the names and details I’ve specified literally, and have had zero issues with it not working on completion.
What you could try, is to go back to the beginning and run through it using each step exactly as I have it detailed (including the naming and column details listed). I realize its a bit of an effort to do this, but one thing I’ve learned by following countless tutorials online throughout the years, is that the first time you run through it…do it ‘Exactly’ as detailed so you have the same end result. Once you get it working, then you can go back and start to stray a bit to personalize it.
Again, because you’ve obviously made some changes to what I had detailed, its hard for me to really be able to find out what the issue might be without seeing each and every step yours takes in detail, along with the schemas of your list and library.
Try running through it again following the steps exactly, then post back with your result. We’ll get it figured out, it just may take a bit longer.
- Dessie
I started over as you suggested and it works perfectly.
Thanks again for all of your assistance.
Helena
Helena,
I’m glad to hear you got it working :)
- Dessie
Dear Dessie,
i have the same problem with Helena, but I follow exactly what u suggest…i did it twice and the same problem occur, that is the “auto number” is not functional. When i create new item, the “project id” will display something like this
“01.01.1automatically filled in.A”
i hope u can help me on this.
azri,
Start by clearing out all items in both the master list and projects library, and remove the workflow from the list in its settings page.
After this, create one item in both the master list and the library and compare their ID’s. What you’re looking for is for both of them to match. You may have to create/delete multiple times until you get them to match, but when they finally do, delete all items again.
Next, create an item in the master list-ONLY, then stop and go back to SPD.
In SPD, verify that all steps are complete (visually compare with all steps listed throughout this article series), the re-deploy the workflow back to the library.
Next, time to test again. Go back to the library and upload a new file (follow the steps listed in the walkthrough to make sure you’re setting field values as appropriate), then see what happens.
The ID should update with the value from the master list, and the master list should increment to the next value.
If it still doesn’t work, then something may be incorrect in the workflow itself. My suggestion would be similar to Helena’s situation in that I’d ask you to upload screenshots (to flickr or similar images hosting site) of you’re master list (default view and setings page with columns), the library (views and settings page with columns), and workflow. With these, I may be able to help.
Let me know how things progress.
- Dessie
btw, you did great for this tutorial….it is very easy to follow and it help me a lot in understanding sharepoint…thanx a lot.
Hi,
This is great and wery helpful!!!
I have one questiion?
What shoul I change so Automatic ID for new document will be something as DN09-00-147 (next one is DN09-00-148….) for next year it will be DN11-00-001 where 09 (or 10, 11) are years when document is created.
THANKS a lot!!!!!
Malik,
The quick and easy way is to just add in the parts needed to the formula in the “Project ID” calculated column. Something like this:
="DN"&RIGHT(YEAR(Created),2)&"-"&[Project Code]&"."&[Document Type]&[Auto Number]This will take the date the record was created, grab the year, then return only the right two digits (”10″ instead of “2010″). Using the “&” symbol for concatenation, you can then put all the pieces together (put the “DN” first, then the date, then the rest).
This would produce auto numbers appearing something like:
DN10-01.01.1100
DN10-03.01.2102
For the rest of the custom numbering, refer back to the steps where I cover what pieces make up the final project ID number and make any changes needed to use your numbering scheme instead of the one I use in the walkthrough (you’d only need a few minor changes, so the steps should cover everything).
Hope this help,
- Dessie