SQL Server not returning more than 500 items

[Anonymous]

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?

[NEPatton]

@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]

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