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

 

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

 

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

Microsoft Business Applications Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 Release Wave 2 Plan

Power Platform 2019 Release Wave 2 Plan

Features releasing from October 2019 through March 2020

thirdimage

Flow 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 Flow Community Video Gallery!

Top Kudoed Authors
Users Online
Currently online: 208 members 4,033 guests
Please welcome our newest community members: