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.
SharePoint Stuff 
Hi,
I’m following Modern SharePoint steps to populate a Request ID. After adding a new item, it returns to my list view. The Request ID does not include the [ID] immediately in the list view. After a few minutes, the Request ID refreshes to include [ID]. Is there anything I can do to have the list view show the correct Request ID immediately after adding an item?
LikeLike
Hi, thanks for the comment. Unfortunately there will always be a time gap between the item being andded and Power Automate running a flow and updated the item.
The only way I can think for you to have a request ID present is to use formulas in a calculated column. I found that can be problematic if you use the in-built ID column as your auto incrementing number but it’s worth trying. My classic example details how to do it or you can use this syntax reference as a guide https://support.microsoft.com/en-us/office/examples-of-common-formulas-in-lists-d81f5f21-2b4e-45ce-b170-bf7ebf6988b3
LikeLike
Hi Anthony,
The custom ID part works great, but I can’t get the number to increase incrementally with each new item.
Any ideas?
Many thanks!
LikeLike
Hi Ross, are you following the classic or modern example? Both solutions use the in-built ID column to create the incremental number – are you doing that?
LikeLike
Hi Anthony, got it working, however I need the numbers to be automated, so instead of people selecting any odd number, I’d like the flow to pull the unique ID somehow. I can’t figure out how to use it in my Calculated formula, and via the Flow I can’t quite figure out how to do it either.
I would really appreciate your help if it’s something you came across before?
LikeLike
Hi Alice,
Thanks for commenting. In my example flow I use the inbuilt list item ID column to create a automated incremental number. Have you tried that? Thanks Anthony
LikeLike
Fantastic, worked perfectly, thank you! I needed my IDs to start with D and begin at 10000 so I used formula =”D”&(solIncrementNum+10000) to get D10001.
LikeLike
@Juliana really happy to hear this worked for you ☺️
LikeLike