Creating a custom unique ID in a SharePoint list

Use Power Automate with SharePoint to create a customisable unique ID number in a list that does not rely on secondary lists to work.

This has been quite a challenge to achieve and to be honest, many of the solutions offered on the internet are either overtly complicated or the steps are too vaguely explained. Here’s my method, written in plain English.

The Challenge

I have a SharePoint list with entries in it. I need to automatically assign a code to each entry in that list that is unique and which automatically updates. My list is only ever populated by a Power Automate flow with data collected from Microsoft Forms.

SharePoint lists have an ID column (hidden by default) which contains a unique number for each entry. It is possible to establish a calculated column in the SharePoint list using this value, however it will break if the list is updated by a Power Automate flow.

The method below will create a unique ID that will consist of a prefix (“INV”) followed by a unique number string (for example: INV283). Please note that the number will not reset at the start of a new year and the methodology for that is beyond the scope of this article. As Power Automate adds new entries collected from my Microsoft Form, the number will increase by one.

Before you begin…

You will need to complete the following steps prior to commencement:

Establish a Microsoft Form.
Create a SharePoint list.
Connect the Microsoft Form to the SharePoint list via Power Automate.

Let’s begin!

Step-by-Step

Step 1

Enable the ID column in your SharePoint list by going to + Add Column > Show/Hide Columns and ticking the box beside “ID”.

Step 2

In your SharePoint list, create a new column by going to + Add Column and choose “Single Line of Text”. Name the column “UniqueID”.

Screen capture — The “Create a Column” dialogue box in SharePoint lists.

Step 3

Switch to Power Automate and either create a new Flow or otherwise alter an existing flow. To create a new Flow, click on + Create on the left menu and then choose Automated Cloud Flow.

Screen capture with circles over menu items. — The Power Automate main page.

Give the Flow a name such as “Generate Unique ID” and choose “When an item is created” as the trigger. Click on “Create”

The “Build an automated cloud flow” dialogue box in Power Automate.

Click on “When an item is created” and choose your Site Address and List Name from the dropdown menus.

Click on “New Step” and choose “Initialise Variable”. Set “Code” for the Name, “string” for the Type and add “ID” for the Value. At this step, you can add a prefix or suffix as desired. In our example, we’ll set it as “INV” and “ID” as shown in the picture below.

Click on “New Step” and choose “Update Item” from SharePoint. Fill in the Site Address and List. The “Id” should be set as “ID” and Title can be “Title”. Importantly, set the “UniqueID” column to our new variable, “Code”

The “Update Item from SharePoint” step in Power Automate.

Click on “Save” to save the Flow.

Step 4

Check the flow by entering some data and confirming that the “UniqueID” column has been updated with a new ID number.

Now when you complete your Microsoft Form, a new entry should appear in your SharePoint list with a custom unique ID.

Tips

This could be modified further. For instance

If you wanted to add leading zeroes, you could create another column in your SharePoint list (as a calculated value) and set the calculation as TEXT(VALUE(UniqueID),"000").
If you want to add a leading date/time string in front of your number, go back into Power Automate and change the Value under “Initialise Variable” to include the following Dynamic Content: formatDateTime(triggerOutputs()?['body/Created'], 'ddMMyyyyhhmmss')

Comments

7 responses to “Creating a custom unique ID in a SharePoint list”

On 13 November 2021, Natalie wrote:

This has been really helpful. Simple and easy quick guide. Thank you.

On 18 January 2022, Con wrote:

Very helpful. Thank you so much!

On 29 April 2022, Kathrin wrote:

Thank you for the instructions!
Unfortunately I can’t find “update item” in the last step.
Since I work in German, the “UniqueID” fields are not specified under “Element aktualisieren” (which would theoretically be the correct translation).
Can you give me a tip as to which keyword I can search?

On 29 April 2022, Kathrin wrote:

Thank you very much for your instructions!
After a short scruggle of understanding problems (lost in translation), I was able to implement an alphanumeric ID in my project lists :)

On 7 September 2022, Vlad wrote:

My unique ID is created, but its not showing under Update Item. I checked the column and its not hidden in a list, thoughts?
Thanks

On 14 September 2022, Jay wrote:

Hi
Thanks for the advice. Works for me, except I already have my number sequence at high number (Purchase Orders – PO-000375). How would I get it to create the next number in the sequence? I feel like it should be as an easy as auto filling a column in Excel, but I’m yet to discover it.

On 8 February 2024, Rem wrote:

is it possible to get a unique code from another column with a conditional format in the Sharepoint list? For example:
If Column B reflects Tower, Column A would reflect a unique ID with TOXXXX , and if Column B shows Ground, column A would reflect GRXXXX. Basically an additional identifier coming from Column B would be considered – numbering can just continue.