cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Kudo Kingpin
Kudo Kingpin

Auto-increment column inSharePoint

Hi,

 

Looking for an advice here?

 

I need to build an app and the first step is to create a consecutive number like Order Number. But the number should be tide with two comboboxes. i.e

 

1st combobox

cbo_departments

Maintenance : 10

HR: 20

Logistics : 30

Shipping : 40

 

2nd ComboBox

cbo_costcenter

Expenses : 01

Sales: 02

Transportation: 03

Meals: 04

 

I need to start in a number for instance : 1000, then when a user creates the first order Should be something like this:

I guess "1000" I can add this number to the first record in the order column?

Assuming is a HR employee adding an order for meals:

 

Next Order: 20041001

 

Any idea?

 

Thanks in advance

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User
Super User

Re: Auto-increment column inSharePoint

@sajarac 
Fair enough.  Was just checking your awareness of the ID column 😉

 

Create a new Number type column in SharePoint.  Then you can detect the next autonumber using this set of forumlas.  nextIdNumber will be the result you want to update your Number ID column with in SharePoint.

 

Set(startingIdNumber, 10056);
Set(currentIdNumber, First(Sort(your_datasource_name, NumberColumnName, Descending)).NumberColumnName);
Set(nextIdNumber, If(IsBlank(currentIdNumber), startingIdNumber, currentIdNumber + 1)); 

 

 

There wasn't any description of the context you are looking to use this code so I went for the more generic idea. 

 

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

View solution in original post

Highlighted
Community Support
Community Support

Re: Auto-increment column inSharePoint

Hi @sajarac ,

Do you want to create a Auto-Increment type column in your SP List based on the two ComboBox selected values?

How do you submit your data back to to your SP List? Using SubmitForm function?

 

Based on the needs that you mentioned, I have made a test on my side, please take a try with the following workaround:

I assume that your two ComboBox available options as below:

cbo_Departmentscbo_Departments

 

cbo_costcentercbo_costcenter

 

1. Add a Number type column in your SP List called "Order Number"

2. Generate an app based on your SP List. Go to your Edit form screen, enable the "Order Number" field data card in your Edit form.

3. Unlock the "Order Number" field data card in your Edit form, set the Default property of the OrderNumber Text Input Box to following:

Value(
   Switch(
       cbo_departments.Selected.Value,
       "Maintenance", "10",
       "HR", "20",
       "Logistics", "30",
       "Shipping", "40"
   ) &
   Switch(
       cbo_costcenter.Selected.Value,
       "Expenses", "01",
       "Sales", "02",
       "Transportation", "03",
       "Meals", "04"
   ) &
   Text(1000 + CountRows('Your SP List'))
)

Set the DisplayMode property of the OrderNumber Text Input Box to following:

DisplayMode.Disabled

When you select option within your two ComboBoxes, the Order Number field value would be generated automatically. When you submit your form data successfully, the generated Order Number value would be saved back to your SP List.

 

Of course, if you use Patch function to submit your data back to your SP List, you could apply above Order Number value generate formula to corresponding 'Order Number' field as below:

Patch(
      'Your SP List',
      Defaults('Your SP List'),
      {
         Title: "xxxx",
         .....
         'Order Number': Value(
                                Switch(
                                      cbo_departments.Selected.Value,
                                      "Maintenance", "10",
                                      "HR", "20",
                                      "Logistics", "30",
                                      "Shipping", "40"
                                ) &
                                Switch(
                                       cbo_costcenter.Selected.Value,
                                       "Expenses", "01",
                                       "Sales", "02",
                                       "Transportation", "03",
                                       "Meals", "04"
                                 ) &
                                 Text(1000 + CountRows('Your SP List'))
                           ),
          ....
      }
)

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
Highlighted
Super User
Super User

Re: Auto-increment column inSharePoint

@sajarac 

Any reason you are not using the auto-increment number column in every SharePoint List called ID?

 

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

Highlighted
Kudo Kingpin
Kudo Kingpin

Re: Auto-increment column inSharePoint

Hi,

 

I can use, but I would like to have the option to start in any number. For instance currently in a manual mode "excel template the next consecutive number is 10057. So as sson as I start with the app I can change a value for the next and then continue.

 

Regards

Highlighted
Super User
Super User

Re: Auto-increment column inSharePoint

@sajarac 
Fair enough.  Was just checking your awareness of the ID column 😉

 

Create a new Number type column in SharePoint.  Then you can detect the next autonumber using this set of forumlas.  nextIdNumber will be the result you want to update your Number ID column with in SharePoint.

 

Set(startingIdNumber, 10056);
Set(currentIdNumber, First(Sort(your_datasource_name, NumberColumnName, Descending)).NumberColumnName);
Set(nextIdNumber, If(IsBlank(currentIdNumber), startingIdNumber, currentIdNumber + 1)); 

 

 

There wasn't any description of the context you are looking to use this code so I went for the more generic idea. 

 

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

View solution in original post

Highlighted
Community Support
Community Support

Re: Auto-increment column inSharePoint

Hi @sajarac ,

Do you want to create a Auto-Increment type column in your SP List based on the two ComboBox selected values?

How do you submit your data back to to your SP List? Using SubmitForm function?

 

Based on the needs that you mentioned, I have made a test on my side, please take a try with the following workaround:

I assume that your two ComboBox available options as below:

cbo_Departmentscbo_Departments

 

cbo_costcentercbo_costcenter

 

1. Add a Number type column in your SP List called "Order Number"

2. Generate an app based on your SP List. Go to your Edit form screen, enable the "Order Number" field data card in your Edit form.

3. Unlock the "Order Number" field data card in your Edit form, set the Default property of the OrderNumber Text Input Box to following:

Value(
   Switch(
       cbo_departments.Selected.Value,
       "Maintenance", "10",
       "HR", "20",
       "Logistics", "30",
       "Shipping", "40"
   ) &
   Switch(
       cbo_costcenter.Selected.Value,
       "Expenses", "01",
       "Sales", "02",
       "Transportation", "03",
       "Meals", "04"
   ) &
   Text(1000 + CountRows('Your SP List'))
)

Set the DisplayMode property of the OrderNumber Text Input Box to following:

DisplayMode.Disabled

When you select option within your two ComboBoxes, the Order Number field value would be generated automatically. When you submit your form data successfully, the generated Order Number value would be saved back to your SP List.

 

Of course, if you use Patch function to submit your data back to your SP List, you could apply above Order Number value generate formula to corresponding 'Order Number' field as below:

Patch(
      'Your SP List',
      Defaults('Your SP List'),
      {
         Title: "xxxx",
         .....
         'Order Number': Value(
                                Switch(
                                      cbo_departments.Selected.Value,
                                      "Maintenance", "10",
                                      "HR", "20",
                                      "Logistics", "30",
                                      "Shipping", "40"
                                ) &
                                Switch(
                                       cbo_costcenter.Selected.Value,
                                       "Expenses", "01",
                                       "Sales", "02",
                                       "Transportation", "03",
                                       "Meals", "04"
                                 ) &
                                 Text(1000 + CountRows('Your SP List'))
                           ),
          ....
      }
)

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Highlighted
Kudo Kingpin
Kudo Kingpin

Re: Auto-increment column inSharePoint

Good morning,

 

Quick question now that you mentioned this:

"Any reason you are not using the auto-increment number column in every SharePoint List called ID?"

 

Is possible to get the SPL ID when the user creates a new record? and if so, is also possible to complete that number with zeros until the number reach the 10000 mark?

 

Current Order : 200410-09999

Next Order      : 200410-10000

 

And for the previous numbers: 

00009

00099

00999

09999

99999

 

I would like to keep consistent showing 5 digits number for the "order"

 

Any idea?

 

Thanks in advance

 

I mean in SPL First record = ID:1, second = ID:2, etc.

 

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

secondImage

Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

secondImage

Super Users Coming in August

We are excited for the next Super User season.

secondImage

Community User Group Member Badges

FIll out a quick form to claim your community user group member badge today!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (10,340)