cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Collect row Limit 500 record ?

Hi

 

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 ?!?

 

Thank

Giancarlo

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted

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

Highlighted

Hi Meneghino

 

It's ok function...

 

Thanks for your help

 

Giancarlo

View solution in original post

14 REPLIES 14
Highlighted
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:
https://powerusers.microsoft.com/t5/PowerApps-Forum/Only-256-Entries-show-from-Sharepoint-list/m-p/1...

 

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.
Highlighted

Buongiorno Giancarlo,

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

https://powerapps.microsoft.com/en-us/tutorials/delegation-overview/

 

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.

Highlighted

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???

 

Grazie

 

Giancarlo

 

Highlighted

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:

 

Refresh('[Access].[Storico]');
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.

Highlighted

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

Giancarlo

 

 

 

 

 

 

Highlighted

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)

Thanks

 

CaptureAWAdatetype.JPG

Highlighted

Hi

 

Yes data source is Access Web App

and field is DATE

 

Thank

Highlighted

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?

 

CaptureDates2.JPG

Highlighted

Hi Meneghino,

 

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

 

Thank

Giancarlo

 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Community Highlights

Community Highlights

Check out the Power Platform Community Highlights

Top Solution Authors
Top Kudoed Authors
Users online (9,426)