cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
GHvdBerg
Frequent Visitor

Calculate a record number based on last record

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.

1 ACCEPTED SOLUTION

Accepted Solutions
CarlosFigueira
Power Apps
Power Apps

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)

View solution in original post

7 REPLIES 7
CarlosFigueira
Power Apps
Power Apps

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)

View solution in original post

Delid4ve
Impactful Individual
Impactful Individual

Value(Last(ListName).ID)+1

Does this not work??

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
Impactful Individual
Impactful Individual

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

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
Impactful Individual
Impactful Individual

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
Impactful Individual
Impactful Individual

Carlos shows you the sortbycolumns way up a few posts

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Users online (2,487)