Skip to content

Dear Internet Explorer user: Your browser is no longer supported

Please switch to a modern browser such as Microsoft Edge, Mozilla Firefox or Google Chrome to view this website's content.

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:

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.

Screen capture
Enter the SharePoint URL and List name in Power Automate under “When an item is created”.

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.

Screen capture
The “Initialise Variable” step in Power Automate.

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

   

Comments

No comments have yet been submitted. Be the first!

Have Your Say

The following HTML is permitted:
<a href="" title=""> <b> <blockquote cite=""> <code> <em> <i> <q cite=""> <strike> <strong>

Comments will be published subject to the Editorial Policy.