Wait! Solve Delegation Easily! In Power Apps Search
Вставка
- Опубліковано 20 лип 2024
- 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 - Наука та технологія
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!
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?
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.
Hi Andrew, great video, as always; very informative and well explained. Appreciate your knowledge sharing. 👍👍
Thanks for still watching Imran! Appreciate your support! 👍
@@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. :)
@@imran002ali Too kind!!!
Great video Andrew 👍🏻. Any ideas on how to do the same for multiline columns? The substringof fails when searching a multiline column
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.
Thanks. The guide in that link worked great
@@NeilMarjoram Noice!
Andrew. Do you know if the attachment column can be parsed through the same process?
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
Agreed, best to pay for SQL or Dataverse, but not everyone can.
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. 😅
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?
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
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?
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.
@@andrewhess123 Thank you
@@walkingdadjokes6099 Thanks you too walkingdad! Hopefully the advice is helpful!
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?
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.
Hyoseung, I would definitely try in a dev environment first before messing with anything in production.
@@andrewhess123 Thanks addrew... waiting for your feedback in happy
Better use api graph
Does this work if the data source is Excel?
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
@@andrewhess123 thank you for the source recommendation Andrew. Love your videos.