Create a unique ID for a SharePoint 2007 List or Library using SharePoint Designer Workflow

12Jun09

Customer Requirement:  A unique ID was needed for each item in a list upon item creation.  The ID needed to start with the number 6000, increase by 1, and concatenate the number with the first three characters of a location code.

SharePoint Option:  Out of the box, SharePoint has an auto-generated field called ID that starts at 1 and increases by 1 with each new entry.  If you delete an item in a list, that ID is removed from the list as well so there is no chance of having a duplicate ID.  One issue we ran into is you cannot use ID in a calculated field for new items.  It will work fine for existing items but, the ID is not assigned to a new item until the user hits submit.  So the calculated column that uses ID will get a value of 0 on all new items and does not change the calculated column even if you refresh or edit the item.  You actually have to edit the calculated column and all existing items will be correctly calculated.   

Final Solution:  We wrote a SharePoint Designer workflow that will add the ID to the number 6000 and concatenate the number with the first 3 letters of the Campus Code.

How we did it:  In our list, we created the following columns required for the workflow.  (Several other columns exist in the list to collect additional information but, these were needed for the workflow solution.)  We used a content type to hide the fields that are used by the workflow from the user forms.

  1. “SPID #”  – Single Line of Text – hidden field
  2. “Campus” – Choice field with the names of the 5 campus choices prefixed by the campus code (ex: UCH – University of Colorado Hospital)
  3. “CTRCCode” – Calculated Column (Formula:  =LEFT([CTRC Campus],3)) – hidden field

Once the list was set up, we opened the site in SharePoint Designer

  1. Select File – New –  SharePoint Content – Workflow – Blank Workflow – OK.
  2. Give a name to the workflow – we used “Generate SPID”
  3. Attach the workflow to the list
  4. Select the start options – we selected manual and on item creation
  5. Click Next
Define your SharePoint Designer Workflow

Define your SharePoint Designer Workflow

 1. Provide a name for the first step of the workflow if you like – we used “GenSPID”

Now we needed to create a couple of workflow variables for use in our calculation (add the ID to the number 6000).  SharePoint stores ID as a string instead of a number so we will convert it to a number in the variable.

  1. On the actions menu, select “Set workflow variable”
  2. Click on workflow variable and click “Create a new variable…”
  3. Provide a name for the variable – we used StartNum
  4. The type should be Number
  5. Click on value and set the value to 6000
  6. Create another workflow variable – Click on Actions – “Set workflow variable”
  7. Click on workflow variable and click “Create a new variable…”
  8. Provide a name for the variable – we used IDValue
  9. The type should be Number
  10. Click on value and then click on the function button set the Source to: Current Item and the Field to: ID  (This will convert the ID string to a number so it can be added to the number 6000)
  11. Click on Actions and choose “Do calculation”
  12. Click on the first value and then the function button and choose Source: Workflow Data, for Field: select Variable: StartNum
  13. Leave the operant as “plus
  14. Select the second value and then the function button, choose Source: Workflow Data, Field: Variable:IDValue
  15. Output the value to a new variable – we used SPIDNum

Next we created a dynamic string to concatenate our SPID num with our calculated field called “CTRCCode”.

  1. Click Actions and select “Build a dynamic string”
  2. Click on dynamic string and in the String Builder, click Add Lookup
  3. Source: Workflow Data, Field: Variable:SPIDNum – click OK
  4. Click on Add Lookup again
  5. Source: Current Item, Field: CTRCCode
  6. Click OK.

Your String Builder should look like this:

 

SharePoint Designer String Builder

SharePoint Designer String Builder

  1. Create one more new variable to store your dynamic string – we used the name SPIDID

The final step is to Set the field SPID # in the list to the variable we just created called SPIDID.

  1. Click on Actions – “Set Field in current item”
  2. Click on field and select SPID #
  3. Click on value and then the function button
  4. Source: Workflow Data, Field: Variable:SPIDID

When you are finished, your workflow Designer should look like this:

 

SharePoint Designer Workflow for creating a unique ID for a SharePoint list

SharePoint Designer Workflow for creating a unique ID for a SharePoint list

The workflow variable should include the following variables and types:

 

SharePoint Designer Workflow Variables

SharePoint Designer Workflow Variables

Click finish and test the workflow!

Advertisements


6 Responses to “Create a unique ID for a SharePoint 2007 List or Library using SharePoint Designer Workflow”

  1. 1 Darren Lowe

    Great description on how to get this to work! One minor thing on my end, once the workflow is completed, I still need to do a page refresh to get the calculated value to appear on the list. We are using WSS3.0 in https mode (internal). Thanks for any thoughts

    • Good question. Unless the workflow completes before the screen returns to the default view, you will need to refresh the browser. Think of the browser like a digital camera, it takes a “snapshot” of the data at the time of refresh so if the workflow is not finished calculating the unique ID, or any other value, you will need to take a new “snapshot”, by refreshing the screen to see the updated values in the list. It really is a function of timing. Some workflows are simple enough to complete in time for the refresh but, in this case, the workflow is gathering a value that is entered at the time the item is created or edited so the chances are slim that it will complete before the refresh. This question comes up in a lot of different scenarios with SharePoint. However, as web development evolves, real-time data is becoming more of a reality with development tools like Silverlight, which will be a big part of SharePoint 2010.

  2. 3 Samer Shennar

    Nice don.e But the real challenge is to have a unique column that is input by user rather than generated. Any ideas?

  3. i tried to plug in your solution in the calculation field. It failed. Are you sure of the calculations. I tried six different times. Also, set up the fields just as you mentioned.

    • Hi Reggie,

      I am sure of the calculations, as a number of others have used the solution and had success. Can you explain the error you are getting or the problem you are having?

  4. 6 Monali

    Thank you very much. This is Great!!!


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: