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.
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
@@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. :)
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
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.
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. 😅
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.
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.
Good question and this is getting requested more and more. Most Power Apps developers are being pushed towards Dataverse so I believe SQL becomes less of a topic. I'm not saying I don't like SQL, honestly I prefer SQL. Also, SQL has less delegation issues compared to using a SharePoint backend, so I think that's why no ones talking about it as much. But I do think yes! We should be talking about using SQL more. I'm going to write it down.
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
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.
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
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!!!
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
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. 😅
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.
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
But how can you do this for multiple filters? I am having trouble having it return all if the variable is blank.
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
Thanks for watching NA
why does it seem no one shows how to prevent this is sql server databases?
Good question and this is getting requested more and more. Most Power Apps developers are being pushed towards Dataverse so I believe SQL becomes less of a topic. I'm not saying I don't like SQL, honestly I prefer SQL. Also, SQL has less delegation issues compared to using a SharePoint backend, so I think that's why no ones talking about it as much.
But I do think yes! We should be talking about using SQL more. I'm going to write it down.
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.
Better use api graph