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:
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:
- Publish the workflow (list workflow)
- 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)