Wait! Solve Delegation Easily! In Power Apps Search

With Power Automate we can create Flows that beat the delegation limit and in this video we show exactly that! If you are just looking for the secret sauce just go right to the @15:50 mark and that's where we make the Flow!

This is part 5 of our complete app creation:

Using Power Automate as a workaround to the delegation limits in Power Apps can be highly effective, especially for larger datasets. By creating flows in Power Automate that handle data operations—like advanced queries or aggregations—on SharePoint lists or other data sources, you can bypass the Power Apps delegation constraints. These flows can then be triggered directly from Power Apps, allowing the retrieval, processing, and return of data in manageable chunks or as a pre-processed result, ensuring your app remains responsive and robust, regardless of dataset size. This method not only overcomes delegation limits but also enhances the app's capability by leveraging the power of server-side processing.

When integrating SharePoint with Power Apps, users often encounter the "delegation" issue, which arises when dealing with datasets larger than 500 items by default or 2000 max. Power Apps requires this delegation to efficiently handle data queries without overloading memory or processing power. To manage this, it's crucial to use delegable queries and functions in Power Apps, such as filtering on indexed columns, to ensure your app remains performant and scalable. Understanding and planning for these delegation limits is key to building effective and efficient applications that leverage SharePoint data.

Chapters:
0:00 Introduction
0:49 Populate Fake Data with Power Apps
4:15 Populate Fake Data with Power Automate
11:00 Creating a Search Bar with StartsWith
14:00 Breaking with Non-Delegable Functions
15:50 The Flow to Fix Delegation
19:50 The Fx Formula that uses Flow for Delegation
26:40 Testing
27:35 Our Patch Broke Due to using a Collection
28:30 Adding the Power Automate Fx to Patch on Edit

[andrewhess]

Hi All, if you just want to skip to the Power Automate to solve delegation it's at about 15:50 but there's a lot of tips in between. Also at about 27:40 we broke our "Edit" patch statement and fixed it with a lookup!

[_samurai]

Making some use of the offline holidays, thanks brother!

[pequadcob]

But how can you do this for multiple filters? I am having trouble having it return all if the variable is blank.

[imranali]

Hi Andrew, great video, as always; very informative and well explained. Appreciate your knowledge sharing. 👍👍

[vaibhavrajput]

Hey andrew, very informative video! How would apply bulk updates on top of this? I am unable to figure that part out.

[sergiocaballero]

Thank you, Andrew, for another great video! Would this work with any number of records? Or is this also limited to a max 5000 list items at a time via power automate?

[geoffreymckay]

great tutorial. got it working where textinput searches a pair of fields in my list. Works great. Then I added a second textinput field that searches a third field and I do it as an And between the two inputs. It works, but you have to have content in both fields or the filtering doesn't work. How do you handle a situation where the user may not want to include a search item in both fields...?thanks

[peacetalks]

I appreaciate this video, really interesting. I have a question, one of my searchs have 1576 results and this are not shown in the gallery, if I search for something different it appears but in this case I have this problem, I tried your solution and works in all the cases, less in this one

[NeilMarjoram]

Great video Andrew 👍🏻. Any ideas on how to do the same for multiline columns? The substringof fails when searching a multiline column

[kennethgottfredsen]

Hi Andrew, is it possible to reproduce this when the SP column is a number instead of text? I have a use case where a ID column has to stay as a number type.

[syazniel_]

Will performance be affected if 10 people run it simultaneously, each searching for an item and triggering Power Automate to run 10 flows at once?

[abdipowerappsguru]

Hi, this is good but relying on power automate it will count the flow runs and you may end up flow error that’s the downside of flow

[HyoseungLee]

Thanks, it makes my code more readible.
So far, I applied this way to solve deligation
- added IDFilter, put same number with ID value, it makes my code more un-readable
- whenever get data, using IDFilter column
. Get max ID
. calculate number, N by MOD(maxID, 2000)
. collect data by Filter function N times
=> this makes my code terribly un-readable

but I have question,
which is better in performance view, collecting data N times or use power automate as you show me?

[walkingdadjokes]

Thank you for this video.
For some reason I was expecting the fix for delegation would have been to load all SharePoint list items in collections and combine them together. Search on title and customer name using substring in power automate is great.

What about something more general to group many items of the same type with a filter (possibly multiple checkbox options?) like I would definitely see clients wanting to see Status == red or look at items for a specific category (or multiple?). Would it be the same?

Also for SharePoint Choice columns...how?

So basically, can a sort/filter options that is typically seen in lists be done in power apps with big lists (over 2k)?
How about combining multiple lists?

[DirtNerds]

why does it seem no one shows how to prevent this is sql server databases?

[kennethgottfredsen]

Does this work if the data source is Excel?