Answered

Collection & Delegation Help - More than 2000 records

(0) Share

Report

Posted on by marsdendd

Hi All,

I am hoping someone can help. I have a SharePoint list with 6400 items which I am trying to display in a Gallery.
It seems whichever way I slice it I am subject to the 2000 limit. I would rather not have to pull all the data in and
filter to aid performance.

I would like to collect all 6400 records (this will increase over time) from SharePoint to be able to use Filter
listing only the items that match (Current user).

One suggested workaround was to use a collection (see formula below). I used the following but again I am still stuck with 2000 items.

Before I can start to work on my filter i need all 6400 items.

What is wrong with my collection that prevents me from getting all 6400 items?

Is it because ID is not delegable?

If so then how can i workaround this limitation ?

//Collects all items in list and adds them to collection
Concurrent(
ClearCollect(
CollectionA,
Filter(
SiteRegistry,
ID < 2000
)
),
ClearCollect(
CollectionB,
Filter(
SiteRegistry,
ID >= 2000 And ID < 4000
)
),
ClearCollect(
CollectionC,
Filter(
SiteRegistry,
ID >= 4000 And ID < 6000
)
),
ClearCollect(
CollectionD,
Filter(
SiteRegistry,
ID >= 6000 And ID < 8000
)
),
ClearCollect(
CollectionE,
Filter(
SiteRegistry,
ID >= 8000 And ID < 10000
)
),
ClearCollect(
CollectionF,
Filter(
SiteRegistry,
ID >= 10000 And ID < 12000
)
),
ClearCollect(
CollectionG,
Filter(
SiteRegistry,
ID >= 12000 And ID < 14000
)
),
ClearCollect(
CollectionH,
Filter(
SiteRegistry,
ID >= 14000 And ID < 16000
)
)
);
ClearCollect(
colSiteRegistry,
CollectionA,
CollectionB,
CollectionC,
CollectionD,
CollectionE,
CollectionF,
CollectionG,
CollectionH
)

Categories:

Building Power Apps

I have the same question (0)

All responses (7)

Answers (1)

Sort by

Pstork1 69,653 Most Valuable Professional on at

Like
a
(2)

Report
Copy link

Link copied!
The point of delegation and Filter() is to get the number of items you need to retrieve BELOW the data row limit while you are on the server so you don't have to worry about the 500-2,000 item limit. If you read the documentation you will find that the only operator that works with ID is '='. So you can't use > or < than to pull the records into a local collection.

But instead of doing that try the following. Add this code to the OnStart property of the App

Set(CurrentUser,User().Email)

Then for your gallery apply a Filter in the Items property that checks the appropriate user field against Current User. For Example

Filter(datasource,'Created By'.Email = CurrentUser)

Assuming that you don't have more than 2,000 records created by an individual user that will return all the records to the Gallery for that particular user so you won't have to try to pull all the records into a local collection.

Was this reply helpful? Yes No
marsdendd 68 on at

Like
a
(0)

Report
Copy link

Link copied!
Thanks for your response.

All records are created by a service account so 'Created by' is a non starter.

I tried filtering using in (using collection)

Sort( Filter( colSiteRegistry, (PPCustPrimarySiteOwner.Email in vUserEmail) || (PPCustSecondarySiteOwner.Email in vUserEmail) ), Created, Ascending )

subject to the 2000 limit which is why i tried loading all 6400 records into the app

I also tried filtering using StartsWith (using sharepoint)

Sort( Filter( SiteRegistry, StartsWith(PPCustPrimarySiteOwner.Email, vUserEmail) || StartsWith(PPCustSecondarySiteOwner.Email, vUserEmail) ), Created, Ascending )

I also tried filtering using = (using sharepoint)

Sort( Filter( SiteRegistry, (PPCustPrimarySiteOwner.Email = vUserEmail) || (PPCustSecondarySiteOwner.Email = vUserEmail) ), Created, Ascending )

Was this reply helpful? Yes No
Drrickryp Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @marsdendd

If you are using the list for a lookup only, not adding, deleting or modifying any of the items, you can convert your SharePoint list to a Excel table and connect to it using the Static Excel connector. This allows you to import up to 15,000 items and you can use all PowerApps functions such as Search(), "in", etc to narrow the scope. Delegation is not an issue with the Static Excel connector. This method has only narrow usefulness but in the right situation it can be quite helpful.

Was this reply helpful? Yes No
Pstork1 69,653 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

The point is that you are going to have to filter on something that will get the number of records returned below the data row limit. The data row limit defaults to 500 but can be extended as high as 2,000. Created By was just an example because I know it exists in every list. You can use whatever field you like, as long as it will limit the records returned to fewer than the data row limit. Do either PPCustPrimarySiteOwner or PPCustSecondarySiteOwner meet that description?

Also, 'in' is not delegable in SharePoint. Startswith and '=' are.

Was this reply helpful? Yes No
marsdendd 68 on at

Like
a
(0)

Report
Copy link

Link copied!
"Do either PPCustPrimarySiteOwner or PPCustSecondarySiteOwner meet that description?"

They do, it is massively unlikely that these columns will every meet the 2000 limit HOWEVER they are Person columns and that is where I am struggling.

I have tried both = and StartsWith

= and StartsWith are subject to the 5000 list limit
Sort( Filter( SiteRegistry, (PPCustPrimarySiteOwner.Email = vUserEmail) || (PPCustSecondarySiteOwner.Email = vUserEmail) ), Created, Ascending )

in returns some results but is limited to 2000 (delegation warning)

Was this reply helpful? Yes No
Verified answer

Pstork1 69,653 Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!

The delegation warning is probably because you are using an Or. Try it with just one of those columns and see if the delegation warning goes away. Also make sure the columns are Indexed columns in SharePoint to deal with the 5,000 limit. I've done both StartsWith and = on a single indexed people column in a list that has over 5,000 items in it. It does work.

To do the Or you may have to split this into two galleries or cache it by adding the results to a local collection.

Was this reply helpful? Yes No
marsdendd 68 on at

Like
a
(0)

Report
Copy link

Link copied!
Got there!

Removing the Or and then Filtering the collections was the way to go.

Thank you for your help.

ClearCollect( colColumnPrimary, Filter( SiteRegistry, StartsWith( PPCustPrimarySiteOwner.Email, vUserEmail ) ) ); ClearCollect( colColumnSecondary, Filter( SiteRegistry, StartsWith( PPCustSecondarySiteOwner.Email, vUserEmail ) ) ); ClearCollect( colSiteRegistry, colColumnPrimary, colColumnSecondary )

Was this reply helpful? Yes No