Auto-increment Number in SharePoint 2010

For weeks, I have been working on a SharePoint project, a training courses management site. I was brand new to Mircosoft SharePoint, and I did run into tons of problems. I'd like to take down some tips for general problems I have encountered as references for those who have same problems, but please notice that these solutions may not be the best practices.

poweredbysp2010.png

Environment: SharePoint Server 2010 Enterprise

In many cases, it is necessary to have a unique ID number for each item in a list, especially when your workflow need to lookup for unique value or track an item passed between differet lists. SharePoint have an built-in GUID (Global Unique ID) mechanism to ensure that not only every item but also every piece (i.e. lists, libraries, views, and sites...) in the system can be identified as unique. However, the problem is that GUID is so long that makes it less human-readable. And in some conditions it is hard to get GUID. In a SharePoint list, there is also a built-in [ID] column with a format like 1, 2, 3..., which can be confusing when used between different lists. What I need are serial number consist of meaningful prefix, year, and ID number, i.e. OTU2015-000001, OTU2015-000002, OTU2015-000003, etc.

There are different approaches to do this, while utilizing a calculated column and a workflow is the simplest, as far as I know. At my first try, I added a calculated column, which I called it "myUID", to the list with formula:

="OTU"&YEAR(Created)&"-"&LEFT("000000",6-(LEN(TEXT([ID],"0"))))&TEXT([ID],"0")

It seemed work well, but when I edited an existing item, its "myUID" reset to OTU2015-000000 automatically. I didn't find any useful clue to solve this problem, but I managed to make it work well with help of an auxiliary column and a workflow.

Step 1, create a "Single Line of Text" column and name it "AID.

Step 2, create a workflow for this list, which have only one action "Set [AID] to [ID]". Then configure it to start on item creation in the list. This workflow pass the ID to AID and save it as TEXT so that it won't change anyway.

Step 3, create the "myUID" column, modify the formula as

="OTU"&YEAR(Created)&"-"&LEFT("000000",6-(LEN(TEXT([AID],"0"))))&TEXT([AID],"0")

Save your work and add a new item to try. Now you get your auto-increment number column works. Enjoy it!

*Copyright to SharePoint logo and image belong to Microsoft.



知识共享许可协议
除非注明,本博客文章均为原创
并采用知识共享署名-非商业性使用 4.0 国际许可协议进行许可。转载请以URL链接形式标注源地址。

标签: sharepoint

添加新评论