Reply
Level: Powered On
Posts: 21
Registered: ‎11-09-2016
Accepted Solution

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


Accepted Solutions
Level 10
Posts: 1,490
Registered: ‎11-07-2016

Re: Collect row Limit 500 record ?

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

Level: Powered On
Posts: 21
Registered: ‎11-09-2016

Re: Collect row Limit 500 record ?

Hi Meneghino

 

It's ok function...

 

Thanks for your help

 

Giancarlo

View solution in original post


All Replies
Community Support Team
Posts: 2,948
Registered: ‎06-22-2016

Re: Collect row Limit 500 record ?

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

Level 10
Posts: 1,490
Registered: ‎11-07-2016

Re: Collect row Limit 500 record ?

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.

Level: Powered On
Posts: 21
Registered: ‎11-09-2016

Re: Collect row Limit 500 record ?

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

 

Level 10
Posts: 1,490
Registered: ‎11-07-2016

Re: Collect row Limit 500 record ?

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.

Level: Powered On
Posts: 21
Registered: ‎11-09-2016

Re: Collect row Limit 500 record ?

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

 

 

 

 

 

 

Level 10
Posts: 1,490
Registered: ‎11-07-2016

Re: Collect row Limit 500 record ?

[ Edited ]

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

Level: Powered On
Posts: 21
Registered: ‎11-09-2016

Re: Collect row Limit 500 record ?

Hi

 

Yes data source is Access Web App

and field is DATE

 

Thank

Highlighted
Level 10
Posts: 1,490
Registered: ‎11-07-2016

Re: Collect row Limit 500 record ?

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

Level: Powered On
Posts: 21
Registered: ‎11-09-2016

Re: Collect row Limit 500 record ?

Hi Meneghino,

 

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

 

Thank

Giancarlo