My career in technology

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

Advertisements

Comments on: "Auto-Increment Workflow Screenshots" (22)

  1. […] Need more info?  I just posted an update with screenshots! […]

  2. […] this month I finally got around to a follow-up post on the subject of custom auto-incremented IDs, with detailed screenshots and instructions for setting up an SPD workflow to provide an automated […]

  3. Alan Jones said:

    We tried this, but it didn’t work. We had an error message in the ID column of the Target Library

    • Was your target column the actual ID column? As in [ID]? If so, that will not work. The ID is managed by SharePoint and cannot be written to by any means (think the identity column in a SQL table). You need to create a custom column to contain that data.

      If I misunderstood, let me know and I will try to assist.

  4. Andy Cuthbert said:

    Thanks Jim, this solved our problem perfectly!

  5. […] Auto-Increment Workflow Screenshots […]

  6. Mykal Lesure said:

    Jim,

    I am trying to implement this for a SharePoint Action Items list (Modified Task List). What adjustments would I need to make to do so? I’ve attempted this myself a couple of times, but to no avail. Any help would definitely be appreciated.

    • I don’t know of any special requirements. Can you provide more details about the problem you are having and the results you are getting?

      • Mykal Lesure said:

        Jim,

        I am unable to get the Workflow to execute properly, and I thought it might be because it was looking for a check in and check out? Or a connection to a document library rather than a task item list? I simply cannot get the ID numbers to populate in the Action Items list for existing and new items. However, I did successfully establish the Unique Number ID in the (Unique Number List), entered 0 (NextNumber), 1 populated as the FollowingNumber, and it populated with my appropriate string ID in the NextUniqueID column. I just do not know where to go from there to make the connection between the two lists.Thanks.

  7. ROnan Currid said:

    Hi Jim,

    My workflow is automatically cancelling with an error ‘ Coercion Failed: Unable to transform the input lookup data into the requested type.’

    I looked up the error and it is something to do with the lookup values?

    One thing I have noticed is that for setting the NextNumber variable to the FollowingNumber field, the Return Field As = ‘As Double’. The same occurs for the updating list item line when trying to return the NextNumber values.

    I am using SP 2010.

    Any ideas what i sgoing wrong?

    Thanks in advance

  8. Hi,

    Is there instructions to do this on Sharepoint 2007?
    I was following the instructions until I hit the create workflow part, at which point the screens and options were completely different.

    • These are the instructions for SP2007. Feel free to shoot me an e-mail with screenshots of what you are seeing (you can find my e-mail on my resume page).

  9. Hi Jim,
    your procedure works correctly in my implementation, but I’m facing issues with multiple uploads. I think I need to use the checkIn/checkOut approach in a Unique Number Library.
    Therefore, in my workflow linked to the target list item, I should wait for Check-in in the Unique Number Library. I cannot do this with SP Designer out-of-the-box actions… I guess. I found this action listed “Wait for change in document check-out status”, but it refers to the current item checkout status, I cannot select an item in anoter list, that is Unique Number Library.
    Maybe there’s another action I can use… could you help me to find it?
    Thanks a lot,
    Angela

    • I don’t have this system implemented in my currrent environment. If you are going to do check in/out on the Unique Number list, perhaps a workfow on the Unique Number Item that sets a flag when the item is checked in. I envision something like:

      Workflow on target item starts.
      …Waits until flag on Unique Number list item is not set to “Do Not Disturb”.
      …Checks out the Unique Number list item.
      …Sets flag to “Do Not Disturb”, saves (This starts workflow on Unique Number list item, which waits for Unique Number list item to be checked in).
      …Performs the tasks to get the number and update.
      …Checks the Unique Number list item back in.
      Workflow on Unique Number list item now runs (and workflow on target item ends).
      …Updates Unique Number list item, setting flag to “I’m ready to be disturbed now!”.
      …Workflow on Unique Number list item ends.

      Let me know how it works out!

  10. Hi Jim – I’m alittle late to the party 🙂 I implemented this at work and it was working great as long as I manually enter an item. We have a setup that when an item is created in an intake list., a workflow then creates an item in the target list with the custom ID auto-increment. When I do one record at a time, the items are created and the custom ID operate as expected. Now that we are testing our import of multiple records into the intake list, the custom ID is now repeating the same number instead of incrementing. We are using Access to import XML files into the intake list. Each record is creating an new item in the target list but the custom ID repeats the number of times = the number of records. For example, if we upload 4 records in the intake list, 4 records appear in the target list but the custom ID repeats 4 times instead of incrementing by 1. Any suggestions or explanation on why this happens? Thanks for any assistance you can provide, Jerri

    • Well, it would appear that the rate of new items being created means that new workflows access the number item before the last workflow updates it.

      You could solve that by using a library rather than a list, and enforcing a check-out, get the number, update the number, and check-in, and requiring that the new workflows wait until the item is checked back in before proceeding (check in/out is not available on lists).

  11. Hi Jim – I need to create a column in a SharePoint List which will autogenerate a sequential number. What’s the easiest way to do that?

  12. Hello, this is exactly what I need however I need it to reset the incremental number at the beginning of each month. Can you describe how you do that for each year and I will apply that to each month,

    Thanks!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: