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: