Adding Sequential Numbers to a SharePoint List Column

[Guero]

Hi,

I'm looking to introduce unique sequential numbering into a SharePoint List - currently we ID items via something we call a BSS Number, if you look at my screenshot below you'll see we have a 'BSS.No' column, with BSS-001 for one item , BSS-002 for the next, and so forth. 

Currently, we enter these BSS numbers manually into the a 'Single Line of Text' column when we add a new item to this list, however this is kind of inneficient as we first have to go through the list to find the newest item so we can see what the last number used was, so going forward I'd like it to try and automate it so when a new item is added, the BSS number would just increment upwards 1 from the last entered BSS number (IE if the last item had a BSS of BSS-50, then the next item would automatically be assigned BSS-051), however I'm not sure how to do this. I know SharePoint Lists have a native ID field, however we'd prefer to use our own identification method as we've already been using the BSS numbers for identification for quite a while, and it'd be a bit of a pain to switch over, plus I'm not sure how accurate the native ID numbers would be.

Would it be best to use a Flow for this, and if so, how would I go about doing it? Using formulas? 

See list below:

Any advice would be appreciated!

Thank you!

[RobElliott · Accepted solution]

Hi @Guero , strangely I answered a very similar question in my own company yesterday so the steps I gave then with the incrementing number starting at 100000 are below and the screenshots further down. I hope it helps but if it's not quite what you want do come back with any questions.

For the incremental number beginning 100000 I've got my main list (called CropLocations) where the column to hold the incremental value is called ProjectInc and is a single line of text column.

I've also got a separate list called CurrentIncrement with the title column just having a value of "Current value". There is also a number column with no decimal points called Inc with a default value of 100000.

The Flow is:

1. A Sharepoint Trigger: "When a new item is created".
2. The first action is a SharePoint action "Get items" that gets the items from the CurrentIncrement list, and of course there's only 1.
3. We then get the current value of the increment by adding a Compose control and in the field we select Inc which is the column name from the CurrentIncrement list. We rename this compose ComposeCurrent.
4. You will see it wraps the compose control automatically in an Apply to each control. We add all the following actions inside this Apply to each.
5. Add a SharePoint "Get item" (not get items!) and select the site, the CropLocations list and we add the ID from the dynamic content box.
6. We add another Compose control to add 1 to the output of the ComposeCurrent control. To do this, in the Expression tab on the right type add(outputs('ComposeCurrent'),1) and rename the control to ComposeIncremented.
7. We now need to convert the that new ComposeIncremented value into text for the main list, so we add another Compose control and in the Expression tab type: string(outputs('ComposeIncremented')) and rename it to ComposeProjectInc
8. We now want to update the CurrentIncrement list with the new value, so we add a SharePoint Update item action.
9. Finally we add the increment number to the main list..

Rob
Los Gallardos
If I've answered your question or solved your problem, please mark this question as answered. This helps others who have the same question find a solution quickly via the forum search. If you liked my response, please consider giving it a thumbs up. Thanks.

[Guero]

Hi @RobElliott,

Thanks for your walkthrough - it worked perfectly!

Many thanks

[Anonymous]

@RobElliott Hello,

I was able to use your solution for incremental numbers, however the incremented number only works on the very first entry in my list (it would be your CropLocation list). Something that is different than what appears in your screenshots is "Title" is a required field and I've added that and I'm thinking that shouldn't be the issue. Any help would be greatly appreciated.

Thank you,

Shery K.

[dmclaughlin]

I've tried the example in your post but cant quite get it to work.  I cant get the add expression to work in mine?

[zelsawaf]

Hi @RobElliott 

Can you help me creating a similar flow that auto generate the number without depending on any other list or column. 

[Loosersic]

Hi,

I'm trying to do the same and turn our ancient Purchase Order manual system into an Adobe PDF/Power Automate/Sharepoint List flow.
Adobe Sign does not have sequential options for numbers - everything else works a treat though.
Sharepoint has an ID field that is automatically incremented that you can make visible - or, concate...

[MariaMoran]

Hello!

Mine is doing the same, did you figure out what is the reason?