Filter data from SharePoint -- to any item that is in a Collection

[Anonymous]

I'm looking for ideas to improve performance of a Collect call to Sharepoint in my PowerApp.
Here are the basics:

I have a SharePoint list full of Widgets and their details. The key column used for lookups is WidgetID, which originates from a barcode that are on my physical Widgets.

My app users have these Widgets with barcodes in front of them; they could have 1 or as many as 20. They can the WidgetID barcodes into a text input control, then press "Find Widgets" to look up details on all those Widgets.

The Find Widgets button uses the SPLIT function to turn the string of all WidgetIDs into a collection of WidgetIDs:

ClearCollect(WidgetIDList,Split(Input_WidgetBarcodes.Text)," ")

Then it loops through this list and makes calls to SharePoint to get widget details:
ForAll(WidgetIDList,Collect(myWidgetCollection,Filter(WidgetSharePointList,WidgetID=Result)))


This works OK, but it just isn't particularly quick, probably because I'm making anywhere from 1-20 calls to SharePoint.

Is there a smarter/faster way to do this in a single call?

Is there some way to write a Filter function that says "Return rows from Sharepoint where WidgetID the is in WidgetIDList? Almost like an "inner join" on the two data sources?

[Anonymous]

I'm looking for ideas to improve performance of a Collect call to Sharepoint in my PowerApp.
Here are the basics:

I have a SharePoint list full of Widgets and their details. The key column used for lookups is WidgetID, which originates from a barcode that are on my physical Widgets.

My app users have these Widgets with barcodes in front of them; they could have 1 or as many as 20. They can the WidgetID barcodes into a text input control, then press "Find Widgets" to look up details on all those Widgets.

The Find Widgets button uses the SPLIT function to turn the string of all WidgetIDs into a collection of WidgetIDs:

ClearCollect(WidgetIDList,Split(Input_WidgetBarcodes.Text)," ")

Then it loops through this list and makes calls to SharePoint to get widget details:
ForAll(WidgetIDList,Collect(myWidgetCollection,Filter(WidgetSharePointList,WidgetID=Result)))


This works OK, but it just isn't particularly quick, probably because I'm making anywhere from 1-20 calls to SharePoint.

Is there a smarter/faster way to do this in a single call?

Is there some way to write a Filter function that says "Return rows from Sharepoint where WidgetID the is in WidgetIDList? Almost like an "inner join" on the two data sources?

[RezaDorrani · Accepted solution]

Hi @Anonymous 

How many items do you have in WidgetSharePointList?

On App load you can load the entire list into a collection.

Then run your queries on the collection and not the SharePoint list.

This will ensure the round trip calls to the SharePoint list is avoided and significantly improve performance and optimize the code.

--------------------------------------------------------------------------------
If this post helps answer your question, please click on “Accept as Solution” to help other members find it more quickly. If you thought this post was helpful, please give it a Thumbs Up.

Thanks,
Reza Dorrani, MVP
YouTube
Twitter

[Anonymous]

Hi @RezaDorrani  -- good idea -- however the SharePoint list is several thousand rows, and growing. It is about 10k records now and we add another 1K records every week. Is bringing the entire list into a Collection still appropriate?

[RezaDorrani]

@Anonymous 

No it is not appropriate to load 10k records into a collection.

That's why I asked how many items 🙂

The In function is not a delegable operation with SharePoint. So the contains operation logic will also not work.

You may have to live with the number of calls for now.

Make sure you Index the column WidgetID in SharePoint.

--------------------------------------------------------------------------------
If this post helps answer your question, please click on “Accept as Solution” to help other members find it more quickly. If you thought this post was helpful, please give it a Thumbs Up.

Thanks,
Reza Dorrani, MVP
YouTube
Twitter

[Anonymous]

Thanks @RezaDorrani  , the column is indexed

Is there a practical/hard limit to the number of rows? Perhaps I could use a simple filter to pull in "some" of the list and look up WIdgets from there?

[RezaDorrani]

Hi @Anonymous 

A collection can hold as much data as you want. No limits.

Just chain Collect statements and keep collecting in batches of 2k records.

Problem is your App performance will degrade and will surely crash when used on mobile devices.

There is no limit to number of rows returned as long as your query is delegable to the data source.

Since your data source is SharePoint, indexing is required for lists > 5k items (best practice)

--------------------------------------------------------------------------------
If this post helps answer your question, please click on “Accept as Solution” to help other members find it more quickly. If you thought this post was helpful, please give it a Thumbs Up.

Thanks,
Reza Dorrani, MVP
YouTube
Twitter

[Anonymous]

Thanks @RezaDorrani, I think I will Collect in the most recent 4k or maybe 6k from SharePoint. I'm also using ShowColumns to leave behind all the SharePoint metadata. In quick testing, the performance is MUCH better.

Of course if someone has an "older" Widget then this will be problematic.

Anyways assuming there is no other solution for SP, I will call this solved! THanks again

[RezaDorrani]

@Anonymous 

Good luck with your App

--------------------------------------------------------------------------------
If this post helps answer your question, please click on “Accept as Solution” to help other members find it more quickly. If you thought this post was helpful, please give it a Thumbs Up.

Thanks,
Reza Dorrani, MVP
YouTube
Twitter

[Anonymous]

@RezaDorrani  Circling back on this, I actually haven't been able to implement a solution:

---

Just chain Collect statements and keep collecting in batches of 2k records.

Problem is your App performance will degrade and will surely crash when used on mobile devices.

There is no limit to number of rows returned as long as your query is delegable to the data source.

Since your data source is SharePoint, indexing is required for lists > 5k items (best practice)

---

Since the ID field in SharePoint isn't delegable, I can't find a way to chain together these Collects, or even just Collect the most recent 2000 items. Using FirstN(SharePointList,2000) or LastN(SharePointList,2000) seems to return the incorrect rows unless I sort first -- FirstN(SortByColumns(SharePointList,"SortingColumn",Descending) and this sorting on my large list just introduces new performance issues.

I can't find any post that has solved this for SharePoint. Only suggestion I've found is initiating a Flow that writes the ID number to a new text/number field (like ID2) so that you can delegate to that column instead and do a simple filter on the ID2, but incorporating flow is messy and adds more delay. I can't come up with a solution (other than not using SharePoint!)