SharePoint and the 2000 item limit again

[Craig_Humphrey]

Hey People,

yes, this another post, moaning about the issues with the SharePoint connector, delegation and the 2000 item limit.

I've managed to hack together a work-around, but it's only good for upto 4000 items.

So I'll show what I've done and lets see if anyone can figure out a way to make it more performant, or to scale to more than 4000 items.

• I currently have just over 2100 items in my test list.
• I NEED Search(), or Filter() using "in".  StartsWith() is not an option.
• While there are potential (delegatable) pre-filters, this is not a good user experience.
• I've tried some of the iterative solutions for looping through and getting 500 (or up to 2000) items at a time, but
  • given the SharePoint ID field isn't delagatable for ">=" or ">" or "<" and
  • Calculated columns don't return a proper numeric type to Power Apps (and you can't put an SP index on them) - these just fail and
  • I don't really want the additional wrinkle of a Flow (Power Automate) populating a proper index (ID) field for me.
• Had to work around Distinct() only returning a single column table.

In my OnStart, I'm doing the following:

ClearCollect(firstCollection, 'SharePointList'),
ClearCollect(secondCollection, SortByColumns('SharePointList', "ID", Descending));

ClearCollect(finalCollection, firstCollection, secondCollection);
Clear(firstCollection);
Clear(secondCollection);
ClearCollect(finalFinalCollection, GroupBy(finalCollection, "ID", "grouped"));
Clear(finalCollection);
ClearCollect(newFinalCollection, DropColumns(AddColumns(finalFinalCollection, "Title", First(grouped).Title, "Organisation", First(grouped).Organisation, "Contact Category", First(grouped).'Contact Category', "Position", First(grouped).Position, "Direct Dial", First(grouped).'Direct Dial', "Extension", First(grouped).Extension, "Mobile Number", First(grouped).'Mobile Number', "ContactType", First(grouped).ContactType, "First Name", First(grouped).'First Name', "Last Name", First(grouped).'Last Name', "Full Name", First(grouped).'Full Name', "Section", First(grouped).Section, "Division", First(grouped).Division, "Department", First(grouped).Department, "Mailing Address", First(grouped).'Mailing Address', "Keywords", First(grouped).Keywords, "Language Spoken", First(grouped).'Language Spoken'), "grouped"));

This last line is the killer.  Essentially it's using GroupBy() to perform a distinct, then I promote all the columns I'm actually using up to the grouping (allows me to just use ThisItem.FieldName as though I hadn't grouped) and then removing the grouped child field, to keep memory usage down.

My gallery is then just based on newFinalCollection and when I OnSelect into an individual record, I'm using a First(Filter()) on the original data source, so that there's no complications with Submit(form).

It does mean I have to either initiate a data refresh of the collection after a Submit(), or use a button to refresh.

Refreshes are taking between 4 and 9 seconds, both on my desktop and on my phone (wifi).  Which is really getting too high.

If I could keep them under 5 seconds, I could probably publish this out.

Can anyone give me any pointers/tips on how I can improve this, or how they've managed to get around the 2000 item limit for SharePoint datasources, when they need some of those non-delegatable functions.

Thanks!

Craig

[WarrenBelz]

Hi @Craig_Humphrey ,

Yes I share your frustration, however a couple of workarounds.

Firstly I have a small WorkFlow in some of my apps (Designer 2013), which sets a second numeric hidden column to the ID number. This one is completely able to be filtered.

Secondly if you have a field anywhere that contains a small number of text possibilities (I have a Status that has three), you can collect all of it - as an example I just collected 4500 items with the below.

ClearCollect(Col1,Filter(YourList,Status="Pending"));
ClearCollect(Col2,Filter(YourList,Status="In Progress"));
ClearCollect(Col3,Filter(YourList,Status="Completed"));
ClearCollect(colAll,Col1,Col2,Col3)

As you would know, once you get a collection, all the Delegation issue disappear.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[Craig_Humphrey]

Thanks Warren.

It's pre-existing client data, so I'm loath to add another column and have a WF/Flow updating it, but I might just have to...

I still don't like the hoops you have jump through to loop through batches into Collections, there really should be a better way!

I like your second idea.  I've got a couple of fields that use lookups, so could split it up using those, the only problem is, they can also add additional values...  Though in this case they have talked about locking it down...

Arguably, the SharePoint connector should do all the batching and complying with SP throttling limits, so the Power App (or Flow) doesn't have to worry about it.  It's one thing to have slow data sources, it's quite another to have to write slow janky code for each situation...

[WarrenBelz]

Hi @Craig_Humphrey ,

I agree with all of the below.

[v-siky-msft]

Hi @Craig_Humphrey ,

Currently, besides function optimization, the main way to avoid delegation error is to save SP lists to collections in batches, just like your resolution, there is no better way I think.

Optimize function could be a more possible way, please share your delegable code? We can talk about whether other formulas can achieve the same effect.

In addition, How do you refresh the Collections? Trigger these ClearCollect function again, right? What is the duration of each function? I suggest you use 'Monitor tool' to get each function's Duration (set each function to button onSelect to trigger separately), then you can target these functions that take a long time.

Sik

[Craig_Humphrey]

Hey Sik,

thanks for responding.

So the app is essentially a phonebook of contacts.  So the key thing is to be able to search across a bunch of fields:
SortByColumns(Search(newFinalCollection, TextSearchBox1.Text, "Title", "Organisation", "Position", "Contact Category", "First Name", "Last Name", "Full Name", "Section", "Division", "Department", "Mailing Address", "Location", "Call Sign", "Language Spoken"), "Title", If(SortDescending1, Descending, Ascending))

These fields are a mixture of single-line and multi-line text.
When I was querying directly, I was having to use Filter() and StartsWith() which is non-ideal.

To create the local collection I do the following:
ClearCollect(firstCollection, 'Contact Phonebook List'),
ClearCollect(secondCollection, SortByColumns('Contact Phonebook List', "ID", Descending));
ClearCollect(finalCollection, firstCollection, secondCollection);
Clear(firstCollection);
Clear(secondCollection);
ClearCollect(finalFinalCollection, GroupBy(finalCollection, "ID", "grouped"));
Clear(finalCollection);
ClearCollect(newFinalCollection, DropColumns(AddColumns(finalFinalCollection, "Title", First(grouped).'Title (Title1)', "Organisation", First(grouped).Organisation, "Contact Category", First(grouped).'Contact Category', "Position", First(grouped).Position, "Direct Dial", First(grouped).'Direct Dial', "Extension", First(grouped).Extension, "Mobile Number", First(grouped).'Mobile Number', "ContactType", First(grouped).ContactType, "First Name", First(grouped).'First Name', "Last Name", First(grouped).'Last Name', "Full Name", First(grouped).'Full Name', "Section", First(grouped).Section, "Division", First(grouped).Division, "Department", First(grouped).Department, "Mailing Address", First(grouped).'Mailing Address', "Location", First(grouped).'Location', "Call Sign", First(grouped).'Call Sign', "Keywords", First(grouped).Keywords, "Language Spoken", First(grouped).'Language Spoken'), "grouped"));

The first two ClearCollects are part of a Concurrent() - which seems to confuse Monitor, so I'll split them out an do some test timings, but over-all, it takes between 4 and 9 seconds.  If I can get it consistently under 5 seconds, I can probably give it to the client.

And yeah, each time the user does a new/edit, I run a refresh.