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.
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.
ID column in your SharePoint list by going to + Add Column > Show/Hide Columns and ticking the box beside “ID”.
In your SharePoint list, create a new column by going to + Add Column and choose “Single Line of Text”. Name the column “UniqueID”.
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.
Give the Flow a name such as “Generate Unique ID” and choose “When an item is created” as the trigger. Click on “Create”
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”
Click on “Save” to save the Flow.
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.
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
- 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:
6 responses to “Creating a custom unique ID in a SharePoint list”
This has been really helpful. Simple and easy quick guide. Thank you.
Very helpful. Thank you so much!
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?
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 :)
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 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.