How to create a custom ID for lists and libraries

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
Setting the formula for the solReqNum column in list settings

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
Step 1 of the Populate Solution Request Flow

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.

Step 2 of the Populate Solution Request Flow

Now when new items are created within the list or library, the flow will fire and create a new request number.

Modern SharePoint list with Flow that populates Request Number

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google 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 )

Connecting to %s