CollectAll function to collect the entire table or extend delegation to Collect and ClearCollect

[mokhawaja]

Hi,

it is really a big step forward to reduce the effect of the "500 records limit" by extending the delegation for ClearCollect and Collect functions, or create a new function to store the whole table or entity locally, which will drastically improve performance in our apps.

Regards,

Mohammad

[KroonOfficeSol]

I agree with mokhawaja, this should be a great addition.

I have a situation that I want to import more then 2000 records from a table in a sql server to a collection. In this case this table has a relationship to the table in which I want to add and update records. Not beïng able to save all records from the related table makes it impossible to use the PowerApps offline.

Please add a formula or option in the ClearCollect and Collect functional which overwrites the 2000 record limit and save all records to a collection. Maybe there must be a limit, but this should be far more then 2000.

[Anonymous]

It would be nice and I agree to some extent.  I understand the balance of performance over data volume coming back. It can take a long time and the wrong data set could kill the app waiting. limits help to control this.

Working off line or even locally with data can improve speed for the user so long as you can get all the data down.

I have got around the issue for now by adding in a button on the main form which the user can invoke when they want and update the local device data (iPhone). It takes a little while but at least they invoke this and can see the progress happening. Once complete they carry on using the app which only works on the collection created.

How did I do this.... I have broken down the returned data set to ensure it is always less than 500 and keep adding this to the same collection. For example lets consider accounts. They are all assigned to a sales person and it is unlikely (I checked before hand) that a user will have more than 500 accounts starting with A or B or C etc This is the scope that helps to ensure the number is less than the limit. Depending on the data the scope might be different but the aim is to reduce the result size.

For accounts I came up with: 

ClearCollect(CurrentUserAccounts,Filter('Accounts',cr964_acctasgn=Username.Text,StartsWith(cr964_acctdesc,"A")));
Set(NumberOfUserRecords,CountRows(CurrentUserAccounts));
Collect(CurrentUserAccounts,Filter('Accounts',cr964_acctasgn=Username.Text,StartsWith(cr964_acctdesc,"B")));
Set(NumberOfUserRecords,CountRows(CurrentUserAccounts));

etc all the way to Z and then you can also factor in 0 - 9

I then save the data to the device.

SaveData(CurrentUserAccounts,"CurrentUserAccounts");

This will ensure that whenever I start the app it can load (Loaddata) from the local data and not have to look at importing the data from the database unless the user wants too. Very much faster and works well on static data or data that does not change much.

I would welcome the ability to use CountRows on a datasource. This would at least help in setting an expectation to the end user on how many records are coming down.  

[mokhawaja]

Hi,

Check out @mrdang’s blog on the response method to return all the records you need to PowerApps using Flow

https://powerapps.microsoft.com/en-us/blog/return-an-array-from-flow-to-powerapps-response-method/

Note... it’s blazing fast!!!!

cheers

Mohammad