Adding the next incremental number in the SharePoint list

(0) Share

Report

Posted on by nshaw75

440

Apologies as I am certain this has been asked before, or a similar question at least.

I am creating an list of all ongoing projects. The projects will be initiated by a Power App form being completed. Each project will have it's very own Legacy Number created from the following :

{Site} - {area code} {next number on SP List}. eg LNDOF1234 would indicate the London Site Office Project #1234

The site & area code is a code which will be based on the return from a Form in Power Apps, the number from the SharePoint list must be a unique value and running incrementally. The Power Apps return will originally be the full wording and I would want to abbreviate them into a code.

Categories:

Building Power Apps

I have the same question (0)

All responses (11)

Answers (0)

Adrian_Celis 1,652 Moderator on at

Like (0)

Report
Copy link

Link copied!
Hi @nshaw75
First sort your SP List by Legacy number by descending order. The first record will be the latest legacy number.
When you have the latest legacy number, get the number part (exclude the letters) and add + 1. Then patch your next legacy number by using the added number.
Try something like this. This is how you get the next number in the series

Set(NewLegacySerialNumber,Value(Mid(First(Sort(YourSPListHere,LegacyNumber,SortOrder.Descending)).LegacyNumber,6))+1)

Then patch your new legacy number as such:

Patch(YourSPListHere,Defaults(YourSPListHere), { LegacyNumber: Site & AreaCode & NewLegacySerialNumber })

Here is a similar concept:
Power Apps – Serial Reference Numbers

Was this reply helpful? Yes No
opticshrew 193 on at

Like (0)

Report
Copy link

Link copied!

I'd tackle this slightly differently.

I'd have your form submit into the list, then use the Form.LastSubmit value to re-submit and you can then use the item id in the new id.

Was this reply helpful? Yes No
nshaw75 440 on at

Like (0)

Report
Copy link

Link copied!

Thanks guys for helping out. @Adrian_Celis I am lost as to where I would put the formula into. I have set my sharepoint list up into descending order but I get stuck around that pont 😐

Was this reply helpful? Yes No
Adrian_Celis 1,652 Moderator on at

Like (0)

Report
Copy link

Link copied!

@nshaw75
Are you using patch to create new records into your SharePoint list? Can you share me the formula of your patch?

Was this reply helpful? Yes No
nshaw75 440 on at

Like (0)

Report
Copy link

Link copied!

Hi @Adrian_Celis
Sorry I am a bit of a noob and have just been using a form which is connected to the SharePoint list and when I hit the button I use the SubmitForm(Form1) command to populate the list.

Was this reply helpful? Yes No
Adrian_Celis 1,652 Moderator on at

Like (0)

Report
Copy link

Link copied!
Hi @nshaw75
Alright. understood. So you have to put this code at the bottom of the SubmitForm(Form1)

Patch(YourSPListHere,Form1.LastSubmit, { LegacyNumber: Site & AreaCode & Value(Mid(First(Sort(YourSPListHere,LegacyNumber,SortOrder.Descending)).LegacyNumber,6))+1 })

Replace YourSPListHere with the name of your SP List.
Make sure to have a column named LegacyNumber as a signle line text in the SP List.
Replace Site with the Site number
Replace AreaCode with the Area Code

Was this reply helpful? Yes No
nshaw75 440 on at

Like (0)

Report
Copy link

Link copied!

@Adrian_Celis Thanks for the help.

I have noticed that some of the columns have slightly different names, so i have adjusted the formula accordingly.
My formula for the OnSelect property is as follows :
SubmitForm(Form1);Navigate(Screen1);Patch('Project Tracker list - PowerApp',Form1.LastSubmit,{LegacyName:"LDS"&Value(Mid(First(Sort('Project Tracker list - PowerApp',LegacyName,SortOrder.Descending)).LegacyName,6))+1})

I have no errors and it has submitted the form, although the legacy number hasn't been filled in.

Was this reply helpful? Yes No
Adrian_Celis 1,652 Moderator on at

Like (0)

Report
Copy link

Link copied!
@nshaw75 Oh so you only added LDS as a prefix, so that means your ref number will be LDS1
Therefore you should change the 6 into 4 because it should get the 4th character. Try

SubmitForm(Form1);Navigate(Screen1);Patch('Project Tracker list - PowerApp',Form1.LastSubmit,{LegacyName:"LDS"& Coalesce(Value(Mid(First(Sort('Project Tracker list - PowerApp',LegacyName,SortOrder.Descending)).LegacyName,4)),0)+1})

Was this reply helpful? Yes No
nshaw75 440 on at

Like (0)

Report
Copy link

Link copied!

Hi @Adrian_Celis . I have popped the code in and I am now getting a network error: Requested operation is invalid, when trying to submit the form, the red line in the formula bar is under the first part - SubmitForm(Form1)
which I am finding strange

Was this reply helpful? Yes No
Adrian_Celis 1,652 Moderator on at

Like (1)

Report
Copy link

Link copied!

Hi @nshaw75 ,
If you remove the code you added and only retained SubmitForm(Form1), does it still have an error after submitting?

Was this reply helpful? Yes No