Home > Work > Unique alphanumeric list ID’s via SPD Workflow and Calculated Columns #SharePoint #SP2010 #in

Unique alphanumeric list ID’s via SPD Workflow and Calculated Columns #SharePoint #SP2010 #in

A common thing I do for most clients is to create a unique alphanumeric ID that is consistent in length with pre-filled zeros.

Risk list alphanumeric ID example:

  • RSK0001

However, as has been documented many times (link); you cannot use the ID column of a list in a calculated column.  To get over this issue I use a simple SharePoint Designer workflow to copy the ID value to another column (Unique Reference) and then base my calculation on the Unique Reference column.

Step One: Create a common site column for use across the Site Collection

  • Column Name: Unique Reference
  • Column Type: Single Line Of Text

Step Two: Create a calculated column for the alphanumeric ID

  • Column Name: Risk ID
  • Column Type: Calculated Column
  • Formula: =”RSK” & TEXT([Unique Reference], “0000”)

NB: The TEXT function will prefill the ID with zeros

    Step Three: Add the Unique Reference and Risk ID column to your List or Content Type

Step Four: Create the SharePoint Designer Workflow

  • Create a list or content type (SP2010 only) workflow
  • Workflow should fire on Creation only (disable Manual and Edit)
  • Use the following steps:

image

  • Publish the workflow (list workflow)

OR

  • Assign the workflow to the content type (if you have created a redistributable workflow in SPD 2010) and assign the content type to the list (content type workflow)

Step Five: Hide the Unique Reference column in the list / content type

  • This will stop the Unique Reference column from showing to the end user.

NB: You must hide the Unique Reference column after you have created and published the workflow otherwise it will not appear in SharePoint Designer

You list items will now have a unique alpha numeric ID

NB: Please remember that automatic workflows will not fire if you are logged in as the farm account (link)

Advertisements
  1. January 13, 2012 at 13:11

    Thanks, for the above. Really helped me out. The Risk ID gets set when a document is uploaded to the library.

    How can I ‘pull’ the Risk ID back down and put it into an infopath form view for ‘readers’ to view?

    • January 13, 2012 at 17:24

      Hi Andrew,

      Thank you for your comments, I have emailed you directly about your question and once we reach a resolution I will post an update.

      Kind Regards

      Giles

  2. January 27, 2012 at 15:35

    Thank you so much for this write up. I’ve been struggling with this problem for a week now. I found other articles that accomplished the same thing but with much more complexity. I knew there had to be an easier way and I found it in your article.

    • January 29, 2012 at 19:46

      No problem Sean,

      Thanks for the feedback and really glad we could help

      Kind Regards

      Giles

  3. wai
    February 8, 2012 at 08:04

    Thanks! Really helpful.

  4. Fran
    February 26, 2012 at 14:16

    Can you make the RSK0000 always add 1 when a new item is uploaded?

  5. Ari
    April 5, 2013 at 07:40

    thanks for this WF but problem when i login user in this site they can showing a Error ” The file xxxx.docx has been modified by aaa\user i on 05 Apr 2013 13:05:34 +0530.”

    • April 5, 2013 at 20:14

      Hi Ari, i don’t believe we have successfully used this technique against document libraries. Have you considered the document id feature available in SP2010?

  6. Ari
    April 5, 2013 at 07:40

    Ari :
    thanks for this WF but problem when i login user in this site they can showing a Error ” The file xxxx.docx has been modified by aaa\user i on 05 Apr 2013 13:05:34 +0530.”

  7. WC
    April 9, 2013 at 23:20

    I have trouble with your formula for the calculated column. It results in a syntax error for me and will not create the column. What is wrong?

    • April 9, 2013 at 23:32

      Hi,

      Can you copy the exact formula you are using into the comments box or perhaps send it via the contact page and we can take a look. I suspect it is just a syntax error.

      Kind regards

      Giles

      • WC
        April 10, 2013 at 16:22

        Sure thing 🙂

        I copied your formula from the post: =”RSK” & TEXT([Unique Reference], “0000″)

        I altered it to this: =”GIS” & TEXT([GIS ID], “0000”)

        The altered one above reflects the new first 3 letters that I want as well as the title of the site column that I created.

      • April 10, 2013 at 18:29

        Thanks, I will set up the scenario tomorrow morning (UK time) and take a look.

  8. April 11, 2013 at 10:25

    Giles Hamson :

    Thanks, I will set up the scenario tomorrow morning (UK time) and take a look.

    Hi,

    I got this working this morning, however I received an error if I copied and pasted the formula in. I had to double click on the field from the list for it to recognise the formula.

    Could you give that a go and let me know how you get on please.

    Kind Regards

    Giles

  9. Shinu
    January 7, 2016 at 16:35

    Thankyou so much for this post. This was very helpful.

  1. No trackbacks yet.

Leave a Reply

Please log in using one of these methods to post your comment:

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: