Learn how create a dynamic number column with incremental values - ideal for project numbers
The challenge
SharePoint does all ready have an incrementing number column - the ID field.
You can’t really control the ID field, or change the increment value, or raise the start value.
The solution
I like to use Power Automate to create the number. It gives me full control of how I would like the number to look like, and I can mix it with other values.
The only down site is that the number is not created instantly. We will mix in some JSON column formatting so that the user won’t notice.
Create a new list called settings and add two new columns
- ProjectNumber (Number)
- Comment (Text)
By adding a comment to the settings list, we can use this list for other settings as well.
I like to add these type of lists under an Administration label in my menu
Power Automate
When an item is added to my projects list I will use Power Automate to take the current value from the settings list, use that number to create my project number, and then raise the value in the settings list.
We will create a simple number, but also a bit more advanced number that contains the year, number & everything before @ in the users e-mail
Create a new automated cloud flow and use the trigger When an item is created
Initialize an empty float variable
Use an Get item action. In my settings list my number has Id 1 - yours could be different
Concurrency control
If the users are creating two projects at the same time, the flows will run simultaneously, so you will end up with the same project number.
To avoid this behaviour click on the 3 dots on your when an item is created trigger and turn on concurrency control and change the value to 1
This will make sure that one flow is finished before the next will run. The only down site is that we can’t control which will run first
Now we will update the projects list. The simple version is to just insert the ProjectNumber value that we just got from the settings list.
We can also make the number a bit more advanced, like I did in the ProjectNumberString column. This is just an example, and can be done in many ways
Year
formatDateTime(utcNow(),'yy')
The letters before @ of the creators e-mail
first(split(triggerOutputs()?['body/Author/Email'], '@'))
The last thing we want to do is to raise the number in the settings list. You can change the number in the increment variable to whatever you prefer
JSON formatting
The Power Automate is not triggered instantly, so let’s add some JSON formatting to our column, so that the user won’t just see an empty value
The code will check if the field value is empty. If it is empty it will show the value auto-created if not it will show the value of the field with @currentField
{
"$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
"elmType": "div",
"children": [
{
"elmType": "div",
"style": {
"display": "flex"
},
"attributes": {
"class": "ms-fontColor-neutralPrimary ms-fontColor-neutralDark--hover ms-font-m"
},
"children": [
{
"elmType": "span",
"txtContent": "=if(@currentField == '', 'auto-created', '@currentField')"
}
]
}
]
}
Now you know how to create a dynamic number in SharePoint - Happy Power Automating :)