I recently came across a requirement where in customer wanted to auto generate a custom sequential number with current date as a part of this sequential number on a SharePoint list.
Although SharePoint has an inbuilt “ID” field and Issue Tracker list can Issue ID. But they simply just provide running numbers like 1, 2, 3, 4.What if you need some prefix and also a choice from where the sequential numbers should start.
You can achieve this through different approaches, like using an event handler etc., but I think the following is the simplest of all.
Here is the step by step instructions on how to get this done.
There are two parts for this solution.
Part 1: Getting the desired sequential number
1. First of all we need to create a list which will help us in generating the numbers, let us call this list as “Unique Numbers”
2. Add one number field, "LastNumber" and one calculated field "NextNumber" to this list. The formula to be used for NextNumber is "[LastNumber]+1"(Note: The data type returned from this formula should be number)
3. Let us add a new Item to this list with the following information
Title : ORDERNO
LastNumber : 99
4. Now we need to create the second list I will call it “Orders”
5. Add a number field “OrderNo” to this new list, now we are ready to build the workflow to generate unique sequential numbers
6. Open your web site in SharePoint Designer (SPD) .Create a new workflow. In the File menu, select New->Workflow...
7. In the dialog that appears, give the workflow a name, e.g. "Assign Order Number". In the drop-down list below, choose to connect the workflow to the list you want to assign auto sequential numbers to (In this case, this would be a list called Orders). Then check the middle option of the three check boxes below, "Start this workflow automatically when a new item is created".
8. On the same dialog, there is a button at the bottom called "Variables". Click it. Choose "Add", name it "mNumber" and make it of type "Number". Click OK, and OK again. Now, click "Next" in the dialog.
9. You can leave the step name as is ("Step 1"). Leave the "Condition" empty, and click the "Actions" button. In the menu that appears, select "Set workflow variable". This will add an action to the list to the right of the Actions button. Now you need to set up this action. Click the link "Workflow variable", this will open a small dropdown list. Select "Variable: mNumber". Then click the next link, "value". This will display a textbox and a small button in place of the link. Click the button ("Show databindings"). This will bring up a new dialog, where you will select the value to assign to the "mNumber" variable. In the "Data Source" drop down, select "Unique Numbers". In the drop down just below ("Field:") select "NextNumber". Now, the dialog will be expanded with a "Find list object" section. In the drop down "Field", select "Unique Numbers:Title", and in the value box below, type ORDERNO. Click OK. SPD will now give a warning that the look up might return more than one value, just select "Yes" to continue here.
10. Now you add one more action. Click the "Actions" button and select "Set field in current object". Click the "field" link in the new action, and in the list that appears, select the field in your list that is the target of the autonumber (e.g. "OrderNo"). Then click the "value" link, and once again click the little button that appears ("Show databindings"). This time, in the "Data Source" drop down, select "Workflow data", and then in the next drop down, select the variable "mNumber". Click OK.
11. Add yet another action, this time choose the "Update list item" action. Click the link in the new action, "update list object in this list". This brings up another dialog, in the "List" drop down, select "Unique Numbers". Click the "Add" button just below to the right, and in the new dialog's first drop down ("Set this field:"), select "LastNumber" and to the right of the "To this value:" text box, click the formula button. Again, a new dialog appears, in the "Data Source" dropdown, select "Workflow data" and then in the "Field" drop down, select "Variable: mNumber". Click OK twice to get back to the "Update list item" dialog. Now, once again in the "Find list object" section, select "Unique Numbers:Title", and type in the value ORDERNO. SPD will give a warning again, click "Yes".
12. That's it. Click the "Finish" button, and if all is well, the workflow will be created.
13. Now when you create a new item in the Customers list you will see that a unique sequential “OrderNo” is generated
Now we have achieved our first goal of generating a desired sequential number. Now on to Part 2
Part 2: Adding a prefix to the sequential number, in this case current date in YYYYMMDD format
1. Create a field “Today’s Date” this should be of “Date and Time” type and default value should be today’s date.
2. Create another field “OrderID” of calculated type and use this formula
=TEXT(Today’s Date,"yyyymmdd")&OrderNo
Now when you create a new item on Orders list you will see that a new OrderID is generated .
Note: The field names mentioned here for example purpose, you can use any name with the same logic to make it work.
