Showing results for 
Search instead for 
Did you mean: 
Helper I
Helper I

Collect row Limit 500 record ?



I have connect sql with gateway, and i have create a collect with my one table.


I have see that append only 500 records .. it's normal ?!?





Accepted Solutions
Community Champion
Community Champion

PS The reason you got a blue circle warning is that if you apply a function (like Year) to the column you are filtering, there is a risk that the delegation will not work so you will filter only over the first 500 records of the source and not over all of the source. By comparing the field directly (i.e. without applying functions) to fixed values then delegation should work and you can have many && to filter different fields and ranges.

Be careful because in any case only the first 500 records that satisfy the condition will be returned.  That is why you need a few collection steps if you have more than 500 records to collect.

Anyway, please let me know if this works in the same way for you.

View solution in original post

Hi Meneghino


It's ok function...


Thanks for your help



View solution in original post

Community Support
Community Support

Hi Giancarlo,


I test this issue with an Excel file which has nearly 800 records, I format the file as a table and save it in OneDrive, then I create an App from data with OneDrive connection using this table. However, when I add a Text box in the App and set its Text property to CountRows(data source), I know that only 500 items are displayed on the App.


I have seen a related thread which says that data source 500 items limit is going to be released in January/February 2017, I guess that SQL server could be the same. The thread link for a reference:


Best regards,
Mabel Mao

Community Support Team _ Mabel Mao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Buongiorno Giancarlo,

yes it is normal but this can be resolved by delegation:


In other words, use the filters in the PowerApp itself to get the 500 records that you need.


Please let me know if you need more specific help.

Buongiorno Meneghino


I have this event that create collect

Refresh('[Access].[Storico]');;Clear(storico1) ;;Collect(storico1;(Filter('[Access].[Storico]'; idagentestoricox1="90010" )))


Table Storico have 40.000 record, and with filter have 1200 record..


you can please, give me an example for solution???






Community Champion
Community Champion

Giancarlo, the problem is clear.  The filter should return only the first 500 records found ot of all the 1,200.


Here is a proposed type of solution, assuming that you have another column, say "anno" which take values 2013, 2014, 2015.

If in each of the categories, the returned records are <=500 then you are ok with this:


ClearCollect(storico1, Filter('[Access].[Storico]', idagentestoricox1="90010" && anno=2013));
Collect(storico1, Filter('[Access].[Storico]', idagentestoricox1="90010" && anno=2014));
Collect(storico1, Filter('[Access].[Storico]', idagentestoricox1="90010" && anno=2015))

This is not elegant, but will work.  Also, please note that you can combine Clear and Collect with a function called ClearCollect.

Salve Meneghino


Thank for your example.... i have tested this solution but i have notify error (icon blue)


Refresh('[Access].[Storico]');;Clear(storico1) ;;Collect(storico1;(Filter('[Access].[Storico]'; idagentestoricox1="90010" && Year(DataUltVendita)=2016 )))


field dataultvendita is type DATE 


The sintax for the year is correct??


Thank You








Community Champion
Community Champion

OK, that can also be easily resolved.

There are at least 3 alternative solutions, so to choose which I need to know two things:

  1. Can you confirm that you are using an Access Web App as a data source?
  2. If yes, then what Subtype of date column is DataUltVendita? Is it "Date" or "Date withTime"? (see below)






Yes data source is Access Web App

and field is DATE



Community Champion
Community Champion

OK, I also use Access Web App a lot.

I have seen a bug, that fields of sub-type DATE are not seen by PowerApps (this problem may be resolved in future but I am not sure)

So there are two things that you can do:

1) Change the field sub-type to Date with Time (nothing happens to the data), OR

2) Add a calculated column to your table, which contains the date as an integer (see below)


My preferred solution is the second, but we can work with either.


The next step depends on what you choose.  Which one?



Hi Meneghino,


I prefered the 1 solution... change type field in datetime..





Helpful resources

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!


Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

Top Solution Authors
Top Kudoed Authors
Users online (72,080)