This is pretty cool. What if we want to show more columns than are on the main screen, can you add a horizontal scroll bar? Also, what if you wanted to select one or many rows to perform an action on them? Is there a select capability that can be added like a checkbox with a select all? Thanks much.
I don’t know of any horizontal scroll bars. You can play with spacing/padding, font sizes, etc to try to cram more in. And double check how many columns you really need to show on a single screen or maybe you need to split it up into different views. As for selecting multiple lines for edits, Reza recently did such a video: ruclips.net/video/Tviar3UFFLo/видео.htmlsi=JO3LouG2SH3AjRw3
Create the gallery inside a vertical or horizontal container(Doesn't matter which.), enable scrollbars on the container, set the gallery size to fit all the columns you need. Now you should get a horizontal scrollbar on the container that allows you to scroll. You can also do this inside the gallery if you wish by adding a container and having the columns inside of the container, that way you can have ID and Title to the far left as always visible and then have a container to the right of it with the remaining columns which can then be scrolled (Makes it easier to keep track of what the user is looking at.).
Yeah, I wanted this to be the basic set-up & Power Apps side of things without making the video too long. Then I’ll probably need to do a separate video just dedicated to the flow created for the efficient & fast SharePoint list data reads.
At a high level, it gets the highest & lowest ID numbers from the list, & uses those to calculate how many batches it needs to read in from SP. Then concurrently reads those batches in using the SP HTTP action. If there is a search term, it is using the process that applies the search filter array. Then it does some reformatting of all the batches of data so it can get everything into a single combined JSON array, like what you would normally get out of a SP Get items with pagination turned on. Once it has the single combined JSON array, it applies the sort on the given column in the given order & takes a top count of items from that to return to the app.
I would have split it out into more readable actions & expression logic, but unfortunately one of the largest query speed considerations is how many actions Power Automate must pass the query data between. So I had to make everything compact to make it fast.
@@Hellocrzywrld The reverse is not crucial in any way, just a very minor performance improvement because there is a later Sort( ) in the Respond to PowerApp actions that must sort the entire set of data in ascending order. So if all the batches were sorted in descending order then that ascending step would take a little longer because it would have to reverse the order within each batch & do it on an action that isn’t working concurrently. Meanwhile reversing the order at the previous step should require less computation & happen concurrently.
Thanks for sharing this, its a very elegant solution to a common problem. I will add to the requests for the Power Automate information. I am trying to recreate the flow, but have an issue with trying to reference the JSON items that are in the 'Settings' compose step, like SiteAddress and ListName .The Dynamic content from the PowerApps(V2) step is available but not the 'Settings' properties. Any help would be appreciated.
I asked too soon, the answer is to use an expression, then close and reopen the Flow, then the expressions will magically change to the Property Name of the compose item.
Thank you very much, this is really fantastic. If I had an additional Value column and wanted to see a total label at the end, how would I sum all the items that appear in the gallery?
Are you talking about a Gallery in Power Apps? There is a way to reference AllItems for a gallery column & use the Sum( ) expression on it. www.crmonce.com/how-to-calculate-gallery-items-in-power-apps/
Hi! Just checking if you are still using connection to sharepoint? I'm doing the same thing with you using Powerautoamte to get,update and delete sharepoint item using power apps. Are you using a form to fill-out or edit items?
Yes, I set it to use a SharePoint connection for any of the viewing & editing of individual items on the details screen. The Power Automate flow piece just helps view & select all relevant items in the initial gallery.
@@tylerkolotaI do have a question when setting up the flows. In the condition, in the "no" branch I have configured "Apply to each Get items With search" with no issues. However, when I get to "Select ReformatItemJSON" I do not have "Body" output available. I can use the "Body" from the the min and max calls or the body from the "PowerApps (V2)" step. What did I miss?
@@jbailey130 It lost the reference during import? To get the reference I usually have to create an extra Compose action inside the loop, insert the body of the Filter array search action in the Compose, copy the dynamic content, then paste it into the action outside the loop & delete the extra Compose. But you can also try just copying & pasting this in... @{body('Filter_array_ApplySearch')}
If I want to search multiple values for a column, would I need to add a loop in the power automate flow? Or can I modify the odata query to filter a list of values?
@@cat-si1et Hm, I have not heard a use-case where someone wants to search two non-contiguous words/phrases before. But I would think you could edit the flow to check if there is a second search term, then use a Filter array query with an or( ) & 2 contains expressions.
@@s1301950 You could add some parameters to the flow so it only returns x number of items between set page numbers/items. But personally I have yet to encounter a use-case where I’d have a user going page by page through results instead of refining their filters & search input to go directly to the item(s) they need.
Hi, your works are very cool and impressive! 👍Wonder if we can add the pagination function on top of this, both on the Power Automate (with offset and limit) and Power App side so the data load can be reduced? I'm sure most of the users only want to search less than first 100 records to check?
@@ekoiswinarso the way this is designed, I don’t see paging reducing load times much on the Power Automate side. It still has to load all the items into the flow before it then applies the page cut-offs.
@@ekoiswinarso I gather offset & limit in Power Automate would refer to the Top count & Skip count. That’s the thing, the Excel connector / calls include both a Top & Skip, but the SharePoint connector / calls only include a Top parameter. To do something like a Skip in SP I think you need to actually make a call to SP, get the items, & take the ID of the last item to then in the next SP call get items where ID is greater than the previous last ID. The set up for this template doesn’t even use that type of set-up, instead it uses a faster method of concurrently pulling all items at once.
@@ekoiswinarso There is an option to use pagination links, but the set-up requires a slower Do-until loop, just like the other slower ID method I mentioned. We want something that works with a faster, concurrent Apply to each loop. www.matthewdevaney.com/get-over-5000-items-from-a-sharepoint-list-in-power-automate/#Initialize-Variables-For-List-Items-and-SharePoint-URI
Hi @@tylerkolota , very appreciate for your replies 👍😊. The "Skip" clue is very interesting, unfortunately it only works for Excel. And also yes, I already read that link. However, back to my requirement for pagination, I won't rely on ID at all since user will have capabilities to sort and search, thus the IDs will be randomly sort as the query result as well, not in ascending order anymore.
Dude! This is amazing. I installed the solution... but I am having a hard time making heads and tails of how to point it towards a different list. I changed the list name to a new list, but I am getting Network Errors 502 Bad gateway when running on start... the new list is in the same site as the SP Large List... Any ideas?
@@kennethgottfredsen767 Yes. By default it will search the entire record for any column value or column name matching your input. Including number column values.
This method is reliant on Power Automate which has a 100MB to 200MB message size limit. I would never try loading all 1million items to an app. This method will let one search & filter over the most recently created 100-400k items in the list & return the top results from that. If a contains search or other non-delegable filter is really important for the app, then you may try using this. But if you can you may want to stick to a standard app with all delegable queries for the best performance.
Yes, they didn't transfer the upload files from the old site, & I can't manually re-upload any of the download files there at the moment. I'm waiting on a solution from their support team. In the meantime, here is a Google drive link to a zip folder with solution imports for most of my projects: drive.google.com/file/d/1-hZmu2-QdEl7l95Y3NdZ1jn8sYdaK2kD/view?usp=sharing
Great Video! one question, can I filter by a calculated column using the dropdowns? I can see the options in the dropdown but when I filter it doesn't get any results. This is what I have in the filter button formula: If(IsBlank(FilterRegionDropdown.Selected.Value), "", "Region_x0020_Group eq '"&FilterRegionDropdown.Selected.Value&"' and ") and this is what I have in the Onstart formula: Set(RegionGroupDropdownOptions, Sort(Distinct(Table(ParseJSON(QueryResult)), Text(Value.Region_x0020_Group)).Value,Value));
All the filter odata expressions ultimately go to a SharePoint HTTP action filter query, and it looks like you can’t filter by calculated columns there. www.google.com/search?q=power+automate+sharepoint+filter+on+calculated+column&ie=UTF-8&oe=UTF-8&hl=en-us So you could… -If possible find a way to replicate the filter using the column values the calculated column relies on. OR -Make the column a text column & calculate those text field values through a flow &/or through the app. OR -Add another app input to the flow for the drop-down’s selected option. Then add another Filter array before the search Filter array so if the new app input for a Region filter is blank, then default true equals true else filter the SharePoint HTTP outputs by the region input, like… if(empty(SelectedRegion), true, equals(SelectedRegion, item()[‘Region_x0020_Group’])) equals @{true} That would filter to just the items with the calculated region matching the selected region, so then you could pass that Filter array outputs to the next Filter array to apply the search filter. Then you would do the same action between the SP HTTP & Compose action on the other Yes side of the condition.
@@tylerkolota I end up using the column values the calculated columns relies on and setting just the values I want in the dropdown and it worked great!. Also, can I use the same flows on a different pages in the App with other SharePoint lists and replace the name of the List in the code or do I have to create new flows?
@@edgar3tres Yea it is set so you can use the same flow for different lists. Just make sure you are updating the Select, Filter, Search, & other query variables to the relevant list before the new query for the new list is sent. Also you’ll probably want to save the outputs to different variables for each page/list.
Microsoft started renovating their community site, they didn't transfer the upload files from the old site, & I can't manually re-upload any of the download files there at the moment. I'm waiting on a solution from their support team. In the meantime, here is a Google drive link to a zip folder with solution imports for most of my projects: drive.google.com/file/d/1-hZmu2-QdEl7l95Y3NdZ1jn8sYdaK2kD/view?usp=sharing
Microsoft started renovating their community site, they didn't transfer the upload files from the old site, & I can't manually re-upload any of the download files there at the moment. I'm waiting on a solution from their support team. In the meantime, here is a Google drive link to a zip folder with solution imports for most of my projects: drive.google.com/file/d/1-hZmu2-QdEl7l95Y3NdZ1jn8sYdaK2kD/view?usp=sharing
Thank you; this is really helpful and brilliant. Please do more videos like Reza is doing.
This is pretty cool. What if we want to show more columns than are on the main screen, can you add a horizontal scroll bar? Also, what if you wanted to select one or many rows to perform an action on them? Is there a select capability that can be added like a checkbox with a select all? Thanks much.
I don’t know of any horizontal scroll bars.
You can play with spacing/padding, font sizes, etc to try to cram more in. And double check how many columns you really need to show on a single screen or maybe you need to split it up into different views.
As for selecting multiple lines for edits, Reza recently did such a video: ruclips.net/video/Tviar3UFFLo/видео.htmlsi=JO3LouG2SH3AjRw3
Create the gallery inside a vertical or horizontal container(Doesn't matter which.), enable scrollbars on the container, set the gallery size to fit all the columns you need.
Now you should get a horizontal scrollbar on the container that allows you to scroll.
You can also do this inside the gallery if you wish by adding a container and having the columns inside of the container, that way you can have ID and Title to the far left as always visible and then have a container to the right of it with the remaining columns which can then be scrolled (Makes it easier to keep track of what the user is looking at.).
Thank you ,it is good stuff ,
Can you show us what you are doing in the flow as well clearly
Yeah, I wanted this to be the basic set-up & Power Apps side of things without making the video too long.
Then I’ll probably need to do a separate video just dedicated to the flow created for the efficient & fast SharePoint list data reads.
At a high level, it gets the highest & lowest ID numbers from the list, & uses those to calculate how many batches it needs to read in from SP. Then concurrently reads those batches in using the SP HTTP action. If there is a search term, it is using the process that applies the search filter array.
Then it does some reformatting of all the batches of data so it can get everything into a single combined JSON array, like what you would normally get out of a SP Get items with pagination turned on.
Once it has the single combined JSON array, it applies the sort on the given column in the given order & takes a top count of items from that to return to the app.
I would have split it out into more readable actions & expression logic, but unfortunately one of the largest query speed considerations is how many actions Power Automate must pass the query data between. So I had to make everything compact to make it fast.
why not using graph search in the powerapp?@@tylerkolota
A video on the flow is now up: ruclips.net/video/V1d3V_cWVaA/видео.htmlsi=QrMce_mV1F2fw1oa
Thanks for the great video
May I know why did you use the reverse function in flow when joining the data returned from spo http call?
@@Hellocrzywrld The reverse is not crucial in any way, just a very minor performance improvement because there is a later Sort( ) in the Respond to PowerApp actions that must sort the entire set of data in ascending order. So if all the batches were sorted in descending order then that ascending step would take a little longer because it would have to reverse the order within each batch & do it on an action that isn’t working concurrently. Meanwhile reversing the order at the previous step should require less computation & happen concurrently.
Thanks for sharing this, its a very elegant solution to a common problem. I will add to the requests for the Power Automate information. I am trying to recreate the flow, but have an issue with trying to reference the JSON items that are in the 'Settings' compose step, like SiteAddress and ListName .The Dynamic content from the PowerApps(V2) step is available but not the 'Settings' properties. Any help would be appreciated.
I asked too soon, the answer is to use an expression, then close and reopen the Flow, then the expressions will magically change to the Property Name of the compose item.
Well thanks for mentioning this. A separate video on the quick SP list read/filter/search/sort is next on my list of video ideas.
@@dc71181 A video on that flow is now up: ruclips.net/video/V1d3V_cWVaA/видео.htmlsi=QrMce_mV1F2fw1oa
Thank you, you have helped me a lot.
Thank you, glad to hear that it was helpful!
Thank you very much, this is really fantastic. If I had an additional Value column and wanted to see a total label at the end, how would I sum all the items that appear in the gallery?
Are you talking about a Gallery in Power Apps?
There is a way to reference AllItems for a gallery column & use the Sum( ) expression on it.
www.crmonce.com/how-to-calculate-gallery-items-in-power-apps/
Hi! Just checking if you are still using connection to sharepoint? I'm doing the same thing with you using Powerautoamte to get,update and delete sharepoint item using power apps. Are you using a form to fill-out or edit items?
Yes, I set it to use a SharePoint connection for any of the viewing & editing of individual items on the details screen.
The Power Automate flow piece just helps view & select all relevant items in the initial gallery.
This is brilliant.
Thank you, hope it helps!
@@tylerkolotaI do have a question when setting up the flows. In the condition, in the "no" branch I have configured "Apply to each Get items With search" with no issues. However, when I get to "Select ReformatItemJSON" I do not have "Body" output available. I can use the "Body" from the the min and max calls or the body from the "PowerApps (V2)" step. What did I miss?
@@jbailey130 It lost the reference during import?
To get the reference I usually have to create an extra Compose action inside the loop, insert the body of the Filter array search action in the Compose, copy the dynamic content, then paste it into the action outside the loop & delete the extra Compose.
But you can also try just copying & pasting this in...
@{body('Filter_array_ApplySearch')}
If I want to search multiple values for a column, would I need to add a loop in the power automate flow? Or can I modify the odata query to filter a list of values?
@@cat-si1et Hm, I have not heard a use-case where someone wants to search two non-contiguous words/phrases before. But I would think you could edit the flow to check if there is a second search term, then use a Filter array query with an or( ) & 2 contains expressions.
how does power automate address pagination?
@@s1301950 You could add some parameters to the flow so it only returns x number of items between set page numbers/items.
But personally I have yet to encounter a use-case where I’d have a user going page by page through results instead of refining their filters & search input to go directly to the item(s) they need.
This is awesome
Thank you!
Hi, your works are very cool and impressive! 👍Wonder if we can add the pagination function on top of this, both on the Power Automate (with offset and limit) and Power App side so the data load can be reduced? I'm sure most of the users only want to search less than first 100 records to check?
@@ekoiswinarso the way this is designed, I don’t see paging reducing load times much on the Power Automate side. It still has to load all the items into the flow before it then applies the page cut-offs.
Hi@@tylerkolota, are there some method on getting list data with combination of offset and limit in power automate, similiar with Odata filter?
@@ekoiswinarso I gather offset & limit in Power Automate would refer to the Top count & Skip count. That’s the thing, the Excel connector / calls include both a Top & Skip, but the SharePoint connector / calls only include a Top parameter. To do something like a Skip in SP I think you need to actually make a call to SP, get the items, & take the ID of the last item to then in the next SP call get items where ID is greater than the previous last ID.
The set up for this template doesn’t even use that type of set-up, instead it uses a faster method of concurrently pulling all items at once.
@@ekoiswinarso There is an option to use pagination links, but the set-up requires a slower Do-until loop, just like the other slower ID method I mentioned. We want something that works with a faster, concurrent Apply to each loop.
www.matthewdevaney.com/get-over-5000-items-from-a-sharepoint-list-in-power-automate/#Initialize-Variables-For-List-Items-and-SharePoint-URI
Hi @@tylerkolota , very appreciate for your replies 👍😊. The "Skip" clue is very interesting, unfortunately it only works for Excel. And also yes, I already read that link. However, back to my requirement for pagination, I won't rely on ID at all since user will have capabilities to sort and search, thus the IDs will be randomly sort as the query result as well, not in ascending order anymore.
Dude! This is amazing. I installed the solution... but I am having a hard time making heads and tails of how to point it towards a different list.
I changed the list name to a new list, but I am getting Network Errors 502 Bad gateway when running on start... the new list is in the same site as the SP Large List... Any ideas?
Did you get a 502 Bad Gateway on the Power Automate flow side or the app side?
@@tylerkolota Hello, I received the same error using the solution and the BadGateway happened in the Power Automate Flow side
@@Wolf0300 What are your list & site settings?
Does this work if I want to search on a number column?
@@kennethgottfredsen767 Yes. By default it will search the entire record for any column value or column name matching your input. Including number column values.
when sharepoint list have 1 milion records. can app show data it well?
This method is reliant on Power Automate which has a 100MB to 200MB message size limit. I would never try loading all 1million items to an app.
This method will let one search & filter over the most recently created 100-400k items in the list & return the top results from that. If a contains search or other non-delegable filter is really important for the app, then you may try using this. But if you can you may want to stick to a standard app with all delegable queries for the best performance.
@@tylerkolota Thanks so much! I will try on my app now, I hope my app will run successful. Thanks for your shared
Thank you! Any chance you can re upload the zip ? Microsoft nuked their community site this week
Yes, they didn't transfer the upload files from the old site, & I can't manually re-upload any of the download files there at the moment. I'm waiting on a solution from their support team.
In the meantime, here is a Google drive link to a zip folder with solution imports for most of my projects: drive.google.com/file/d/1-hZmu2-QdEl7l95Y3NdZ1jn8sYdaK2kD/view?usp=sharing
@@tylerkolota You are amazing, truly thank you!
Great Video! one question, can I filter by a calculated column using the dropdowns? I can see the options in the dropdown but when I filter it doesn't get any results.
This is what I have in the filter button formula: If(IsBlank(FilterRegionDropdown.Selected.Value), "", "Region_x0020_Group eq '"&FilterRegionDropdown.Selected.Value&"' and ")
and this is what I have in the Onstart formula: Set(RegionGroupDropdownOptions, Sort(Distinct(Table(ParseJSON(QueryResult)), Text(Value.Region_x0020_Group)).Value,Value));
All the filter odata expressions ultimately go to a SharePoint HTTP action filter query, and it looks like you can’t filter by calculated columns there.
www.google.com/search?q=power+automate+sharepoint+filter+on+calculated+column&ie=UTF-8&oe=UTF-8&hl=en-us
So you could…
-If possible find a way to replicate the filter using the column values the calculated column relies on.
OR
-Make the column a text column & calculate those text field values through a flow &/or through the app.
OR
-Add another app input to the flow for the drop-down’s selected option. Then add another Filter array before the search Filter array so if the new app input for a Region filter is blank, then default true equals true else filter the SharePoint HTTP outputs by the region input, like…
if(empty(SelectedRegion), true, equals(SelectedRegion, item()[‘Region_x0020_Group’])) equals @{true}
That would filter to just the items with the calculated region matching the selected region, so then you could pass that Filter array outputs to the next Filter array to apply the search filter. Then you would do the same action between the SP HTTP & Compose action on the other Yes side of the condition.
@@tylerkolota I end up using the column values the calculated columns relies on and setting just the values I want in the dropdown and it worked great!.
Also, can I use the same flows on a different pages in the App with other SharePoint lists and replace the name of the List in the code or do I have to create new flows?
@@edgar3tres Yea it is set so you can use the same flow for different lists. Just make sure you are updating the Select, Filter, Search, & other query variables to the relevant list before the new query for the new list is sent. Also you’ll probably want to save the outputs to different variables for each page/list.
@@tylerkolota Awesome Thanks a lot for your help!
Blog post is corrupt. Cannot download.
Microsoft started renovating their community site, they didn't transfer the upload files from the old site, & I can't manually re-upload any of the download files there at the moment. I'm waiting on a solution from their support team.
In the meantime, here is a Google drive link to a zip folder with solution imports for most of my projects: drive.google.com/file/d/1-hZmu2-QdEl7l95Y3NdZ1jn8sYdaK2kD/view?usp=sharing
mind blowing but am unable to download the list and solution
Microsoft started renovating their community site, they didn't transfer the upload files from the old site, & I can't manually re-upload any of the download files there at the moment. I'm waiting on a solution from their support team.
In the meantime, here is a Google drive link to a zip folder with solution imports for most of my projects: drive.google.com/file/d/1-hZmu2-QdEl7l95Y3NdZ1jn8sYdaK2kD/view?usp=sharing
@@tylerkolota thanks a lot