Calculate a record number based on last record

[GHvdBerg]

Hi Community,

I have been making the companies bookingsystem using Sharepoint and Powerapps.

Now, I'm trying to automate a part of it.

For instance, every new booking has an invoice number that has to be filled in. 

This number has the following build up 2017XXXX (year + number of invoice).

However, not every number has to be in the administration (invoice 20170023 could be deleted).

Also the number of the invoice does not correspond with the ID (sometimes invoice 20170011 is created before 20170009).

So, what I want is the following:

When I want to create a new record, by using NewForm in Powerapps, I want the default text in DataCardValue "Invoice Number" to be the number of the last record + 1.

Last booking/record was 20170346, so when I create a new form it should (by default) say 20170347.

I already tried to work with the Last() function, but couldn't get it working.

Can someone help me with this?

Thanks a lot!

G.H.

[CarlosFigueira · Accepted solution]

You can try sorting the SP list by the invoice id descending, and take the First element.

If you've created your app using the "Start with your data" option, then you can get that value when selecting the 'Create new item' option:

NewForm(EditForm1);
Navigate(
    EditScreen1,
    ScreenTransition.None,
    {
        isNewItem:true,
        defaultInvoiceId:Last(
            SortByColumns(
                SPListName, "InvoiceId", Descending)).InvoiceId
    })

And on the edit screen, you would update the Default property of the text input control that holds the invoice id (you first need to unlock the card in the Advanced pane in the right-hand side) to

If(isNewItem, defaultInvoiceId, Parent.Default)

[Delid4ve]

Value(Last(ListName).ID)+1

Does this not work??

[GHvdBerg]

Thank you Delid4ve,

This was a straightforward solution. 

This solves the first part for me. 

However, it created a new problem.

Somehow, it says that the last item in the column is 20170514.

The actual last booking I made was with number 20170669.

Do you have any idea why it ignores the bookings after 20170514?

Thank you,

G.H.

[Delid4ve]

If your using form submission you will need to add into the onsuccess and onfail events Refresh(listname)
If your using patch function then add in ;Refresh(listname) directly after your patch argument.
Basically it looks like it’s not refreshing the datasource after you add a new record

[GHvdBerg]

The weird thing is that the app wasn't even created during that time.

I was already at approx. booking number 20170615 when I created the app.

Do you think the refresh will still fix the situation?

[Delid4ve]

Did you try it?
How big is the list? Over 500 records? Is 20170514 the 500th record by any chance?
If so it’s because your hitting the limitation.
You may need to either filter the source first/inside or sortbycolumns in reverse order and use the first function instead of last

[Delid4ve]

Carlos shows you the sortbycolumns way up a few posts