Forum Discussion
AutoNumbering Columns in SharePoint List
Good morning,
I have a Sharepoint list used for tracking my documents. I manually number each document, but this has become challenging over the past month.
Is there a way to automatically create a unique ID?
I need something that starts with the two-digit year and has the document number created that year. for example, 22-205
22 for the two-digit year 205 for the document added that year.
The number will also need to be reset every year. Once January first, 2023 comes, the following document created will be 23-001.
Can someone assist me?
4 Replies
Just for interest, building on the approach already suggested of basing it on the ID column, below is an image from a live app we're running for giving a document a unique name based on a several different fields in the app, including the next ID at the end of the document number (it gets the max ID and add 1 to it).
Rob
Los Gallardos
Microsoft Power Automate Community Super User.
Principal Consultant, SharePoint and Power Platform WSP Global (and classic 1967 Morris Traveller driver)Hello torresH
you can do it with Power Automate Flows. Here is one example: https://www.bruce365.com/2020/03/31/sharepoint-auto-number-generation-solution-using-flow/
Other you can find with internet search machines, search "power automate flow sharepoint autonumber"
Regards, Dave
I just tried it with PA and its a failure. PA cant hold data so it cant know which number it did last. There is people who use lets say a number inside sharepoint and update it every time flow runs, but the issue with that is PA takes 1ms to run and requesting number takes a minute so you give same ID to all documents that is uploaded within a minute or so.
The above solution by DaveMehr365 is a direct response to the question, and is a good solution.
If you want to avoid a Power Automate component, you could try the alternative below.
1. Use the system ID column as ID
In every SharePoint list, there is a system column "ID" which contains a unique ID determined as an auto-increment. See screenshot below to show it. (Browse to the SharePoint list in question, then click on "All items" at top right, then "Edit current view", and then check the box next to the ID column.)
This follows a good practice that ID columns are probably best to not have significant prefixes or suffixes in them. If a row is deleted later, the related ID will be deleted and never be re-used. This is desired behavior.
2. Create your own Document ID column as text
You can create a separate column, call it "Document ID", and format it as Text. Presuming that you have the columns 'Year' ("2022") and 'Document Sequence Number' available ("205"), you can use a formula to concatenate them into a "YY-NNN" string, where YY is the last two digits of the year ("22"), and NNN is the sequence number of the document ("205"). Each document would then have a "Document ID" in the format that you look for. This would not be a primary key in the technical sense.
In other words, the "Document ID" column is the business identifier that you can use in your work processes whenever end users interact with an ID.
The "ID" column is the real primary key from a system perspective in SharePoint. You would probably not reveal that to end-users.
