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

[jesenavaranjan]

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?

[timl]

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

[jesenavaranjan]

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

[jesenavaranjan]

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...

[timl]

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.