Continue counting

[ASH3]

Hi

I need some help.

I have an Excel table with a serial number column. and I have a form in power apps before the user enter the serial number manually by checking the last serial number in the excel table.

And now I need to automatically generates the next sequential serial number by checking the last record in excel but also I want it to be fast. And I want it to be shown in the form. 

The desired format for the serial number is "ABC-DA-XXXXX", where "XXXXX" is the number that should increment by 1 for each new record.

For example, the sequence should be:

ABC-DA-2433

ABC-DA-2434

ABC-DA-2435 "last record before submitting the form"

ABC-DA-2436 "last record after submitting the form should be like this"

the 243X should not be fixed this is just an example

thank you

[DBO_DV]

Sorry i missed one Braket here you go:

Right(
    Last(Sort(SN,Column1,SortOrder.Ascending)).Column1,
    Len(Sort(SN,Column1,SortOrder.Ascending)) - Len("ABC-DA-")
) + 1

[ASH3]

Still same problem

[DBO_DV]

Yeah sorry .... another mistake from my part. I guess I'm a bit tired 😅

Here you go this should work now 

Right(
    Last(Sort(SN,Column1,SortOrder.Ascending)).Column1,
    Len(Last(Sort(SN,Column1,SortOrder.Ascending)).Colummn1) - Len("ABC-DA-")
) + 1

[ASH3]

That's worked really well thank you!! "last cell was ABC-DA-6"

but when it updated in excel it didn't change, and I think I have to refresh the collection each time.

If you have any ideas to make it more efficient or is it better to remove the collection.

Please response at your convenient time since you're tired 😬

[DBO_DV]

Great since it worked I'm just gonna put you all the code here that you would:

To have the data always up to date I would skip using a collection otherwise you have to re collect everything after every patch: 

Refresh(Table2);
With({LastRecord:Last(Sort(Table2,Column1,SortOrder.Ascending)).Column1},
Right(
    LastRecord,
    Len(LastRecord) - Len("ABC-DA-")
) + 1
)

Just a quick explanation why a collection wouldn't be good in this case.

Another user could always patch a record while you have all your Data already loaded in the Collection. So it could create twice the same ID, since you hadn't had the most up to date date.  

That's also why I added the Refresh just before. 

[ASH3]

Sorry but where should I write this code? Since I'm going to use Form and I will show this number in the form so I can write it in excel when submitting the form.

And thanks for the explanation! 

[DBO_DV]

No problem, since it is in a Form you can put this part in the Button that navigates you to the Form screen OnSelect 

Refresh(Table2);

The next part you can put it into the default property of your TextInput: 

With({LastRecord:Last(Sort(Table2,Column1,SortOrder.Ascending)).Column1},
Right(
    LastRecord,
    Len(LastRecord) - Len("ABC-DA-")
) + 1
)

You will probably have to unlock your Textlabel to be able to add this code. you can do it like this: 

You just need to click on it once and add the code here:

[ASH3]

[DBO_DV]

I've justed tested it on my side: it Works for me. I don't know if there is any difference to the code you have just try it again: 

With({LastRecord:Last(Sort(Table2,Column1,SortOrder.Ascending)).Column1}, Right(LastRecord,Len(LastRecord) - Len("ABC-DA-")) + 1)

If it does not work however then we will just go back to the version without with since that worked fine: 

Right(Last(Sort(Table2,Column1,SortOrder.Ascending)).Column1,Len(Last(Sort(Table2,Column1,SortOrder.Ascending)).Column1) - Len("ABC-DA-")) + 1)

[ASH3]

Both did not work