11-16-2016 07:25 AM
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 ?!?
Solved! Go to Solution.
11-17-2016 09:44 AM
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.
11-16-2016 11:22 PM
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:
11-16-2016 11:42 PM
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.
11-17-2016 02:40 AM
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???
11-17-2016 04:44 AM
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.
11-17-2016 08:24 AM
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??
11-17-2016 08:47 AM - edited 11-17-2016 08:48 AM
OK, that can also be easily resolved.
There are at least 3 alternative solutions, so to choose which I need to know two things:
- Can you confirm that you are using an Access Web App as a data source?
- If yes, then what Subtype of date column is DataUltVendita? Is it "Date" or "Date withTime"? (see below)
11-17-2016 09:09 AM
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?