No video :(

No Delegation Limit - SharePoint List Power App

Подписаться 1,4 тыс.

Просмотров 12 тыс.

50% 1

Community Thread & Download Page: powerusers.mic...
Microsoft started renovating their community site. They didn't transfer the download 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.c...
This template app & flow set-up presents a method of filtering, searching, sorting, & loading a large SharePoint list data without delegation limitations.
Usually one could not get a Power App to get more than 2000 items across a large SharePoint list with more advanced filters and searches, especially on non-standard fields like a Created By column, multiline text column, or on many columns at once. This template does that & more using a Power Automate flow and the ParseJSON( ) expression in Power Apps.
Say goodbye to SharePoint list delegation limitations & issues.
Load a much larger number of SharePoint records into a Power App, up to 100MB worth.
And avoid the SharePoint interface 5000 list view threshold with an app interface that can display many more than 5000 records on a screen.
All without premium connectors.
Venmo
venmo.com/u/Ty...
PayPal
www.paypal.com...
Buy Me a Coffee
www.buymeacoff...
Thanks for any feedback,
And reach out on LinkedIn ( / kolota ) if you want to hire me to consult or build more custom Microsoft solutions for you.

Опубликовано:

5 сен 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 49

@HaileyZikarge 7 месяцев назад

Thank you; this is really helpful and brilliant. Please do more videos like Reza is doing.

@timshinkle2782 7 месяцев назад

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.

@tylerkolota 7 месяцев назад

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: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-Tviar3UFFLo.htmlsi=JO3LouG2SH3AjRw3

@notuern 28 дней назад

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.).

@tulasirao360 11 месяцев назад

Thank you ,it is good stuff , Can you show us what you are doing in the flow as well clearly

@tylerkolota 11 месяцев назад

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.

@tylerkolota 11 месяцев назад

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.

@tylerkolota 11 месяцев назад

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.

@speranza4music 11 месяцев назад

why not using graph search in the powerapp?@@tylerkolota

@tylerkolota 8 месяцев назад

A video on the flow is now up: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-V1d3V_cWVaA.htmlsi=QrMce_mV1F2fw1oa

@ekoiswinarso 4 дня назад

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?

@tylerkolota 4 дня назад

@@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.

@DanielJustiniani-tb3jh 10 месяцев назад

Thank you, you have helped me a lot.

@tylerkolota 8 месяцев назад

Thank you, glad to hear that it was helpful!

@jbailey130 8 месяцев назад

This is brilliant.

@tylerkolota 8 месяцев назад

Thank you, hope it helps!

@jbailey130 8 месяцев назад

@@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?

@tylerkolota 8 месяцев назад

@@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')}

@dc71181 8 месяцев назад

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.

@dc71181 8 месяцев назад

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.

@tylerkolota 8 месяцев назад

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.

@tylerkolota 8 месяцев назад

@@dc71181 A video on that flow is now up: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-V1d3V_cWVaA.htmlsi=QrMce_mV1F2fw1oa

@uzoruchenna4863 6 месяцев назад

This is awesome

@tylerkolota 6 месяцев назад

Thank you!

@natcongreso2763 7 месяцев назад

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?

@tylerkolota 7 месяцев назад

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.

@cat-si1et 28 дней назад

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?

@tylerkolota 28 дней назад

@@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 26 дней назад

how does power automate address pagination?

@tylerkolota 26 дней назад

@@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.

@billionairemaker 4 месяца назад

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?

@tylerkolota 4 месяца назад

Did you get a 502 Bad Gateway on the Power Automate flow side or the app side?

@thanhsonnguyen7755 3 месяца назад

when sharepoint list have 1 milion records. can app show data it well?

@tylerkolota 3 месяца назад

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.

@thanhsonnguyen7755 3 месяца назад

@@tylerkolota Thanks so much! I will try on my app now, I hope my app will run successful. Thanks for your shared

@parranoic Месяц назад

Thank you! Any chance you can re upload the zip ? Microsoft nuked their community site this week

@tylerkolota Месяц назад

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

@parranoic Месяц назад

@@tylerkolota You are amazing, truly thank you!

@asheerhasan5680 Месяц назад

mind blowing but am unable to download the list and solution

@tylerkolota Месяц назад

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

@asheerhasan5680 Месяц назад

@@tylerkolota thanks a lot

@denniswarren5871 Месяц назад

Blog post is corrupt. Cannot download.

@tylerkolota Месяц назад

@edgar3tres 11 месяцев назад

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));

@tylerkolota 11 месяцев назад

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.

@edgar3tres 10 месяцев назад

@@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?

@tylerkolota 10 месяцев назад

@@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.

@edgar3tres 10 месяцев назад

@@tylerkolota Awesome Thanks a lot for your help!