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
Power Platform Call June 2022 768x460.png

Power Platform Community Call

Join us for the next call on August 17, 2022 at 8am PDT.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Users online (3,122)