cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pcakhilnadh
Helper V
Helper V

Sharepoint Collect data to collection issue

I have a SharePoint list MySpList.  I am collecting this list to a collection on start of app

 

ClearCollect(MyCol,MySpList)

 

Issue

  • MySpList has 560 rows but the collection contains only 100rows.

Questions

  1. What is the limit of "ClearCollect" to collect rows in PowerApp? IF so what is an alternative approach
  2. I have a lot of lookups happening in this SPlist.  API response time as shown in Monitor tool is sometime around 5000ms. To reduce the API response time in getting the information I've decided to use collection. Does Lookup in the collection is also consume same API response time? If so what is alternative approach?

 

Solutions looking for

Looking for an effective approach to fetch complete rows from sharepoint list to a collection in powerapp and lookup to columns in very less amount of time

1 ACCEPTED SOLUTION

Accepted Solutions


@pcakhilnadh wrote:

@BCLS776 

I had set it as 100, Thanks.

 

Also lookup(SharepointList) and lookup(CollectionVariable) which is better according to time complexity? which give faster response?


A lookup on an internal collection will always be faster since there is no network request involved. However, this should be balanced with the additional complexity/maintainability of the code and the time it takes to build the collection in the first place. Unless your code is unacceptably slow or there's no easy alternative, save yourself the trouble of an extra collection.

_________________________________________________________________________________________
Help the community help more users by choosing to "Accept as Solution" if this post met your needs. If you liked the post and want to show some appreciation, please give it a Thumbs Up.

View solution in original post

7 REPLIES 7
BCLS776
Super User
Super User

ClearCollect is capable of retrieving all rows up to your currently-set row limit (500 by default, can be increased to 2000) per query. Your request may be pulling in such a large quantity of data that it is timing out instead. What function are you using to determine the number of rows. Have you tried using a ForAll+Collect to specifically name only the columns/fields you need in your collection. Loading all columns by default can be data-intensive.

 

Bryan

_________________________________________________________________________________________
Help the community help more users by choosing to "Accept as Solution" if this post met your needs. If you liked the post and want to show some appreciation, please give it a Thumbs Up.

@BCLS776 

CountRows(MyCol) -> returns 100

CountRows(MySpList) -> returns 560

 

Lookup(MySpList ,label_id="label_id1").label  -> Displays label as expected

Lookup(MyCol,label_id="label_id1").label  -> Displays blank which is not expected

BCLS776
Super User
Super User

I haven't run into this one, but I am happy to help you troubleshoot. Might be worth starting a Support ticket and getting that moving too.

 

Let's try this and see if you can load a couple of columns completely and successfully. Examine your CountRows() label afterwards and let us know what you see:

 

Clear(myCol);
ForAll(mySPList As aRecord,
    Collect(myCol,
    {
        Title: aRecord.Title,
        ID: aRecord.ID
    }
    )
);

 

Bryan

_________________________________________________________________________________________
Help the community help more users by choosing to "Accept as Solution" if this post met your needs. If you liked the post and want to show some appreciation, please give it a Thumbs Up.

@BCLS776  No this doesn't solve either. I'm still getting 100 in CountROws(myCol) after For looping also.

 

However API response time `getRows` is 223ms where as in ClearCollect it were 467ms.   Do you think forloop is effetive or API response time depends on my ntwork speed at that time. How do you account to it?

The shorter API response time is likely because we requested less data than when you bring in the full table. Or, it can be network/service variability too. ForAll() statements are fairly intensive operations.

 

Can you double-check the row limit for your app, available under File-Settings?:

BCLS776_0-1633441030788.png

If it isn't already, please set it to 2000.

 

Also, anything unusual about your SP List? Did you add many columns? Are there any large, nested records or unusual fields? I've seen some connectors hit a hard stop at 100 rows, but not on a typical SP List. 

 

 

 

_________________________________________________________________________________________
Help the community help more users by choosing to "Accept as Solution" if this post met your needs. If you liked the post and want to show some appreciation, please give it a Thumbs Up.

@BCLS776 

I had set it as 100, Thanks.

 

Also lookup(SharepointList) and lookup(CollectionVariable) which is better according to time complexity? which give faster response?


@pcakhilnadh wrote:

@BCLS776 

I had set it as 100, Thanks.

 

Also lookup(SharepointList) and lookup(CollectionVariable) which is better according to time complexity? which give faster response?


A lookup on an internal collection will always be faster since there is no network request involved. However, this should be balanced with the additional complexity/maintainability of the code and the time it takes to build the collection in the first place. Unless your code is unacceptably slow or there's no easy alternative, save yourself the trouble of an extra collection.

_________________________________________________________________________________________
Help the community help more users by choosing to "Accept as Solution" if this post met your needs. If you liked the post and want to show some appreciation, please give it a Thumbs Up.

Helpful resources

Announcements
Ignite 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Users online (1,827)