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!
@@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. :)
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.
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.
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
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.
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.
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. 😅
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.
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.
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
