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?

[Anonymous]

Sounds as a bug to me. Since filtering is fully delegable on SQL according to the documentation (and you do not see the blue dot) it should retreive all records. I do not have a PowerApp on SQL at the moment, so no means to test it. My suggestion would be to register a support ticket.

[Anonymous]

Hi @Anonymous

As far as I understand PowerApps will never return more than 500 records from any source to increase performance. 

What we do to browse large databases with more than 500 records we use delegatable functions to reduce the number of records that the datasource returns to bellow 500.

If your datasource is more than 500 records and you filter it using delegation but the filtered list is still over 500 records, only first 500 records will be returned. If you filter it, but there is no delegation, only first 500 records will be filtered in the datasource.

From your formula,

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

First it Filters DatabaseTable 

If it is SQL Server then this forumla will be delegated and it will Filter on all records of your database (1211 rows as you say).

Now if the condition 

column1 = 99 || column1 = 8

returns more than 500 records in the datasource, only the first 500 records will go back to PowerApps.

If that is the case then it is working as expectred and there is no bug.

If what you are saying is that you use this forumla and it filters only on first 500 records of the datasource and then returns the records which are matched, then it is a bug. 

[Anonymous]

Hi @Anonymous

The condition bellow should return 1040 items that will be stored in a Collection.

column1 = 99 || column1 = 8

 From there, on each drop down items are being filtered from this Collection and on each drop down no more than 10-20 items are being shown at a time.

Even if it is as you say that no more than 500 items are being returned from any source, why has it been working up until yesterday? As well as, why it doesn't work to filter out of the 1211 the 10-20 items I need on one of the drop downs?

For example, on a drop down the formula is dependent on the selection of the other 2 drop downs, and now I have added the above condition, so it looks like:

SortByColumns(Filter(SQLTable, Region = ddRegion.Selected.Result && Area = ddArea.Selected.Result && (column1 = 99 ||  column1 = 8)), "Branch")

- this on a particular case when ddRegion = 1 and ddArea = 5 should return 10 items to populate the drop down. However, at the moment only returns 5 items (the items which are part of the first 500 items from the SQL table)

This is not a new app that I've created yesterday, the app has been runing correctly for weeks.

[Anonymous]

I am not sure if I understand but as far as I understand you do in "onStart" function the ClearCollect, we have to take into account that this function doesn't support delegation so if Filter returns 1040 rows the ClearCollect will just store the first 500 (I am not really sure about this at this moment) if I was you I would use the Filter function in each dropdown for filter the data directly from the database and not from the Collection.

Of course you will do more calls to database but doing this you will always get less than 500 rows in each dropdown and getting the rows that you want.

Maybe the cause of the problem is that the table where you are getting the rows has increased and your Filter method returned less that 500 rows before.

Regards,

[NEPatton · Accepted solution]

Running on about 85-90% confidence in this answer...but....

The difference in your two implementations is the collection.  When you Collect, Powerapps will cap you at 500 records per query to your SQL table.  I have had no problems getting more than 500 records when I link my SQL datasource to an object (like a dropdown menu), but collections are a different beast.

I have seen some posts of people who try to work around this problem in roughly the following manner...

Let's say you have a SQL table with an ID field as primary key auto incrementing...your results will always be from the top of the table and as far down as 500 records will get you, right?   So you can try things like...

ClearCollect(mycol, Filter(mysql, "my filters")); Collect(mycol, Filter(mysql, "myfilters" && ID > Max(mycol.ID)))

This example would pull up to 1000 records into the collection.  Does that make sense?

[Anonymous]

If the condition returns 1040 items only first 500 will be stored in the collection. What you are doing right now is bringing back only first 500 records and then adding drop down control on them to filter further. To make the filtering work correctly, you have to put your dropdown controls to filter that data source and not the collection to get correct results sent back to PowerApps. After your data source is filtered you can then put it in collection if you want.

It could not have worked correctly before because the 500 limit was always there. Maybe you just missed that it was not taking all the data, or there were new records added that pushed it over 500 limit.

[NEPatton]

The 500 limit doesn't apply everywhere.

You can return more than 500 rows of data from a SQL connection if you link the connection directly to an object without pulling it into a collection first. 

[Meneghino · Accepted solution]

Hi @Anonymous

Please try this:

ClearCollect(CollectionName, Filter(DatabaseTable, column1 = 99));  Collect(CollectionName, Filter(DatabaseTable, column1 = 8))

This should get you more than 500 items but less than 1,000.  If you can further segment your table to segments of below 500 records each then you can get all the result in your collection.

[Anonymous]

Thank you all for the explanations...seems that after all the time I've been developing apps I still can't get my head around these limits... I don't have a logical explanation for how it used to work, but I'll take it as it is now and find a workaround it.

@Meneghino, so that means that the collection is capable of holding more than 500 items as long as they are not being created at once from an external source? I should probably be able to find a way to get my data this way.