Forum Discussion
Solved
Auto-Increment Number Field in a SharePoint List
I need to create an auto-increment field with a standard amount of digits. The format needs to be like this T000001, T000002, T000003, ......T000010, etc (7 Digits). When it gets to the 10th record i...
Hi Keenan (Ninjak), this complex auto-increment can be achieved relatively easily using 2 lists in SharePoint and a flow in Power Automate. The result will look like this in the main list which I've called Crop Harvest:
You will need a second list (which I've called Increment) to hold the next value. In the result above I manually changed the value to show the difference based on the number, and I've manually changed it back to 9 for now but you will see that it will auto-increment in the flow. This second list will only ever have 1 item which is the next value to be used:
In the flow the trigger I've used is When an item is created in the SharePoint main list.
Next we get the 1 item from the Increment list, and the next action is to initialize a string variable which I've named varIncrementNumber:
The next action to add is a Compose control which I've named ComposeNext and you select Next value from the dynamic content box on the right. What you will see when you do this is that it wraps itself in an apply to each control. It does this because although there is only 1 item in the Increment list there could potentially be lots so it adds the apply to each. That's fine, it's expected and you don't try to delete it but just leave it there.
The next action is a condition. For this first condition we are only going to test if the output of the previous compose action is less than 10 (we'll do the others shortly):
If it IS less than 10 then in the If yes green channel we add an Append to string variable and in the value type T00000 immediately followed by the output of the ComposeNext selected from the dynamic content box on the right:
If the ComposeNext value isn't less than 10 then in the red If no channel you add another condition. In the left hand box select the Output from the ComposeNext from the dynamic content, select is greater than or equal to, and then in the right hand box type 10. Then add another row and do the same but make it less than 100.
Again, you'll get a green if yes channel and a red if no channel. In the green channel, as before, add an append to string variable action and this time in the value add T0000 and then the output of the ComposeNext:
In the red If no channel add another condition as before but make it greater than or equal to 100 and less than 1000.
You keep doing that - changing the number of leading zeros each time of course - until you get to the last condition which is if the output of the ComposeNext is greater than or equal to 10000 and less than 100000 then you will add an Append to string variable in both channels. In the green If yes channel it will be T0 plus the Output and in the red if no channel it will be just T plus the Output.
So we've got the right value with the T plus correct leading zeros in our variable now. The next action is still inside the apply to each but not inside any of the conditions. The action is a SharePoint Update item action and in the text column where you are storing the increment text value you select the string variable varIncrementNumber. You'll see I've also stored the actual numeric value number from the ComposeNext:
We're almost at the end now and the next action is to add a Compose and add 1 to the ComposeNext value. So for this we use an expression of add (outputs('ComposeNext'),1). Finally we use the output of that to update the Increment list:
Whatever value you have in the Increment list the flow will check that and apply the T and the correct number of zeros and then the increment value. It will take just 2 or 3 seconds to do this and to update the main list and the increment list.
That took far longer to write out and do the screenshots than it took to prepare the SharePoint lists and do the flow!!
Do come back with questions about any of this.Rob
Los GallardosMicrosoft Power Automate Community Super User
Hi Keenan (Ninjak), this complex auto-increment can be achieved relatively easily using 2 lists in SharePoint and a flow in Power Automate. The result will look like this in the main list which I've called Crop Harvest:
You will need a second list (which I've called Increment) to hold the next value. In the result above I manually changed the value to show the difference based on the number, and I've manually changed it back to 9 for now but you will see that it will auto-increment in the flow. This second list will only ever have 1 item which is the next value to be used:
In the flow the trigger I've used is When an item is created in the SharePoint main list.
Next we get the 1 item from the Increment list, and the next action is to initialize a string variable which I've named varIncrementNumber:
The next action to add is a Compose control which I've named ComposeNext and you select Next value from the dynamic content box on the right. What you will see when you do this is that it wraps itself in an apply to each control. It does this because although there is only 1 item in the Increment list there could potentially be lots so it adds the apply to each. That's fine, it's expected and you don't try to delete it but just leave it there.
The next action is a condition. For this first condition we are only going to test if the output of the previous compose action is less than 10 (we'll do the others shortly):
If it IS less than 10 then in the If yes green channel we add an Append to string variable and in the value type T00000 immediately followed by the output of the ComposeNext selected from the dynamic content box on the right:
If the ComposeNext value isn't less than 10 then in the red If no channel you add another condition. In the left hand box select the Output from the ComposeNext from the dynamic content, select is greater than or equal to, and then in the right hand box type 10. Then add another row and do the same but make it less than 100.
Again, you'll get a green if yes channel and a red if no channel. In the green channel, as before, add an append to string variable action and this time in the value add T0000 and then the output of the ComposeNext:
In the red If no channel add another condition as before but make it greater than or equal to 100 and less than 1000.
You keep doing that - changing the number of leading zeros each time of course - until you get to the last condition which is if the output of the ComposeNext is greater than or equal to 10000 and less than 100000 then you will add an Append to string variable in both channels. In the green If yes channel it will be T0 plus the Output and in the red if no channel it will be just T plus the Output.
So we've got the right value with the T plus correct leading zeros in our variable now. The next action is still inside the apply to each but not inside any of the conditions. The action is a SharePoint Update item action and in the text column where you are storing the increment text value you select the string variable varIncrementNumber. You'll see I've also stored the actual numeric value number from the ComposeNext:
We're almost at the end now and the next action is to add a Compose and add 1 to the ComposeNext value. So for this we use an expression of add (outputs('ComposeNext'),1). Finally we use the output of that to update the Increment list:
Whatever value you have in the Increment list the flow will check that and apply the T and the correct number of zeros and then the increment value. It will take just 2 or 3 seconds to do this and to update the main list and the increment list.
That took far longer to write out and do the screenshots than it took to prepare the SharePoint lists and do the flow!! Do come back with questions about any of this.
Rob
Los Gallardos
Microsoft Power Automate Community Super User
I realize that this thread is over a year old. But I have had this bookmarked and attempted to make this work multiples times over the past 6 months. I finally was able to figure out where I went wrong and got it to work.
So after it finally working on a fresh list, I decided that I would bring in my already created log file and put all those logs in there and update my Increment list to be the next value. I then set my flow to be active. And it worked....but it keeps looping through my already created list values.
Where should I begin looking to stop the flow from auto running even though I have not clicked the create new in my sharepoint list? Please help as I have had over 2000 flows run in the last hour.
JamesC007. Rob's post was great, but since he wrote it, a new "FormatNumber" option became available which makes things much easier. I've screenshotted the following from a real working scenario we use internally:
First, set the AutoNumber variable and grab the current AutoNumber single List Item:
Then, in a For Each loop, increment the variable, and format using the Format Number action. Note the number format string is stored in the List, screenshot further down. (The Update Projects List Item just applies the current number to our separate project list)
Note that the way we do this is to store the next available number in our list, apply the next number, then increment afterwards.Finally, update the incremented number into the AutoNumber list item:
Note, the reason we're not getting any infinite loops is that we only trigger this whole flow when an item (or file, depending on list or library) is created. It is important to not use the "Created or Modified" otherwise the action that updates the number on your target document or list item will continually fire...you could use a Trigger condition to resolve that, but it's easier to just use the "is created" trigger.
If you needed to apply an auto-number as a one off to a historical set of list items, then you could create a separate flow with a manual trigger that you only run once, that filters all your items/documents then uses a foreach to apply the above logic. In that scenario you would need to make sure you set your loop concurrency to 1 only, to allow the number to increment correctly.
Quick look at the AutoNumber list, contains a single item as follows:
Note from above, the NumberFormat is a string value, in the above case "00000" indicating a 5 character length...add more or less zeros to determine the length of the string
Hope that helps
- Thanks for posting this! I was able to follow along fairly easily, and it all makes sense once you see it run.
So with the way that you are doing this, you still have two lists? One that stores the next number and the other that is your log list?
We format our number as MTX21-0000, with 21 being the year. Ultimately, what I am attempting to do is make this as dummy proof as possible. So I would like to bring the last two digits of the year in automatically, as well as return back to 0001 as the first record of the year without interaction from a common day user. Can I incorporate that into the solution that you have posed?
I am fairly new to Power Automate as well as Power Apps, but I am attempting to learn this, which is why it took so long for me to familiarize myself with the Power Automate flow that was initially presented to get it to work mostly correct.
With that said, I think that the pictures you posted for your flow are out of order....correct? As you need to get the criteria first, which would be "when an item is created" in the log list. From there, you then "initialize the variable" and then "Get Items". Then onto "For each autonumber result" and "update autonumber list item". Am I understanding that correctly?
How do you set up the AutoNumber list to be the format that you want? As stated above, we include test and the current year in the log number.
JamesC007 Have you ever figured out a way to incorporate a year in your log numbers?
