cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper V
Helper V

My app is only pulling the first 2000 records in some instances

My SQL Database has 8000 records

And could have up to 20,000

In some cases, it's going to 2000 and stopping there

How do I fix this?

4 REPLIES 4
Super User III
Super User III

Hi @jesenavaranjan 

PowerApps limits the number of rows that it retrieves for performance reasons. A good place to get an overview on this is to read the 'Delegation' section of the documentation.

https://docs.microsoft.com/en-gb/powerapps/maker/canvas-apps/delegation-overview#delegable-data-sour...

 

If you could post the formula that you're using to pull in your records, someone here can maybe able to help adapt it to remove this problem.

 

In many cases, it's not really necessary to pull in such large amounts of data because practically, 2000 records is too much to display on a screen. So it might be useful to understand why you want to pull all 8000 records into your app. Is it because (for example) you're using a combobox control and only the first 2000 records appear there?

 

Another good thread to look at is the code that @ericonline posted here yesterday.

https://powerusers.microsoft.com/t5/General-Discussion/Pulling-in-large-ish-SQL-tables/td-p/243777

The formula that is not working is this one: Once a new record is created, it is taking the ID and passing it through the form to show the form in edit mode - as further info is then used, this formula is as part of my submit


Set(Identi,If(RisksForm.Mode=FormMode.New,
 First(
    SortByColumns('[dbo].[tbRisk]',
        "stDate",Descending)
        ).RiskID,Identi))


But it is always showing the 2000 record

 

Jese

Another formula I am stuck with is:

 


SortByColumns
(Filter('[dbo].[vwMilestoneSummary]',SearchInput.Text in Title, CodeSearch.Text in Code || Value(CodeSearch.Text) = MilestoneID,
 DateDiff(from.SelectedDate,ForecastDate) >= 0,
   DateDiff(ForecastDate,to.SelectedDate) >= 0)

 

 

 

As DateDiff is not a delegable function...

Hi Jese,

For your first question (if I understand you correctly), if you've just added a record into '[dbo].[tbRisk]' through RisksForm, you can retrieve the Identi value of the record that you just inserted with syntax that looks like this:

 

RisksForm.LastSubmit.Identi

 

For your second question, I suspect you're using DateDiff here to overcome the SQL date filtering bug.

 

https://powerusers.microsoft.com/t5/General-Discussion/Filtering-on-prem-SQL-data-source-by-date/td-...

 

If this is the case, there's an alternative workaround to this. If it's possible for you to modify your '[dbo].[vwMilestoneSummary]' view by adding an output column of ForecastDate which has been cast to the DateTimeOffset data type, you should be able to filter directly against that without the delegation warning.

 

 

Helpful resources

Announcements
secondImage

Experience what’s next for Power Apps

Join us for an in-depth look at the new Power Apps features and capabilities at the free Microsoft Business Applications Launch Event.

Power Apps Community Call

Power Apps Community Call: February

Did you miss the call? Check out the Power Apps Community Call here.

New Power Super Users

Congratulations!

We are excited to announce the Power Apps Super Users!

New Badges

New Solution Badges!

Check out our new profile badges recognizing authored solutions!

Top Kudoed Authors
Users online (56,684)