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
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Power Apps June 2021

June Power Apps Community Call

Don't miss the call this month on June 16th - 8a PDT

Top Solution Authors
Top Kudoed Authors
Users online (43,684)