cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Collection & Delegation Help - More than 2000 records

 

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
)

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Dual Super User III
Dual Super User III

Re: Collection & Delegation Help - More than 2000 records

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.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

View solution in original post

7 REPLIES 7
Highlighted
Dual Super User III
Dual Super User III

Re: Collection & Delegation Help - More than 2000 records

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.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
Highlighted
Helper I
Helper I

Re: Collection & Delegation Help - More than 2000 records

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
)

 

Highlighted
Super User III
Super User III

Re: Collection & Delegation Help - More than 2000 records

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.  

Highlighted
Dual Super User III
Dual Super User III

Re: Collection & Delegation Help - More than 2000 records

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.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
Highlighted
Helper I
Helper I

Re: Collection & Delegation Help - More than 2000 records

"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)

 

 

Highlighted
Dual Super User III
Dual Super User III

Re: Collection & Delegation Help - More than 2000 records

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.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

View solution in original post

Highlighted
Helper I
Helper I

Re: Collection & Delegation Help - More than 2000 records

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
)

 

 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Watch Now

Experience what’s next for Power Apps

See the latest Power Apps innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (5,692)