cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
GHvdBerg
Level: Powered On

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
PowerApps Staff CarlosFigueira
PowerApps Staff

Re: Calculate a record number based on last record

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)
7 REPLIES 7
PowerApps Staff CarlosFigueira
PowerApps Staff

Re: Calculate a record number based on last record

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)
Super User
Super User

Re: Calculate a record number based on last record

Value(Last(ListName).ID)+1

Does this not work??

GHvdBerg
Level: Powered On

Re: Calculate a record number based on last record

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.

Super User
Super User

Re: Calculate a record number based on last record

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
Level: Powered On

Re: Calculate a record number based on last 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?

 

 

Highlighted
Super User
Super User

Re: Calculate a record number based on last record

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
Super User
Super User

Re: Calculate a record number based on last record

Carlos shows you the sortbycolumns way up a few posts

Helpful resources

Announcements
firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

SecondImage

Follow PowerApps on Twitter

Stay Up-to-Date by following PowerApps on Twitter

Top Community Contributors for July 2019

Top Community Contributors for July 2019

Let's thank our top community contributors

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

FifthImage

Dynamics 365 and Power Platform April 2019 Release notes

Features releasing from April 2019 through September 2019!

Users Online
Currently online: 46 members 4,011 guests
Please welcome our newest community members: