Unanswered

Filter and Search not delegating with SQL connection

(0) Share

Report

Posted on by StantonD

I've got a large SQL table (over 16 million records) that I need to pull information from based on some selections in my app.

I've tried both Search and Filter. It does not give me the delegation warning but when I look at the call the app makes it is only getting the top 2000.

Here is what I tried for Search

Search(
 '[MMD].[PF_Read_DM_RNTL_PI19_MAIN_IN_SERVICE_SIZE_PREM]',
 compareUseSelection[@Value],
 "ORDERED_PROD"
 )

For Filter I tried with both = and in with the same results.

Filter(
 '[MMD].[PF_Read_DM_RNTL_PI19_MAIN_IN_SERVICE_SIZE_PREM]',
 compareUseSelection[@Value] = ORDERED_PROD
 )

Is there something I am missing or a different way I should be trying to pull the data?

Categories:

Building Power Apps

I have the same question (0)

All responses (11)

Answers (1)

eka24 20,923 on at

Like (0)

Report

Some clarification, is compareselection a column in the Table?

Are using a Textbox for the search formula because I see ORDERED_PROD in both formulas?

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

Was this reply helpful? Yes No
RandyHayes 76,297 Super User 2024 Season 1 on at

Like (1)

Report

@StantonD

PowerApps has a limit of 2000 records. You cannot gather more than that at any one function call.

Record limits impact non-delegable actions, but are different from delegation - they are simply the limit.

If you need to pull more than 2000 records, then you need to look at a way that you can perform multiple calls using some delegable partitioning and collect the results into a collection. Collections do not have a limit of records, but filling then up can only be done 2000 records at a time.

I hope this is helpful for you.

Was this reply helpful? Yes No
timl 36,383 Super User 2025 Season 2 on at

Like (0)

Report

Hi @StantonD

Can you confirm how you're using the result of your Search and Filter functions?

As @RandyHayes correctly indicates, if you've wrapped the calls to Search and Filter inside a ClearCollect or Collect, that combination will result in a SELECT TOP 2000 in a trace.

If you use something like a gallery control, Power Apps will retrieve the next 2000 rows when the user reaches the bottom of the control.

Was this reply helpful? Yes No
StantonD 30 on at

Like (0)

Report

I don't understand why there would be any distinction between data sources/methods that support delegation and those that don't if that is the case.

For example if I used CountA which it says can't be delegated and I have my "Data row limit for non-delegable queries" set to 2000 then it would return a max of 2000 rows.

If I use = which it can delegate it would still only return a max of 2000 rows?

Was this reply helpful? Yes No
StantonD 30 on at

Like (0)

Report

I have the search inside a ForAll. compareUseSelection is a collection and [@Value] is the current text value. ORDERED_PROD is the column in my SQL table is is searching/filtering.

Was this reply helpful? Yes No
eka24 20,923 on at

Like (0)

Report

Watch this tutorial from Daniel Christian:

https://youtu.be/IvTiAanB3Cs

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

Was this reply helpful? Yes No
RandyHayes 76,297 Super User 2024 Season 1 on at

Like (0)

Report

@StantonD

Yes, again, record limits are not delegation. Delegation has to do with the ability of the datasource to be able to conduct your filter on all records and return results (results still limited to 2000).

If you cannot delegate criteria, then PowerApps has to pull ALL of the records and then perform the criteria. In that case, the first part is the problem...it can only pull 2000 records to then perform the criteria on. That means results will always be 2000 or less and may not be accurate.

If you have a 1 ton truck and you go to the supply house to get 2 tons of boxes...you have to make two trips. If you need only blue boxes, you can ask the supply house to give you only blue boxes. If there is more than 1 ton of blue boxes, you will have to make two trips. If you ask the supply house to give you boxes that have red pens inside of them, they will tell you they cannot find those for you...you will then have to make two trips to bring back all of the boxes, then they YOU will have to open each one to find the ones with red pens. If you only make one trip, you might find boxes with red pens, but there may still be more back at the supply house because you did not bring them all back.

That kind of sums up the record limit and delegation issue into an example.

Was this reply helpful? Yes No
StantonD 30 on at

Like (0)

Report

I think I understand what you are saying.

Without delegation it pulls the first 2000 records from the datasource then performs some action on the result.

With delegation it performs some action at the datasource then pulls the first 2000 of that result.

So the only way to filter a dataset larger than that is to loop over the table 2000 records at a time?

Was this reply helpful? Yes No
Verified answer

RandyHayes 76,297 Super User 2024 Season 1 on at

Like (1)

Report

@StantonD

Yes...you got it!

So, the trick is to find a way to determine a "partition" for your data.

This is just and example, but let's say that you have invoices in your data your data spans 2 years. You have a total of 3000 invoices for one customer over the two years, then doing something like this is a workaround:

Collect(customerInvoices, Filter(dataSource, Year=2019, CustomerID=1), Filter(dataSource, Year=2020, CustomerID=1))

The resulting collection will have 3000 records for customerID 1

This is just and example. There are lots of other samples and workarounds for it.

However...when confronted with this, my question usually is - why would you want to return more than 2000 records? PowerApps is an App as opposed to an Application. Apps usually are designed around the concept of "keep it simple". Displaying over 2000 records seems like something that a user would not necessary sit and look through all of them. So, in other words, always consider the user perspective in correlation to the data design.

Was this reply helpful? Yes No
StantonD 30 on at

Like (0)

Report

Thanks for the help.

I think ultimately I should have been looking at doing the work on the SQL server as a view since this data set is so large. Then I can pull in the high level filtering from the view as well as a count of those records if they need me to pull the detailed info in partitions.

Was this reply helpful? Yes No