Sharepoint Collect data to collection issue

[pcakhilnadh]

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

[BCLS776]

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

[pcakhilnadh]

@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]

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

[pcakhilnadh]

@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?

[BCLS776]

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?:

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. 

[pcakhilnadh]

@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?

[BCLS776 · Accepted solution]

---

@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.