Collect row Limit 500 record ?

[giancarlo]

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

[v-yamao-msft]

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

[Meneghino]

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.

[giancarlo]

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

[Meneghino]

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.

[giancarlo]

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

[Meneghino]

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

[giancarlo]

Hi

Yes data source is Access Web App

and field is DATE

Thank

[Meneghino]

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?

[giancarlo]

Hi Meneghino,

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

Thank

Giancarlo