Collection & Delegation Help - More than 2000 records

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

[Pstork1]

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.

[marsdendd]

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
)

[Drrickryp]

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.  

[Pstork1]

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.

[marsdendd]

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

[Pstork1 · Accepted solution]

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.

[marsdendd]

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
)