PowerApps Delegation and the 500 item limit

[@shaunvanhuyssteen7966]

I hope you realize how many people you help, man. Thanks for the great videos. You carry a lot of businesses on your shoulders with these guides and we really appreciated you.

[@ShanesCows]

Thanks for the kind words Shaun. I am glad to do my part. 😀

[@jlzpacheco]

Exactly, and still make it fun! Feels like i´m talking to a funny expressive friend.

[@gokyo_66]

Again you made the things clear than some other youtube video over delegation... I think you're the only one that actually explained the meaning of delegate.. "to delegate to the datasource querying the data" with this simple sentence I understood everything. Many thanx

[@ShanesCows]

Glad to help Roberto.

[@nharzic9248]

Good grief! The light bulb has been switched on. Every time I watch one of your videos, I lean something important. I had been ignoring that triangle because I couldn't quite get my head around what was going on. Now I have no excuses. Thank you as always, Shane.

[@ShanesCows]

🥰 Awesome. You got this.

[@jeannebostick6536]

😂I loved this video, I laughed so much. In a way Delegation is like the 5000 document limit in a SharePoint library, you can load 40,000 files into a library but you can't see them until you Index, which no one wants to do. People actually migrated 40,000 files into a library on an assignment I was on. I tried to tell them! Your videos are really helping me to get comfortable with PowerApps. Thank you!

[@ShanesCows]

Thanks for sharing. 😀 so many silly things have been done with SharePoint 🤩

[@lamchitam5989]

This is crazy helpful man!!!! Your content, your voice, your expression, that's super awesome. I watched entire a video without skipping!

[@ShanesCows]

Awesome, thank you!

[@smritimondal8965]

Shane is awesome. His style of teaching is just superb. The facial expressions that he makes, keeps the audience glued to the video. Thanks a lot for helping me and the entire community.

[@ShanesCows]

Wow, thanks!

[@dacsch97]

This was so helpful. I never knew the consequences of letting that little yellow triangle hang out in my Power App. Now it all makes sense. Thanks!

[@ShanesCows]

Awesome 🤩 😎☺️

[@nicoletimmons2344]

"SharePoint's like "Nah baby, nah - I don't do that!"" Ha -- I love it! Thanks for another very informative (and entertaining) video!

[@ShanesCows]

Glad you enjoyed it! 😂

[@pumakaua]

Wow.... Now we have so much work to do ! We have many collections working with delagation filters! 😭 Thanks alot Shane!! You’re the best!

[@ShanesCows]

You are welcome 🙏

[@christopherlawes9286]

Hi Shane. Just wanted to let you know that I found this video SUPER useful! Thank you so much

[@ShanesCows]

Awesome. Glad to help. 😀

[@prismaerospace139]

I have really appreciated your videos, I have spent dozens of hours on this issue and have yet to find a solution for my particular use case. I don't feel like what I am trying to pull off isn't particularly complicated but for whatever reason there isn't a way to distinct my data without having delegation issues. This is a huge limitation of this platform, it seems like the entire point of powerapps is to be able to retrieve data with a user interface. I don't understand why this aspect of powerapps is so complicated.

[@ShanesCows]

YEah, distinct just is not delegable. SOrry

[@robertwilson6683]

Shane, Thanks this was really helpful, I had a dropdown box linked to another list, which as I now know only allows the first 500 items in a list (I currently have 999 and its growing. So I added a gallery for this list and added a text input box, for the main list identifier value to be typed in. This then allowed me to pull into my app five other referenced field values from my list, which weren't there originally. I also used the filter function elsewhere to show a specific image from a library in a form, which is used by people to verify a task (of which the picture of part of). really neat techniques.

[@ShanesCows]

Awesome Robert.

[@blackrush1234]

Again amazing Work. I'm losing a lot of my fear of PowerApps because of these great videos.

[@ShanesCows]

That is awesome 😎

[@LPadillaCR]

Another hit video!... Thank you!!!... Still breathless by keeping up the pace 🤠👍

[@ShanesCows]

More to come!

[@TDevolder]

Thx Shane... Just used your tips to remove "'stupid yellow triangles" from my prototype!

[@ShanesCows]

Awesome!

[@ybunsee]

Woop Woop! Thanks Shane! We can always count on you.

[@ShanesCows]

Happy to help!

[@naveengupta4010]

Great content to get a hold on the delegation concept. Keep doing this great work.

[@ShanesCows]

Glad to help

[@roid67]

Fantastic video. Your style is engaging, informative & entertaining. Thank you!

[@mlm918]

Thank you Shane! I realize what I was doing wrong because of your explanation. I was using Distinct function for my dropdown with a sharepoint list of 23k. Thanks a lot Shane!!

[@ShanesCows]

Fantastic!

[@Noah_Oye]

Hello. I think I’m facing the same problem at the moment and I can’t seem to figure it out

[@mlm918]

@@Noah_Oye Hey Noah just saw your comment. If I remembered it correctly distinct function has to go thru the list up until the one your app is able to display, hence for a more specific dropdown try to help the drop down have all the list. In my case I made a unique new list and put it in my dropdown. I hope my explanation is clear ✌️

[@Noah_Oye]

@@mlm918 yes it is Thank you👏🏾

[@cybersamurai99]

You are such a great teacher Shane, thank for all your videos.

[@ShanesCows]

My pleasure!

[@oluwatobiyusuf]

Thanks for sharing more insight on delegation around multiple data sources

[@louiscutmore]

This is so good to know and your methodology of trying it in different scenarios helps a lot!! Thank you 😊

[@ShanesCows]

You're so welcome!

[@CaffieneMan]

this video is extremely helpful, I have been fighting the default license limitations and delegation issues. Thank you sir!

[@ShanesCows]

Happy to help Joe

[@ashvinpatel5746]

This video is extremely helpful and Shane you did an excellent job in explaining....

[@ShanesCows]

Thanks 😊

[@sundarun]

Awesome explanation on yellow triangle, Shane

[@ShanesCows]

Thanks 😎

[@ramitkishoresaha9437]

Still helpful after 3 years!

[@ShanesCows]

This should be on my list to do again. 😎

[@Rieske1969]

Good video, very clear! Just started with PowerApps and I am amazed what you can do with it.

[@ShanesCows]

Thanks 😊

[@itsLarryAlright]

Nice one .. Especially how you explained delegates. Was pretty easy

[@ShanesCows]

Thanks 😊

[@chrysarreola5902]

Bro you are awesome. Literally my hero

[@ShanesCows]

Thanks 😎🐶

[@freakynerd05]

Thank you very much for this, I've been deleting my sharepoint data everytime after reaching 2000 record. This saves me

[@ShanesCows]

Glad it helped!

[@pavlosvasileiadis8209]

Thanks so much! You just helped me remove two very annoying delegation warnings on my app!

[@ShanesCows]

Awesome!

[@louismartini6983]

Another AWESOME! Video Keep it up man.

[@ShanesCows]

Thanks! Will do!

[@threewiseman1]

I worked out a way to use the 'in' operator to search for specific text in Gallery items from Sharepoint List, while avoiding delegation. First, set up a series of delegable filters that can narrow your list results directly from Sharepoint. While it's not absolutely essential, it's best if you can use a combination of delegable filters to narrow your Sharepoint List Gallery down to 100 items or less. Next, create a collection wherein the ClearCollect command is Gallery1.AllItems. Third, create a 2nd Gallery that has the Collection you just set up as the Data Source. It's then up to you to work out how you want this to function, but I have a search field that sits beneath my set of filters, and above my 1st Gallery. Upon selecting the search field, the OnSelect property of the search field has my ClearCollect('SearchResults', Gallery1.AllItems), and it turns the visibility of Gallery1 'off', and turns the visibility of Gallery2 'on' using a variable. The 2nd gallery sits on top of the first gallery, so as far as the user is concerned, they are searching the same gallery, unaware of my magical swapper-roo. The limitation I currently have is that Collect will only grab the first 100 items from the 1st Gallery, unless a user scrolls down the list as far as they can go prior to searching. But you are free to use the 'in' operation on a collection, as the data is stored within the app itself. The only thing to be careful about is to avoid making the collection too large. I get around this by disabling the search field until at least a couple of filters have been selected. EDIT: Thanks to Shane and this video for providing the inspiration to accomplish this! This was the last must have feature of my app, and we're rolling it out in my company this month! Hoorah!

[@ShanesCows]

Congratulations and thanks for sharing

[@SauloOliveira]

Fantastic video, thanks Shane.

[@ShanesCows]

Glad you enjoyed it

[@Ben-wq7bc]

Great explanation, thanks.

[@ShanesCows]

You're welcome!

[@psychoba]

Great video, thanks Shane!

[@ShanesCows]

Happy to help 😎

[@subjecttochrist]

excellent video... great job! i've learned ALOT by watching your videos.

[@ShanesCows]

Awesome 😎

[@r602-r6m]

looking at this video now . Using sharepoint as a datasource is the wrost for big datasources/lists. You have to user Filter and starswith. The best for large data is Azure and maybe CDS

[@ShanesCows]

Agree. CDS is best but not everyone has the license for that.

[@Peterkmil]

Hi Shane, I'm a novice with making apps or with IT in general. You're video's are very useful and with them I can manage my way arround the easy parts of power apps. I want to know about what happens in the function bar. What does al the code mean? What are the commands that are available? When do i use what? Is there some kind of beginners guide to those codes? Thanks a lot!

[@marcovidal6729]

Gracias por esta ayuda...!!! Greatings from Chile

[@ShanesCows]

Thanks Marco! 🐶

[@Kylbigel]

Loved the analogy of power apps being lazy and only able to compute 2000 non delegable records

[@ShanesCows]

Thank Kyle. Me too. Just makes it resonate so much better.

[@JoseLuisGarciaA]

Muchas gracias, por compartir tu experiencia y conocimiento!, me ha sido de gran utilidad

[@ShanesCows]

😊

[@wendymason3263]

You rock Shane!

[@ShanesCows]

Thanks Wendy 😻

[@Gracecarragher-d5i]

Great video! Thank you for this!

[@ShanesCows]

My pleasure!

[@shingomcleod]

Genuinely one of my favourite ever videos from you, didn't want it to end! I'm glad it was a flex day here as I laughed out loud on a number of occasions! Also great shoutout to Reza, top man. Any thoughts on the 5k 'List View Threshold' in SharePoint and Indexing columns (from a PowerApps perspective) ?

[@ShanesCows]

Glad you enjoyed. Typically the SharePoint limits don’t hurt our apps but large lists do get slow. You can add an index up to 25k items I think that may help.

[@funkyfoenky]

You're my powerapps hero :D

[@Carlog5290]

this is really helpful. Thanks.

[@ShanesCows]

Glad to help. 😀

[@javiersantos4089]

You are awesome Man!

[@ShanesCows]

You rock!

[@silvermorrah722]

Thanks for this video, Shane. Really really helped!!!

[@ShanesCows]

Awesome. Glad to help.

[@georgewinters2066]

Wonderful demo !

[@ShanesCows]

Thanks George

[@khrystinameier2981]

Hi Shane, Thanks for all of your help. Is it possible to first filter and then search?

[@ShanesCows]

Yes Power Apps Search and Filter Function with SharePoint + Workarounds kzbin.info/www/bejne/oorMY2eloL13htU

[@khrystinameier2981]

Thank you so much!!@@ShanesCows

[@jennyo6326]

Yes the Delegation maze! - Need to have a page with all the delegable stuff - yes the matrix

[@ShanesCows]

It is a pain for sure

[@Mary-AnnRothemund]

Hi Shane, thanks for your great video's. I don't have delegation issues if I use SQL as a data source but I was wondering if I might run into delegation issues if I change my Power App Connectors to a Data Lake instead of querying SQL tables directly. I was hoping you would have had some experience in using Data Lakes as a data source with Power Apps. Any information you can share would be greatly appreciated. Thanks!

[@ShanesCows]

You are going to just have to try it. Every data source is different when it comes to what is and is not delegable. Sorry, no quick answer here.

[@alexotoous]

Do we have pagination in powerapps ?

[@ShanesCows]

Not a setting you can control but it does get items 100 at a time in most cases, like in a gallery.

[@pathumjayasuriya5902]

Hi Shane, What's the best data source to use with power apps, SharePoint lists or SQL database. Best in the sense performance, efficiency.

[@ShanesCows]

Sql works better but requires a premium license. If you don’t have a premium license then SharePoint is the best.

[@anirudsaichenna3158]

Thank you for Shane your great video as usual, when ever I ran into a difficulty in powernaps your videos are the first thing which I look for the help and I get my answers in every situation , Here I have one question in continuation to this video, May i know if there is any best way to handle data source with about 10K items without having to use the premium connector. appreciate your response to my query

[@ShanesCows]

SharePoint is okay with 10,000 items you just need to be smart about how you present and filter the data. :)

[@richardhubbarduk]

Thanks for great way you communicate everything in your videos. I am trying to increase the delegation limit from 500 to 2000. All references to do this point to File -> Advanced Settings etc. What is the mechanism when using Power Apps in a browser? I must be missing something big time because I can't even find the means to use a desktop version which would have the File option. Any pointers gratefully received.

[@richardhubbarduk]

Ignore me - I have found what I was looking for - couldn't see what was right in front of me.

[@ShanesCows]

Glad you got it figured out Richard.

[@ABCD-ABCDEFGH]

is there a workaround for contains or in operator or greater than less than operator??

[@ShanesCows]

All of the workarounds I use are in this video. Power Apps Search and Filter Function with SharePoint + Workarounds kzbin.info/www/bejne/oorMY2eloL13htU

[@crystalhowat4458]

Great video Shane. Any tips to get around the item limit on a dropdown or combo box?

[@ShanesCows]

No, I haven't really had requirements where they were an issue :(

[@richardclowes6166]

Thanks for the explanation. So far, not an issue with my smaller SP Lists - but it will be with my next project. If I need to request our company allows either CDS, SQL, Azure, etc.. what would you recommend I ask for that will present the least amount of issues.

[@ShanesCows]

CDS is the best datasource 😀

[@Loves-f3y]

I'm looking for a way to get data into PowerApps that is not linked. Does that show a level of misunderstanding about PowerApps? I'm starting to think that there is no such thing as data residing in PowerApps.

[@ShanesCows]

You can "import from Excel" to permanently get some data in Power Apps. kzbin.info/www/bejne/ipqph4Ruitedeq8 Hopefully that video helps.

[@przemekbil7310]

Very helpful, thanks!

[@ShanesCows]

You are welcome 🙏

[@tonya3308]

Is there a way to get the count for the entire dataset without a gallery? So if we wanted to see that there are 8000 red records out of the 25K - is there way to do this?

[@ShanesCows]

Not that I can think of. 😐

[@CuratedHistories]

Can you create a search where you might enter into the search something like State, County, zip code (so it would look like this TX, Travis, 78748) and then have the search bring up all companies that are in Texas in Travis County in the 78748 zip code? Does that make sense? By the way your videos are amazing thank you for doing them they have helped me so much, this is my first time using Power Apps and definitely my first time creating an app.

[@ShanesCows]

Hi Amy - Give this video a look for some ideas. kzbin.info/www/bejne/oorMY2eloL13htU I don't think you are going to do exactly what you want but maybe you can get some ideas of ways to do better. :)

[@myama9688]

Hello Shane, what if I want to filter by column which is not blank/empty? Is there a way to workaround !isBlank or !isEmpty ?

[@ShanesCows]

Not that is delegable that I can think of.

[@MrFrbohn]

I have a NewForm for a sharepoint list item (not a gallery) where I am getting the yellow triangle. There is only one record in the list for testing. The warning directs me to Item: If(IsBlank(SharePointIntegration.Selected) || IsEmpty(SharePointIntegration.Selected),First([@TechnicalReviewRequests]),SharePointIntegration.Selected). Is this a potential problem that will bite me in production? Thanks for all your hard work on these videos.

[@ShanesCows]

Nope. That is just some code they did to make the form show data in studio. You can ignore.

[@Damski748]

HI, great video once again, a question though, when you hit the delegation limit which 500 rows are returned? the last 500 created, the first 500 or just a random 500? if it's the last 500 created I can live with it, if not I've got to fight on!

[@nicoletimmons2344]

Go to the 7:30 mark where he answers this: it's the first 500 results.

[@ShanesCows]

Thanks Nicole. She is correct. It is the first 500

[@dbschenkersingapore5542]

hi, how do you collect a filtered sharepoint library into powerapps? I want to only collect recently created files by creating a calculated duration column and using Collect(Collection, filter(Library, Duration < 14). I still get a delegation warning, any advise? thanks.

[@dbschenkersingapore5542]

I only want the recent items as the library has more than 2000 items

[@ShanesCows]

Interesting. I don't have a quick answer here. What if instead of using hte calculated column you wrote your filter to figure out the items itself? Or maybe a flow that calculate and write the duration to a number column when the item in SharePoint is edited?

[@DataisKing]

I'm trying to display employee names from a combobox in Dataverse table that has more than 2000 names. Any tips to retrieve this data?

[@jnspeters]

Super helpful! Thx! How about when it comes to filtering users? We have over 1,000 and trying to filter by department isn't showing all results.

[@AmazingHandy]

You should make a video explaining how to convert a share point list into sql or cds.

[@erikbrown9138]

I did this over the last few weeks. I moved an app that was using Sharepoint Lists to SQL. it wasn't impossible, but it wasn't fun.

[@ShanesCows]

I have a an intro to azure sql video. In that video I show how to create sql from Excel. Export SharePoint to Excel then Excel to sql. 😀

[@ryman3399]

Azure Data Studio makes this very easy also.

[@adamshrinah2595]

Thank you !

[@ShanesCows]

You're welcome!

[@vishvajitgirase7572]

Your videos are very informative and you reply every single comment on your video I really appreciate it. Thank you so much for creating such a beautiful playlist. I have one doubt, As a developer while programming we use git for integration and branch creation so can we create separate branch for each developer in power apps so that developer can collaborate while developing?

[@alwayswalter]

Excelent video! thanks. I am very newbie and just follow your tips in many situations. I have done tests with First(Filter(SharepoinList, ID = MyVariable))... I always get the nasty triangle, but all my tests have work fine on larger datasets... should I worry for a future colapse?

[@ShanesCows]

Replace First(Filter with LookUp( instead. 😀

[@alwayswalter]

@@ShanesCows I am going to check that... thanks a lot!

[@alwayswalter]

@@ShanesCows Oh my... now I got confused, as my scenario relies on multiple Patch(SharepointList, First(Filter(SharepointList, ID = Variable)), {somefield: something})... but as I can read LookUp may return a value, not a record as patch will expect... please feel free to hammer me if I am getting this wrong... :S

[@alwayswalter]

I wont bother you anymore I promess (for a while at least). Just let me know if is it fair to replace my First(Filter(SharepointList, ID = Variable)) with {ID: Value(SomeGallery.Selected.ID)} for example. I mean is it ok to "construct" the base record this way or will I get delegation problems? Again my test worked fine in a 2800 items list...

[@syedabidaliabdi1273]

Hi, I am new in PowerApps. I developed canvas app. On visible it takes user location from user table via lookup formula and add into combination of ID column in PowerApps form. But suddenly it's stop working. While list containing only 4 records.

[@ShanesCows]

I am not sure. Try simplifying your formula and see if you have any luck

[@syedabidaliabdi1273]

I did it but still not working.

[@syedabidaliabdi1273]

Issue resolved with some other method. thank you

[@vonis22]

Hey Shane, do you know a way to better handle negative queries? and Not arent delegable to Sharepoint. But when one wants to exclude all records with a specific choice field for example, you have to filter positively by each and every other choice available, just to exclude the one. Any ideas ?

[@ShanesCows]

Vincent sadly I don't have a better, delegable idea :(

[@mikelevytskyi]

Hello Mr.Young. Not getting into delegation problem. I am looking for a gallery to display more than 100 records without scrolling to the bottom every time. Is there a way to do that ? Because I got a little less than 200 records :) Thank you !

[@ShanesCows]

Only idea I have is you could collect the 200 items and then show the collection in the gallery. I don't know how to change the 100 item limit directly though.

[@reinacp140]

Great content Shane! I saw another video for a workaround delegates, wanted to know your thoughts about it. Using With: With( {wFilter: Filter( 'Data Base', Month=varSelected.Month ) }, CountRows(wFilter) ) What do you think about this solution?

[@ShanesCows]

With is not delegable. It doesn't give you the warning but if you test it you will see it is not delegable. Sorry. This video does have some things to help work within the rules of delegation more creatively. kzbin.info/www/bejne/oorMY2eloL13htU

[@reinacp140]

oh my! here I was so happy that I didn't have the triangle anymore! but you're right, I set my maximum delegation limit to a lower value than my data rows and did not work... I have watched that video and many more, but im basicacly stuck trying to countrows and sum multiple columns. What do you sugest? building a powerautomate flow that does for me?

[@camillebabylas7654]

Hi Shane. Is there a limit in the drop-down lists? I don't have all my recordings in my drop-down list while there is everything in my gallery ? Thank you !

[@ShanesCows]

I haven't ran into it before but I think i heard 500 items? Not sure though. Sorry

[@damian1228]

Love your videos and how on point they are with my problems. One thing I would like to ask. I have a sharepoint list with 10,000 items and a table with a filter that returns one day worth of data or about 20 items. I have a yellow exclamation symbol because there is a sort (of the 20 items filtered) by a field that's an option set which is not delegable (a drop down). Is that ok because the filter is delegable and returns 20 items which then are sorted by a non-delegable field? Again, thank you for your videos! SortByColumns(Filter('Daily Activity', EventDate = DatePicker2.SelectedDate), "Priorities")

[@ShanesCows]

Double check it, but yes, in my head you should be good.

[@julireid9574]

Maybe you have a good suggestion for this. I need to exclude some items from my SharePoint data source so they don't show up in the gallery. The option I am using is giving me the ugly delegation warnings. I am using value of a SharePoint lookup column: Status.Value "Discarded". From what you have said and Reza said AND the documentation, this shouldn't be giving me the delegation warning.

[@ShanesCows]

What if you do !(Status.Value = "Discarded")

[@julireid9574]

Shane Young same thing except the blue lines are now under the !. I even tried using the .id = 4 and got the same thing.

[@briangibson5981]

Hi Shane, I'm getting close to the 2000 limit and my users are used to having a search box. I'm using a Search as one of two Filter terms, currently have the warning triangle. Filter(Search(AAAAA,TextSearchBox1.Text,"BBBB"),CCCCC=Dropdown1_1.SelectedText.Value) Is there a correct way of organizing this so that the search function only operates on the filtered results?

[@ShanesCows]

Search(Filter(AAAAA,CCCCC=Dropdown1_1.SelectedText.Value) ,TextSearchBox1.Text,"BBBB") 😀

[@nocodeyuan]

Thanks for the wonderful video, the PowerApps tell SharePoint to get something example helped me to understand. Looking at the twitter thread and found more videos to watch. Why delegation has to be such rabbit hole! I simply want to countrows/countif to show how many records are in certain database. Is there an easy way to do that?

[@ShanesCows]

Nope. Sorry 😐

[@parulgarg90]

Thanks for the video. Considering And is not delegable, Is there any way to combine two conditions while filtering and still keep it delegable?

[@ShanesCows]

And is delegable

[@parulgarg90]

@@ShanesCows Even for sharepoint list? Cause powerapps keeps giving me error that it is not

[@paulinha-19]

Giant is the name of table, right? And "testVideoCollection" what is?

[@pggg5001]

Does ForAll(Filter()) work in the similar way to Collect(Filter())? i.e., Filter() will find ALL the qualifying items and return the first 500 to ForAll()? so as long as we are sure the Qualifying items are less than 500 we can just ignore the delegation warning?

[@ShanesCows]

ForAll( Filter doesn’t cause delegation by itself. Maybe your filter has a delegation challenge? But yes, should work the same as Collect

[@pggg5001]

@@ShanesCows I trying to patch a collection to a SP list all in one click, where I'm doing Filter(SPlist.id=collection.id)

[@jyothisrinivas6637]

Great video. Have a question, filters child drop-down based on parent drop-down values. Data source is dynamics 365 and function is filter and operator is '='. I got error message like object reference not set to an instance of object. Can you help me on this

[@ShanesCows]

You probably aren’t doing Dropdown.selected.columnname

[@JuanMagzul]

Hi Shane Young, I am from Guatemala and I started watching your videos, they are very interesting. I would like to know if you can help me... I am creating a data post form in Power Apps for a Sharepoint database. I have used a combo box where I make the data call in a sharepoint list, in this list I have more than 500 rows and when I show it in the combo box it does not show me everything. It only shows me 500 records. How can I fix that? I would like to see in my combo box more than 700 records. Thank you.

[@iektech]

Hi Shane and community, I'm wondering how would you handle an scenario with the same 25,000 items in a SharePoint list and be able to provide a search functionality in PowerApps that looks for coincidences in the same way other search boxes do with other technologies, in other words use "Contains" comparissions, due to not being feasible to use "in" to filter SharePoint datasource and also not feasible to pass the complete data to a collection and then apply the filter, what approach would you take to provide the required functionality while keeping the data in SharePoint. Another scenario, just for those who want extra points :), is when you have to apply multiple column filtering on your data source at different times within the same page, would you do this every time directly to the datasource, some time ago I thought coping the data into a collection and apply the filters there would help you to prevent the unncesesary queries to the data source, however I found that collections are not coping all the data due to the reasons explained in this video, so how would you appoach an scenario like this?

[@Alan.Moffat]

I had to add drop down filters by column so it would give me suggestions.... instead of filtering the whole data set.

[@Ylotan]

Hi Isaac, you can bring your data into collection, collections don't require delegation because all of this data is already in memory. The trick is to be able to split your dataset into collectable segments which are below threshhold.

[@iektech]

Thanks @@Alan.Moffat, I'm wondering how do you get the suggestion values for the dropdown?, isn't this requiere additional queries to the datasource to find the exisiting values? if yes then we may have the same problem with that query too.

[@iektech]

Thanks @@Ylotan, however ensuring such fit in the collections will be hard due to the continuos growth of the datasource, the filters you use one day to fit in the collection may not be specific enough a couple days later, even if they fit, the challenge is to use a search box to filter the complete datasource (25,000 in this example), so the way I imagine this by following your approach is to preload the collections in a previous screen and combine then in a single collection, still this approach has the problem of not showing live data and also the performance issue that Shane mentioned.

[@ShanesCows]

Nice ideas here.

[@hahiwa8060]

Ok thanks, i do now understand why, and i know either i've got more work to do ;) but i've got a question about the forms, does it mean it is preferable to use patch function on a sharepoint list wich contains more than 500 or 2000 lines? Because you charge the list on the phone to complain it.

[@ShanesCows]

Delegation doesn’t affect writing data. Mostly just filtering data

[@pushpalatha9351]

Hi Shane, I am using SQL server as a data source, the table contains around 4000 records so I created View & restricted the data by adding filter condition inside View. So, If the user added comments in comments box in Power Apps, Can we store the comments data in View based on Click on Save button in Power Apps?

[@ShanesCows]

No, you have to write to a SQL table with a primary key, then you could have that table feed the view, but you can't write directly to a view.

[@MrZeeshanstudent]

Hi thanks for nice information about delegations. I have one question if we user excel spreadsheet as data source can we filter more than 2000 records in this situation without any loading everything in memory?

[@ShanesCows]

Filter doesn’t have issues with delegation usually so should be okay

[@maryme1996]

Is this uptodate? I thought "in" and "and" Operators work now with Delegation right?

[@ShanesCows]

I think And is always delegable but In is not. I am sure for SharePoint that is the case.

[@28shreyb]

I need to filter out empty dates in SharePoint list so i need a substitute for IsBlank() and Not() functions, Filter( [List Name], Not(IsBlank(Column Name)) )

[@ShanesCows]

ColumName = Blank() is delegable. 😀

[@v75tan]

How for datasource SQLServer filter works : collectionValues IN MyKeyField >> Eg Filter ([dob].[Giant], ("01","04","07" in MyKeyField) Please help

[@ShanesCows]

Not sure

[@wenyantsai]

Wonderful video ! One question about the 500 item limit. I use an Excel file as the data source of PowerApps and there are 3 tables in this excel file. Does the 500 item limit refer to the number of items in each table or the the total? thanks

[@ShanesCows]

Each table

[@garvinchiwaridzo9478]

Hi Shane. Does this error: "Server Response: An error has occurred. Object reference not set to an instance of an object." have anything to do with the amount of data in data source.=? I can't seem to get around it.

[@ShanesCows]

It shouldn’t. I don’t see that error often. Maybe start a new app, add a galley, then add that data source. Start simple and see if you can figure out where it breaka

[@joseluiscolladosoriano9640]

Thanks for this video, Shane... Question, when you need to filter any folder by path from a Sharepoint Document, how can i delegate on Sharepoint to request only records I need?

[@ShanesCows]

I don't know. I played with that one day for 10 minutes and I also go delegation warnings with all of my ideas :( and then I gave up. If you figure it out you would be a hero!

[@hamzeesious5059]

Can you please let me know the Data Rows limit in Sharepoint list and CDS?? Thanks very much in advance

[@ShanesCows]

I don't know them off the top of my head.

[@hamzeesious5059]

@@ShanesCows any rough idea regarding SharePoint list limit? I am searching for it for so long🙁

[@avaniejain5733]

Thanks for the very informative video. In my case, I am trying to filter out a gallery items on the basis of the label (True/False) associated with it. I want to created another filtered gallery where I can have all the contents of Gallery A having Label “True”. I am able to write formula for this but the issue is my filtered gallery is only showing the items when my original gallery is scrolled down till bottom manually. Can you please help me out?

[@ShanesCows]

You can’t use AllItems this way without that exact issue. Maybe replace it with your filter formula.