cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

SQL Server not returning more than 500 items

Hi,

 

I have an app which gets its data from an on-prem SQL database connected through gateway.

The app has been running for some weeks and today it has stopped returning all the rows from the SQL table.

 

For efficiency, I have the OnStart property set to:

 

ClearCollect(CollectionName, Filter(DatabaseTable, column1 = 99 || column1 = 8))

Then I am using the created collection to filter items on drop downs.

 

The SQL table has currently 1211 rows and when filtered it should get down to 1040, then each filter on the drop downs reduces the numbers to 10s - 20s. 

Today, it has stopped returning more than 500 items from the SQL table, having items missing from the drop downs.

 

I have done some testing and if I use the SQL table directly in the drop down filters (with the existing filter on the drop downs) it returns the correct number of items back. But, if I add the "column1 = 99 || column1 = 8" in the filter it doesn't return the values anymore. 

 

Plus that I don't get the blue dot in the OnStart function, nor in the Filter directly on the drop downs, but I am absolutely sure I only get 500 items in the Collection.

 

Is there anyone else experiencing issues with SQL tables? Any reason why I am having these problems all of a sudden?

11 REPLIES 11

@Anonymous

 

Yeah - we both showed solutions that can get more than 500 records into a collection.  The 500 limit isn't a limit on how much a collection can hold.  The 500 limit is a limit on how much a single "Collect" or "Clear Collect" statement can return at once.  So, as @Meneghino is showing you above -- the trick then is to use "Collect" more than once.  You have to find a way to perform your query in sets, and we've given you a few examples of how to do that.

 

I hope you figure it out!

Anonymous
Not applicable

@NEPatton thanks for that 🙂 it makes much more sense now! 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Microsoft Ignite 768x460.png

Find your focus

Explore the latest tools,training sessions,technical expertise, networking and more.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Users online (2,033)