So you’re creating a custom List or Document Library in SharePoint and you have a requirement to generate unique ID numbers for each Item or uploaded file which the included ID column won’t fulfill. The following article will show you how to do it, step-by-step.
Planning
A custom ID number should ideally be not to long, 15 characters at most. It should also convey information about the item it is attached to for anyone who knows how to read it but be complex enough to make duplication impossible. The number we will build in this article will consist of three components:
- Document Type
- Unique Number
- Creation Date
These components will combine using a process called concatenation in to a unique ID number with the following format
[Doctype]-[Unique#]-[CreationDate]
Document Type
There are 2 possible methods of compiling this component, both of which pull data from a Choice column
Method 1:
In this method you create a Choice site column called DocType, containing the possible options for document type including a numeric prefix. This should look something like this:
-
-
- 01 Invoice
- 02 Memo
- 03 Contract
-
Next, create a Calculated column containing the following formula: =LEFT([DocType],2) this should return just the numeric code from the selected option devoid of text.
Method 2:
The second method uses a PowerAutomate Flow to populate a field based on the selection from the choice column. This time you create the DocType Choice site column without the numeric codes and create a second site column of the Single Line of Text type called DocTypeNo. A Power Automate Flow is triggered on item creation which populates DocTypeNo with a numeric code based on a switch statement as illustrated below
Unique Number
Once again there are 2 ways of going about acquiring this data. The first utilizes the system-provided ID Column while second uses a 2 List approach to provide additional control over the number generated.
Method 1:
This method is very similar to the one described above. A Single Line of Text column is created (titled, in this example [IDCopy]) and a Power Automate Flow is created, triggering on item creation, to copy the contents of the system ID field into the new IDCopy field. This step is required because the ID column is protected and not accessible by directly for use in calculated columns or lookups. This field can then be used in a calculated column to form a component of a larger ID number.
Method 2:
In the second method, first we create a Single Line of Text column called IDCopy, as before. Next, we create a new custom list in which the Title field is renamed IDNumber and a Calculated Column, named [IDCalc] is created with the following formula: =CONCATENATE(REPT(“0”,6-LEN([IDNumber])), [IDNumber])
A single item is created in the list and the IDNumber filed is set to 1. The IDCalc field should return the figure 000001. Now a Power Automate Flow is created, triggering on item creation, to copy the contents of the IDCalc field into the new IDCopy field and then increment the IDNumber field by 1. This provides a more controllable and customizable unique ID number than Method 1 at the expense of being less secure as the source list could be vulnerable to being tampered with.
Creation Date
A system provided Created Date column already exists in SharePoint. For this component we will simply reformat it’s contents. To do this you will create a new Calculated Column, called IDDateClac with the following formula: =TEXT([Created],”ddMMyy”)
Bringing It All Together
Finally, we will combine all the elements created above by creating another Calculated Column, this one called UniqueID#, which uses the following formula to combine the modules created in the above sections into a single entity: =CONCATENATE(DocTypeNo,”-“,IDCopy,”-“,IDDateCalc).
This should return a composite number that looks something like this: 01-000001-010121
