cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Guero
Level: Powered On

Adding Sequential Numbers to a SharePoint List Column

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:BSS.png

 

 

Any advice would be appreciated!

Thank you!

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: Adding Sequential Numbers to a SharePoint List Column

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.

0-Trigger-GetItems.png1-Actions3-4-5.png2-Actions6-7.png3-Action8.png4-Action9.png

 

View solution in original post

2 REPLIES 2
Highlighted
Super User
Super User

Re: Adding Sequential Numbers to a SharePoint List Column

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.

0-Trigger-GetItems.png1-Actions3-4-5.png2-Actions6-7.png3-Action8.png4-Action9.png

 

View solution in original post

Guero
Level: Powered On

Re: Adding Sequential Numbers to a SharePoint List Column

Hi @RobElliott,

 

Thanks for your walkthrough - it worked perfectly!

 

Many thanks

Helpful resources

Announcements
firstImage

Incoming: New and improved badges!

Look out for new contribution recognition badges coming SOON!

firstImage

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Top Solution Authors
Top Kudoed Authors
Users online (5,433)