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

A question about collections

Hey everyone! 

 

I hope everyone is having a grand old day of it. 

 

In all my apps I refer to my datasource directly (always a Sharepoint List). 

 

If instead when the app started I created a collection ("test",SPlistname) and referred to the collection in my app would that then overcome delegation issues? 

 

It is my understanding that a collection is a "virtual" table of your data source and as such is not subject to limits of 500 or 2000 records.

 

Am I completely off base here? 

 

Thanks

 

Ed

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User III
Super User III

Re: A question about collections

@milky_bar_may 

I understand your confusion on this topic because it took me awhile to figure it out for myself.

 

A collection does not have any limit to the amount of data it can hold.  However, when you load a collection it will only bring in as many rows as the delegation limit allows.

 

For example, this CLEARCOLLECT formula would only store 2,000 rows assuming you've increased the delegation limit to 2,000.

ClearCollect(colMyCollection, your_datasource_name)

  

But what if we did this and called CLEARCOLLECT twice in a row?  Now the collection would show 4,000 rows.  The data would be the 1st 2,000 rows of your_datasource_name stored twice.

ClearCollect(colMyCollection, your_datasource_name);
ClearCollect(colMyCollection, your_datasource_name);

 

Interesting, right?  Let me know what other questions you have.

 

Link To Collections Cookbook: I think you've seen this before:

https://powerusers.microsoft.com/t5/Community-App-Samples/Collections-Cookbook-50-Visual-Examples-am...

 

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

View solution in original post

Highlighted
Super User III
Super User III

Re: A question about collections

Hi @milky_bar_may 

The best approach is to use only delegatable functions in your app.  However, SharePoint does not have as many delegatable functions as SQL or CDS.  My trick for dealing with SharePoint lists that are >2000 items and < 4000 items. is to create two collections, one sorted ascending, say colasc and the other sorted descending, coldsc and them combining them in a third collection, colall using the Not() function combined with an "in" filter as follows:

 

 

Collect(
    colasc,
    Sort(
        SPlist,
        ID,
        Ascending
    )
);
Collect(
    coldsc,
    Sort(
        SPlist,
        ID,
        Descending
    )
);
Collect(
    colall,
    colasc,
    Filter(
        coldsc,
        Not(ID in colasc.ID)
    )
)

 

 

 

This formula removes the duplicates that normally would occur by combining two collections with overlapping data.  There are other ways to achieve collections for datasets > 4000 items if you search these boards.  Most of them depend on creating a collection by breaking down the list in chunks < 2k and then combining them. 

Annotation 2020-04-30 122138.png

View solution in original post

3 REPLIES 3
Highlighted
Super User III
Super User III

Re: A question about collections

@milky_bar_may 

I understand your confusion on this topic because it took me awhile to figure it out for myself.

 

A collection does not have any limit to the amount of data it can hold.  However, when you load a collection it will only bring in as many rows as the delegation limit allows.

 

For example, this CLEARCOLLECT formula would only store 2,000 rows assuming you've increased the delegation limit to 2,000.

ClearCollect(colMyCollection, your_datasource_name)

  

But what if we did this and called CLEARCOLLECT twice in a row?  Now the collection would show 4,000 rows.  The data would be the 1st 2,000 rows of your_datasource_name stored twice.

ClearCollect(colMyCollection, your_datasource_name);
ClearCollect(colMyCollection, your_datasource_name);

 

Interesting, right?  Let me know what other questions you have.

 

Link To Collections Cookbook: I think you've seen this before:

https://powerusers.microsoft.com/t5/Community-App-Samples/Collections-Cookbook-50-Visual-Examples-am...

 

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

View solution in original post

Highlighted
Super User III
Super User III

Re: A question about collections

Hi @milky_bar_may 

The best approach is to use only delegatable functions in your app.  However, SharePoint does not have as many delegatable functions as SQL or CDS.  My trick for dealing with SharePoint lists that are >2000 items and < 4000 items. is to create two collections, one sorted ascending, say colasc and the other sorted descending, coldsc and them combining them in a third collection, colall using the Not() function combined with an "in" filter as follows:

 

 

Collect(
    colasc,
    Sort(
        SPlist,
        ID,
        Ascending
    )
);
Collect(
    coldsc,
    Sort(
        SPlist,
        ID,
        Descending
    )
);
Collect(
    colall,
    colasc,
    Filter(
        coldsc,
        Not(ID in colasc.ID)
    )
)

 

 

 

This formula removes the duplicates that normally would occur by combining two collections with overlapping data.  There are other ways to achieve collections for datasets > 4000 items if you search these boards.  Most of them depend on creating a collection by breaking down the list in chunks < 2k and then combining them. 

Annotation 2020-04-30 122138.png

View solution in original post

Highlighted
Helper IV
Helper IV

Re: A question about collections

Thanks so much @Drrickryp and @mdevaney . I appreciate it !

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

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

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors
Users online (6,529)