SharePoint Online: Create Unique Auto Increment Column using SharePoint Designer Workflow
Requirement: Create a unique auto-increment column in a SharePoint Online list.
How to Set Unique number Column with SharePoint Designer Workflow?
Auto-incrementing columns are often used in SharePoint lists as unique identifiers for items. These columns automatically generate incremental numbers for each item added to the list, making it easy to track and organize the data. SharePoint Online allows you to create auto-incrementing columns in lists using calculated columns or workflows.
We can create a unique auto number column in SharePoint using the SharePoint Designer workflow. The overall idea is:
- Create a custom list, and name it “UniqueID” or something like that. Leave it with just the default “Title” column and add a row with a Title field value of “1” (Just one row!).
- In the “Requests” list (or whatever list you need a unique ID column), Create a SharePoint designer workflow, get the value of “Title” from UniqueID list, set the value of “Reference Number” or whatever column to the value from UniqueID List. Set the workflow to trigger on new item creation.
- Increment the “Title” field value of UniqueID list by 1 and update UniqueID list. (with ID=1, we’ve created only one row)
- Make sure the “Reference Number” column in the “Requests” list is set to read-only in NewForm and EditForms using JavaScript.
Here is the Auto number generation workflow in action:
Steps:
- Create workflow variables: varCounter and varCounterPlusOne of Number type.
- Set the variable varCounter from UniqueID list item with ID =1
- Update the current item’s “Request ID” in Requests list with the variable varCounter
- Increment varCounter by 1 and store it to varCounterPlusOne
- Update the UniqueID column’s row with the value of varCounterPlusOne.
Advanced Scenario: Create a Unique Reference Number from Multiple Columns
Here is another scenario where I need to generate unique and auto-increment values based on multiple column values. When a new entry is added to the Asset Register list, the “Tracking Number” should get auto populated based on the “Device type” field value selected, and the “Tracking Number” field value should be unique. This tracking number should be framed as a shortcode of the device and an Incremental number. E.g., For the first request under the “Desktop” device type, it should be “DS#1”, for the second request, it should be “DS#2” and so on. Here is the list of device types and shortcodes:
Workflow snapshot:
The “Device Type” field in the Asset Register list is a lookup field referencing the “Title” column of the “Device Type Config” list, which has all devices, shortcodes of the device, and a sequence number. So, when a new request is added to the Asset Register list, we need to get the sequence number and device shortcode values from the parent lookup list “DeviceTypeConfig”.
Result of the auto-generated tracking numbers through workflow:
In summary, you can create an auto-incrementing column in a list using the workflow explained above. Using a custom workflow is a simple method that triggers when you add new items to your list and automatically sets the unique value to a column based on the pre-configured values.
Thank you for these instructions – this is exactly what I need to do in a SP site, with one exception.
I was able to make this work successfully for a new item in an on prem environment for SP 2016. What I need to be able to do is run this workflow when an item changes if a specific column meets certain criteria.
In my scenario, I need to set a folio number on an existing line item when a column titled “Folio #” is blank and the column “Set folio number?” is modified to “Yes” (the default for this column is “No”)
I think I should be able to set the workflow to run when the item is modified and it should:
1-look for a folio number to see if the field is blank
2-if the field is blank check the column “Set folio number?” to see if the field is set to “Yes”
3-if the “Set folio number?” field is set to “Yes”, update the folio number field with the next folio number in the “FolioID” list using the variables as outlined in your article
4-go to end of workflow
Your process worked great when a new item is created but when I changed the workflow settings to run when an item is changed it never resolves.
Can you help?
Thanks,
Tammy