Auto-Increment Workflow Screenshots

My most viewed post to date (it will hit 8,000 page views Wednesday), Add a Unique Auto-Incrementing Column to a SharePoint List, has generated a number of questions, including regarding specific steps for setting up the workflow.

Below you’ll find the (long promised) screenshots for the steps to build the list for auto-numbering solution and the workflow that goes with it.

Some quick information before I dive into the screenshots, a few words about unique IDs and the auto-numbering solution(s) I have created.

First of all, SharePoint has a number of unique ID solutions built-in already.  Each list has a sequential ID number built into the list, the [ID] field.  This numbering starts at 1 and increments automatically.  Any item you delete leaves a gap in the numbering, that, as far as I know, isn’t filled back in later (once you delete #7, number seven is never again in that list).

Each item in SharePoint – lists, list items, workflows, sites, features, etc, everything has its own unique-in-the-world ID, the GUID.  This number is not sequential and isn’t really human-readable in any practical sense, because it isn’t meant to be.

In SharePoint 2010, a new feature was added that provides IDs unique inside each web application, though each web application has its own independent numbering.  This works fine if your documents and list items are not expected to cross web application boundaries.  And you can only have one numbering scheme per web application.

The task I was given was to create an auto numbering scheme for SharePoint 2007 that resets at the beginning of each year, had more than one numbering scheme (one scheme for each type of document that was being tracked), and the documents could live in one of several document libraries.  Usage was not expected to be heavy (about 100 documents per year, not thousands per day), and I was tasked to create this while I was a contractor, with no guarantee I would be there to support the application for the long term, which meant it had to be fairly simple to manage and modify by people with a basic technical proficiency with SharePoint and SharePoint Designer.

I’m currently working on a solution for several high-traffic libraries in SharePoint 2010, and will be going with a custom event handler instead of a workflow.  I’ll provide more information in a later post.

I strongly suggest reading the prior post first, including the comments below it, as it explains a lot of the thoughts behind what you see here as well as some alternative solutions and variations.

So here is how I solved the problem in 2007 with workflow:

We'll start with the Target Library, where documents (or list items if you are using a list) will receive the custom unique sequential ID numbers.

The only customization for this library relevent to this solution is the custom ID field, which since this Target library is (one of) the libraries where LNA documents are stored, this library uses "LNA ID" as its custom unique sequential ID field. This field could easily be part of a content type instead.

Next we have the list settings for the Unique Number list, the list where you store the current values for your custom unique numbers.

NextNumber is a simple numeric field that holds the next number in the sequence.

Following Number just calculates (and provides) the value of the next number in line. You'll see the workflow grab this number and use it to update NextNumber.

Here is where the magic happens in this list. As explained in my earlier post, this calculation creates a defined number with prefix for the type of item, the year the item was created, and the sequential number for that year, with a specified padding of zeroes.

The list item needs to be editable by all users (or at least those who can create new items in the target lists and libraries) or else the NextNumber will fail to increment, as workflows run as the user who created the item. This view obfuscates the ability to manually change the number from the casual user, but allows manual correction if you really need to (which we have needed to on occasion to make corrections).

 So now we get into the workflows themselves:

Initial screen for the workflow. Note the WF will run anytime a new item is created, but I left the ability to manually run the workflow if needed.

On the first step, set the condition under which the WF will run - Compare Target Library Field.

If the unique ID field on the target list (LNA ID)...

...is empty.

Then do stuff. Note that, in this version, I encapsulate the actions (represented by the unfinished Set Workflow Variable) inside "Check Out" and "Check In". If your library requires check in and check out, your workflow will need to do this!

The remainder of the screenshots for this sample WF do not include the "Check Out" "Check In" encapsulation, but if you need it, make sure it is the first and last things in the actions. Here I continue the Set the Workflow Variable shown empty in the previous screenshot. The Variable name is a suggestion, but it shows what is supposed to be in the variable, matching the list column name.

To fill the variable, click the fx (Display data binding) button.

Here is the data binding dialog. The source is the Unique Number list, the data comes from the NextUniqueID field. The WF will want to know which item in the list you want to get the data from. You can use the ID field, but you would have to know which ID corresponds to which kind of unique number you are trying to generate - LNA or XYZ (assuming you have more than one kind of unique number you are trying to generate from the same list). Even if you only have one list item, the WF still wants to know which item you want, because at design time it doesn't know how many list items will be present at run time. I use the Title field to designate which unique number I am generating, and thus which line item I want to access (in this case "LNA").

If you aren't using ID as your lookup value, you will see this "error" message, because SharePoint can't guarantee that the list item it finds is the one you wanted, because it can't guarantee the uniqueness of the data in the field. As long as users cannot add new items to the Unique Number List, this should not be a problem. Click yes.

Now you want to add the variable to hold the FollwingNumber (Despite its source, I called this variable NextNumber because it will be used to set the new NextNumber, as you will see.).

Set the variable to the value from FollowingNumber. Remember everything I wrote about choosing the list item to get the variable data for NextUniqueID? Same thing here.

Now we are going to set the value of a field in the current item (the new item in the Target Library that caused the start of the WF). Can you guess which field we're going to set?

Right, LNA ID, the custom unique sequential ID field on the Target Library.

Clicking the fx button, we set the source as a workflow variable, and pick the correct variable. Since we are setting the Unique ID that we just picked up from the Unique Number List, we choose NextUniqueID (or whatever you named it).

Next we are going to add another action to the step, Update a List Item, but this time, instead of current item, pick the Unique Number List as the target. Click Add to get the Value Assignment dialog, set the NextNumber Filed as the target, click the fx button to get the Define Workflow Lookup dialog, set the source as Workflow Data, pick the correct variable (in this case, since we are updating NextNumber pick the variable called NextNumber). Since you are updating an item on another list, the WF wants to know which item you want to update as before, so you have to provide the same info as in previous lookups. Again, if you are using the built-in list ID field, you won't get the "error" message, but if you are using title or some other field, you will.

Here is what the WF looks like when done, unless...

…you want to create an else branch that runs when the LNA ID is not empty, to log when the WF runs when it shouldn’t.  Which I think is a good practice.

If you are expecting moderate traffic that might have two people creating documents at the exact same moment, you might consider requiring check out on the Unique Number List.  Of course, since lists do not have Require Check Out as an option, you will have to go with a library instead of a list.  Everything in that scenario is the same, though you need to provide a dummy document for the library, in addition to the calculated fields.

 

After you check to see if the Unique ID in the Target List item is in fact empty, you will want to test if the document in the Unique Number Library is currently checked out, and wait for change in field if it is checked out.  Then wrap the entire process of getting the unique ID and updating both lists in a Check Out/Check In of the Unique Number Library item. (Checking out the Target list item if required would be wrapped inside the Check Out of the Unique Number List item.)

 

Hopefully this cleared up any confusion about the steps to build the lists and workflows.

The Custom Unique Automatic ID Trilogy

Add a Unique Auto-Incrementing Column to a SharePoint List
Auto-Increment Workflow Screenshots
Autonumbering Event Handler