cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sajarac
Level 8

Excel Incremental value

Hello there,

 

Probably this is already solved, but I cannot find a simple and easy way to implement.

 

Let's say I have an excel file, and the first row is the Job ID, "incremental number" that start in 4000

 

When I need job is received will be 4001, 4002, etc.

 

But I want that my users will be able to create a new job and the excel file put the next available and consecutive number in that form.

 

is this possible?

 

Thanks in advance for any response.

 

Regards

 

Sajarac

1 ACCEPTED SOLUTION

Accepted Solutions
mr-dang
Level 10

Re: Excel Incremental value

Yes this is doable. Previously you could use Max(), but it cannot delegate, meaning that PowerApps can only handle 500 records at a time. So the only solution to identify the highest number is to pull the last record. Here's what you need to do when saving a record to the datasource:

 

Patch(datasource,Defaults(datasource),
	{JobID: First(Sort(datasource,JobID,Descending)).JobID+1,
		[other fields go here]
	}
)

 

This means, "Save a record to a new row of the datasource. Make the JobID equal to the last JobID plus one."

The last record is identified by sorting the datasource by the JobID column, but in descending order. Then take the First record, and specifically its JobID field to add one to it.

 

This will only work if JobID is a numerical Value. If it is treated as Text in Excel, then the Sort() might not work correctly. If JobID is treated as Text, you might be able to save the following to JobID, but I don't think it would delegate:

First(Sort(datasource,Value(JobID),Descending)).JobID+1

 

Microsoft Employee
@8bitclassroom

View solution in original post

2 REPLIES 2
mr-dang
Level 10

Re: Excel Incremental value

Yes this is doable. Previously you could use Max(), but it cannot delegate, meaning that PowerApps can only handle 500 records at a time. So the only solution to identify the highest number is to pull the last record. Here's what you need to do when saving a record to the datasource:

 

Patch(datasource,Defaults(datasource),
	{JobID: First(Sort(datasource,JobID,Descending)).JobID+1,
		[other fields go here]
	}
)

 

This means, "Save a record to a new row of the datasource. Make the JobID equal to the last JobID plus one."

The last record is identified by sorting the datasource by the JobID column, but in descending order. Then take the First record, and specifically its JobID field to add one to it.

 

This will only work if JobID is a numerical Value. If it is treated as Text in Excel, then the Sort() might not work correctly. If JobID is treated as Text, you might be able to save the following to JobID, but I don't think it would delegate:

First(Sort(datasource,Value(JobID),Descending)).JobID+1

 

Microsoft Employee
@8bitclassroom

View solution in original post

sajarac
Level 8

Re: Excel Incremental value

Hi many thanks, for some reason my powerapp form is not saving the new records into my excel file.

 

But thanks anyway for your prompt reply, I will solve the save issue then I will work with this solution

 

Regards

 

 

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

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

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors
Users Online
Currently online: 341 members 6,596 guests
Please welcome our newest community members: