In this example I demonstrate how to create a simple solution that allows users to submit a request to a list, then a workflow fires that updates the request ID field with our custom ID number.
ID numbers, reference numbers, ticket numbers…this is something that regularly gets asked to be a part of any SharePoint solution or request based system. My first thought when this is required is “easy, we can just use the SharePoint item ID column and use that”. However, creating a simple calculated column that leverages the in-built ID column is not as easy as it seems.
My first attempt at creating a custom ID column involved creating a new calculated column, and appending some text before the ID and then inserting the ID column into the formula, like this:
="REQ-00"&[ID]The problem with this approach is that when new items are added, the ID appears to “slip” resulting in the custom ID column having no ID number being pulled from the SharePoint ID.
Custom ID column – modern SharePoint
1. Pre-requisites
Before you begin you will naturally need to create either a list or library in SharePoint, and the relevant apps checked as part of your O365 license.
2. The setup
- Create a new column, with the type Number – I called this ‘solIncrementNum‘
- Create a new column, with the type Calculated – I called this ‘solReqNum‘, later renamed ‘Request Number’
- In the formula field, add the following:
="SOL-00"&[SolIncrementNum] - For the Data Type, select Single line of text
- In the formula field, add the following:
NOTE: for the Request Number formula if you want to prefix your custom ID with something else just replace what’s between the ” “ in the formula field above.
3. Build the Flow
Flow action: when a new item is created
- Create a new flow from the template “when a new item is created, complete a custom action”
- Give your Flow a name, I called mine “Populate Solution Request Number”
- In the “when a new item is added” step, make sure the site address and list name are the same as the list you built the custom ID column for earlier
Flow: update item
- Press + New step, start typing “update item”, select the update item action from the selection
- Select the site in question, then copy and paste the List Name from the previous action
- Make sure this action has the following fields set:
- Id: ID
- Title: Title
- solIncrementNum: ID
NOTE: make sure that when you set these fields, that the values you use are coming from the “when a new item is created” action.
Now when new items are created within the list or library, the flow will fire and create a new request number.
Leave a Reply