Wait! Solve Delegation Easily! In Power Apps Search

No video

Wait! Solve Delegation Easily! In Power Apps Search

Рет қаралды 3,564

Күн бұрын

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:
Part 1: • Creating a Power App f...
Part 2: • Creating an Edit Form ...
Part 3: • Convert Default Form t...
Part 4: • Making Containers work...
Part 5: • Wait! Solve Delegation...
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

Пікірлер: 31

@andrewhess123 2 ай бұрын

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!

@sergiocaballero1777 2 ай бұрын

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?

@andrewhess123 2 ай бұрын

Hi Sergio, through my testing I believe the limit is 100,000 although I have only tested up to 8000. If you increase your pagination and your topcount in your get items call it is well above 5000 items retrieved. It would be important to build your get items filter query in a way that you don't retrieve that many records, unless there is some specific reason in your requirements you must. If you wanted to retrieve large number of records I would probably use Power BI or then switch over to dataverse/sql/dataverse for teams.

@imran002ali 2 ай бұрын

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

@andrewhess123 2 ай бұрын

Thanks for still watching Imran! Appreciate your support! 👍

@imran002ali 2 ай бұрын

@@andrewhess123 , you are welcome. I have forwarded your contents to my peers and they loved your videos as well, looking forward to more great videos!! Appreciate it. :)

@andrewhess123 2 ай бұрын

@@imran002ali Too kind!!!

@NeilMarjoram 2 ай бұрын

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

@andrewhess123 2 ай бұрын

So with a multi-line column it's going to get way more complex, some ways to make it simple may be to just grab the first 250 words and put that in a variable? and then search.. other options I found this: tomriha.com/power-automate-filter-on-multiple-lines-of-text-sharepoint-column/ But it will be a lot more difficult with multi-line columns especially if enhanced rich text is on.

@NeilMarjoram 2 ай бұрын

Thanks. The guide in that link worked great

@andrewhess123 2 ай бұрын

@@NeilMarjoram Noice!

@NeilMarjoram 2 ай бұрын

Andrew. Do you know if the attachment column can be parsed through the same process?

@kennethgottfredsen767 3 күн бұрын

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.

@andrewhess123 3 күн бұрын

Hi Kenneth, should be possible with a number column. You can see the first column is {ID: Value(Value.ID)}, where the first Value() actually makes sure that the ID field is a number column.

@syazniel_4779 2 ай бұрын

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?

@andrewhess123 2 ай бұрын

Performance will work better with premium licenses, with M365 plans you are at a lower profile, this is more of Power Automate and how you build out your flow. Your flow could be customized and take longer. You can see more about Power Automate performance here: learn.microsoft.com/en-us/power-automate/limits-and-config

@walkingdadjokes6099 2 ай бұрын

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?

@andrewhess123 2 ай бұрын

Hi Walkingdadjokes! so... yes combining all sharepoint list items into collections per 2,000 could work, but if you have thousands of rows that may become an issue. I think Power Automate does a better job. You can build your filter query based on the requirements you don't have to use substringof(), you can do "less than", "greater than", startswith, and, or, not and many other functions inside your get filtery query. Choice columns can be solved with "eq" for equals. Once you go into multi-choice it most likely will be super complex and I would stay away from those options. You could probably break up a choice by "comma" delimeter and search within a string of that in a variable, but changing requirements of the customer may be a better option than giving yourself a headache. If you started combining multiple lists I would probably go to Power BI and do a JOIN instead of trying to view it in Power Apps. In my opinion Power Apps is a front end that is for data entry, if you want to view all your data in a specific way I would use Power BI for that.

@walkingdadjokes6099 2 ай бұрын

@@andrewhess123 Thank you

@andrewhess123 2 ай бұрын

@@walkingdadjokes6099 Thanks you too walkingdad! Hopefully the advice is helpful!

@HyoseungLee 2 ай бұрын

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?

@andrewhess123 2 ай бұрын

The Power Automate is pretty fast. I would have to do a comparison to let you know which is better... but my assumption is collecting data N times could be worse in performance. Especially if you have hundreds of thousands of rows. But this is a guess not verified.

@andrewhess123 2 ай бұрын

Hyoseung, I would definitely try in a dev environment first before messing with anything in production.

@HyoseungLee 2 ай бұрын

@@andrewhess123 Thanks addrew... waiting for your feedback in happy

@TomZero1489 Ай бұрын

Better use api graph

@abdipowerappsguru1053 Ай бұрын

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

@andrewhess123 Ай бұрын

Agreed, best to pay for SQL or Dataverse, but not everyone can.

@abdipowerappsguru1053 Ай бұрын

Hi, myself Sharepoint person never used dataverse or sql, I filter multiple columns and starts with more than 50 thousand records that works well and no delegation. I hope Microsoft do better and let Sharepoint users to use search function, which I don’t see no reason why not allow. Apart than forcing people to buy other services. 😅

@kennethgottfredsen767 2 ай бұрын

Does this work if the data source is Excel?

@andrewhess123 2 ай бұрын

This is using a SharePoint Odata query REST call, I have not tried with Excel, there may be different limitations with Excel, but I do see filter Odata queries in Excel too. Maybe some limitations. powerusers.microsoft.com/t5/General-Power-Automate/examples-of-how-to-enter-a-filter-query-for-excel-list-row-in-a/td-p/326434

@kennethgottfredsen767 2 ай бұрын

@@andrewhess123 thank you for the source recommendation Andrew. Love your videos.