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.
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
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.
😂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!
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.
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.
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.
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!!
@@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 ✌️
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!
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
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) ?
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.
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?
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 !
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!
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!
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.
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.
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
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.
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.
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.
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?
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!
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!
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?
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
Hi Shane, thanks for another great video. You show the text box with the countrows function to illustrate your point. My problem/question is trying to get that number to accurately represent the number of rows. I am trying to pull in an activity view from CDS/Dynamics to put into a graph. I did just what you did and used that text box in my graph formula. However, since that number isn't accurate... that method doesn't work. Any thoughts on achieving this. Literally just trying to pull in one number from dynamics and it's giving me a world of trouble. Thanks so much!!
Hi Shane. I watched the whole video, thank you very much for the explanation. I wanted help. I need to use the sum function, but it is not delegable, the dreaded triangle appears :( I found several places talking to use the Filter inside the sum, but I didn't understand how. Currently my formula looks like this: Sum (Powerapps_relat_mov_ibc; DT_Etapa.Value = "Decontamination" And Status_temp = "PauseDescont"). How do I use the filter? Thanks
@@ShanesCows Thank you Shane. Just one more doubt. The age part, is where will you save? a label for example? I've seen similar formulas, but I couldn't understand what comes after the filter, what do I put there?
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.
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 !
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.
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.
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
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 ?
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.
Hi Amy - Give this video a look for some ideas. ruclips.net/video/lYi24okXDPs/видео.html 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. :)
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")
When using a boolean column in Sharepoint, I had trouble with delegation. Technically, these are the same, right? If(BoolCol, ...) VS If(BoolCol = true,...). When filtering, this works but I get a warning: Filter(Giant, BoolCol). I get no warning when I use: Filter(Giant, BoolCol = true).
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?
Maybe but even it lets you it isn't going to go well. That is too many columns. Maybe look at this video for a different consideration. ruclips.net/video/7XXo9wjnJvc/видео.html
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.
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?
All of the workarounds I use are in this video. Power Apps Search and Filter Function with SharePoint + Workarounds ruclips.net/video/lYi24okXDPs/видео.html
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?
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.
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.
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.
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?
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
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.
@@ShanesCows, he could have use Flow / Power Automate from Power platform. Flow has connectors for SAP and SharePoint. However it's best to use SSIS. Hope this helps.
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)) )
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.
Thank you so much Shane. The Company where I'm working on , asked me to develop a full free app therefore I'm using excel as Datasource (it is my Database :-( ) . If I have understand corretly, the Collect sentence, is able to go over 2000 rows. In my app I'm filtering the Database by week to give the user to modify the data in that specific week, in case the filtered week is situated in a row that is more than 2000, can the user modify it?
Collect will not get more than 2000 rows back without some craziness I don't recommend. You can modify items in a collection but that just modifies them in the collection, not the data source. If you want to change the datasource you will need to modify it using a form or Patch for example.
Thank you so much Shane. In reality I saw the possibility to add row over 2000 and because I’m filtering by week it seems that all is working properly. Let me cross my fingers an thank you again 👍🏼👍🏼
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 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
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...
thanks nice one...can I create a function to filter by ID Greater than and that should be Delegate one ?Filter(MoreThank5KRecords, ID > Value(txtId.Text)) this is not returning item more than 500 limit.
Hmmm I would of that would work but if you are seeing it doesn’t then boo. Check putting a number in place of the value function just to see if that matters
Hi Shane Thanks for the video. I am getting a delegation error on the Item formula created by PowerApps, that selects the first item in the list - for the SharePoint form. It does not like First as it can't be delegated. I have tried even creating a blank OOTB list and it still does it. Have you seen this and logically does it matter? If(IsBlank(SharePointIntegration.Selected) || IsEmpty(SharePointIntegration.Selected),First([@'LitstName']),SharePointIntegration.Selected). I don't intend to use PowerApps for searching as I am using SP library views to access the Edit form as I am using a filtered SP view. Do you mind commenting?
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?
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?
Great vid!! I seen that 'in' cant be used when the data source is SharePoint (delegation waning will appear). Is there any alternatives to this? My function is trying to check if the logged in user email is within a muti-person column in Sharepoint.. LookUp(Staff_List,userEmail in Manager.Email)
Too bad Power Apps can't consume SharePoint views. That would be a great way to exclude items if there were certain items you never wanted to show up in your list, but it's based on a complex column or something like this.
Hi Shane , I am having an sql backend of 60000 to 80000 records how can i paginate over 2000 records as there is a limit in powerapps gallery with pagination. Regards, Bhanu
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?
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?
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?
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. ruclips.net/video/lYi24okXDPs/видео.html
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?
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.
Thanks for the kind words Shaun. I am glad to do my part. 😀
Exactly, and still make it fun! Feels like i´m talking to a funny expressive friend.
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
Glad to help Roberto.
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.
Wow, thanks!
😂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!
Thanks for sharing. 😀 so many silly things have been done with SharePoint 🤩
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.
🥰 Awesome. You got this.
"SharePoint's like "Nah baby, nah - I don't do that!"" Ha -- I love it! Thanks for another very informative (and entertaining) video!
Glad you enjoyed it! 😂
Wow.... Now we have so much work to do ! We have many collections working with delagation filters! 😭
Thanks alot Shane!! You’re the best!
You are welcome 🙏
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!
Awesome 🤩 😎☺️
This is crazy helpful man!!!! Your content, your voice, your expression, that's super awesome. I watched entire a video without skipping!
Awesome, thank you!
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.
YEah, distinct just is not delegable. SOrry
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.
Awesome Robert.
Again amazing Work. I'm losing a lot of my fear of PowerApps because of these great videos.
That is awesome 😎
Another hit video!... Thank you!!!... Still breathless by keeping up the pace 🤠👍
More to come!
Hi Shane. Just wanted to let you know that I found this video SUPER useful! Thank you so much
Awesome. Glad to help. 😀
Woop Woop! Thanks Shane! We can always count on you.
Happy to help!
Great content to get a hold on the delegation concept. Keep doing this great work.
Glad to help
You are such a great teacher Shane, thank for all your videos.
My pleasure!
This is so good to know and your methodology of trying it in different scenarios helps a lot!! Thank you 😊
You're so welcome!
Thx Shane... Just used your tips to remove "'stupid yellow triangles" from my prototype!
Awesome!
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!!
Fantastic!
Hello. I think I’m facing the same problem at the moment and I can’t seem to figure it out
@@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 ✌️
@@mlm918 yes it is
Thank you👏🏾
this video is extremely helpful, I have been fighting the default license limitations and delegation issues. Thank you sir!
Happy to help Joe
Thanks for sharing more insight on delegation around multiple data sources
This video is extremely helpful and Shane you did an excellent job in explaining....
Thanks 😊
Awesome explanation on yellow triangle, Shane
Thanks 😎
Fantastic video. Your style is engaging, informative & entertaining. Thank you!
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!
Congratulations and thanks for sharing
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
Agree. CDS is best but not everyone has the license for that.
Good video, very clear!
Just started with PowerApps and I am amazed what you can do with it.
Thanks 😊
Nice one .. Especially how you explained delegates. Was pretty easy
Thanks 😊
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) ?
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.
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?
Search(Filter(AAAAA,CCCCC=Dropdown1_1.SelectedText.Value) ,TextSearchBox1.Text,"BBBB") 😀
Another AWESOME! Video Keep it up man.
Thanks! Will do!
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 !
I haven't ran into it before but I think i heard 500 items? Not sure though. Sorry
Thank you very much for this, I've been deleting my sharepoint data everytime after reaching 2000 record. This saves me
Glad it helped!
Do we have pagination in powerapps ?
Not a setting you can control but it does get items 100 at a time in most cases, like in a gallery.
Thanks so much! You just helped me remove two very annoying delegation warnings on my app!
Awesome!
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!
Bro you are awesome. Literally my hero
Thanks 😎🐶
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!
Go to the 7:30 mark where he answers this: it's the first 500 results.
Thanks Nicole. She is correct. It is the first 500
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.
Nope. That is just some code they did to make the form show data in studio. You can ignore.
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.
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
Hi Shane,
What's the best data source to use with power apps, SharePoint lists or SQL database.
Best in the sense performance, efficiency.
Sql works better but requires a premium license. If you don’t have a premium license then SharePoint is the best.
Great video Shane. Any tips to get around the item limit on a dropdown or combo box?
No, I haven't really had requirements where they were an issue :(
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.
CDS is the best datasource 😀
Still helpful after 3 years!
This should be on my list to do again. 😎
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.
Ignore me - I have found what I was looking for - couldn't see what was right in front of me.
Glad you got it figured out Richard.
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.
You can "import from Excel" to permanently get some data in Power Apps. ruclips.net/video/TiFVQ8UrjDM/видео.html Hopefully that video helps.
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?
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?
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!
Hello Shane, what if I want to filter by column which is not blank/empty? Is there a way to workaround !isBlank or !isEmpty ?
Not that is delegable that I can think of.
You should make a video explaining how to convert a share point list into sql or cds.
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.
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. 😀
Azure Data Studio makes this very easy also.
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!
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.
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?
Not that I can think of. 😐
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
SharePoint is okay with 10,000 items you just need to be smart about how you present and filter the data. :)
Hi Shane, thanks for another great video. You show the text box with the countrows function to illustrate your point. My problem/question is trying to get that number to accurately represent the number of rows. I am trying to pull in an activity view from CDS/Dynamics to put into a graph. I did just what you did and used that text box in my graph formula. However, since that number isn't accurate... that method doesn't work. Any thoughts on achieving this. Literally just trying to pull in one number from dynamics and it's giving me a world of trouble. Thanks so much!!
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.
Hi Shane. I watched the whole video, thank you very much for the explanation. I wanted help. I need to use the sum function, but it is not delegable, the dreaded triangle appears :( I found several places talking to use the Filter inside the sum, but I didn't understand how. Currently my formula looks like this: Sum (Powerapps_relat_mov_ibc; DT_Etapa.Value = "Decontamination" And Status_temp = "PauseDescont"). How do I use the filter? Thanks
Sum(filter(datasource, chewy=“dog”), age)
@@ShanesCows Thank you Shane. Just one more doubt. The age part, is where will you save? a label for example? I've seen similar formulas, but I couldn't understand what comes after the filter, what do I put there?
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.
Delegation doesn’t affect writing data. Mostly just filtering data
Thanks for this video, Shane. Really really helped!!!
Awesome. Glad to help.
excellent video... great job! i've learned ALOT by watching your videos.
Awesome 😎
Yes the Delegation maze! - Need to have a page with all the delegable stuff - yes the matrix
It is a pain for sure
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 !
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.
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.
What if you do !(Status.Value = "Discarded")
Shane Young same thing except the blue lines are now under the !. I even tried using the .id = 4 and got the same thing.
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
Each table
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 ?
Vincent sadly I don't have a better, delegable idea :(
Hi Shane, Thanks for all of your help. Is it possible to first filter and then search?
Yes Power Apps Search and Filter Function with SharePoint + Workarounds
ruclips.net/video/lYi24okXDPs/видео.html
Thank you so much!!@@ShanesCows
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.
Hi Amy - Give this video a look for some ideas. ruclips.net/video/lYi24okXDPs/видео.html 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. :)
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")
Double check it, but yes, in my head you should be good.
When you say "Azure SQL", are you talking about SQL Managed Instances?
SQL database as a service. But all sql is the same.
Thanks for the video.
Considering And is not delegable, Is there any way to combine two conditions while filtering and still keep it delegable?
And is delegable
@@ShanesCows Even for sharepoint list?
Cause powerapps keeps giving me error that it is not
When using a boolean column in Sharepoint, I had trouble with delegation. Technically, these are the same, right? If(BoolCol, ...) VS If(BoolCol = true,...). When filtering, this works but I get a warning: Filter(Giant, BoolCol). I get no warning when I use: Filter(Giant, BoolCol = true).
I think the yes no column in SharePoint has some weird behaviors
@@ShanesCows It does...
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?
Nope. Sorry 😐
Hi Shane..I have a dataset with nearly 450 columns. Can I use Sharepoint lists to connect to Powerapps in this case?
Maybe but even it lets you it isn't going to go well. That is too many columns. Maybe look at this video for a different consideration. ruclips.net/video/7XXo9wjnJvc/видео.html
@@ShanesCows Thanks Shane...that was helpful
@@ShanesCows Will it be worthy if I use a single table with 450 columns from a Sql database ?
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.
I only want the recent items as the library has more than 2000 items
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?
Fantastic video, thanks Shane.
Glad you enjoyed it
Is this uptodate? I thought "in" and "and" Operators work now with Delegation right?
I think And is always delegable but In is not. I am sure for SharePoint that is the case.
is there a workaround for contains or in operator or greater than less than operator??
All of the workarounds I use are in this video. Power Apps Search and Filter Function with SharePoint + Workarounds
ruclips.net/video/lYi24okXDPs/видео.html
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?
I had to add drop down filters by column so it would give me suggestions.... instead of filtering the whole data set.
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.
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.
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.
Nice ideas here.
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?
Filter doesn’t have issues with delegation usually so should be okay
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
You probably aren’t doing Dropdown.selected.columnname
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.
How for datasource SQLServer filter works :
collectionValues IN MyKeyField >> Eg Filter ([dob].[Giant], ("01","04","07" in MyKeyField)
Please help
Not sure
Giant is the name of table, right? And "testVideoCollection" what is?
Yes, how to do ETL from Excel table with 100k rows to SharePoint and be usable in powerapps
I think I would look at PowerShell if you have that much data. Not sure. That is tough
@@ShanesCows I'm asking since it suppose to be easier than to move SAP big tables to SharePoint ;)
@@ShanesCows, he could have use Flow / Power Automate from Power platform. Flow has connectors for SAP and SharePoint. However it's best to use SSIS. Hope this helps.
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))
)
ColumName = Blank() is delegable. 😀
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.
I am not sure. Try simplifying your formula and see if you have any luck
I did it but still not working.
Issue resolved with some other method. thank you
Thank you so much Shane. The Company where I'm working on , asked me to develop a full free app therefore I'm using excel as Datasource (it is my Database :-( ) . If I have understand corretly, the Collect sentence, is able to go over 2000 rows. In my app I'm filtering the Database by week to give the user to modify the data in that specific week, in case the filtered week is situated in a row that is more than 2000, can the user modify it?
Collect will not get more than 2000 rows back without some craziness I don't recommend. You can modify items in a collection but that just modifies them in the collection, not the data source. If you want to change the datasource you will need to modify it using a form or Patch for example.
Thank you so much Shane. In reality I saw the possibility to add row over 2000 and because I’m filtering by week it seems that all is working properly. Let me cross my fingers an thank you again 👍🏼👍🏼
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?
Replace First(Filter with LookUp( instead. 😀
@@ShanesCows I am going to check that... thanks a lot!
@@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
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...
thanks nice one...can I create a function to filter by ID Greater than and that should be Delegate one ?Filter(MoreThank5KRecords, ID > Value(txtId.Text)) this is not returning item more than 500 limit.
Hmmm I would of that would work but if you are seeing it doesn’t then boo. Check putting a number in place of the value function just to see if that matters
Hi Shane Thanks for the video. I am getting a delegation error on the Item formula created by PowerApps, that selects the first item in the list - for the SharePoint form. It does not like First as it can't be delegated. I have tried even creating a blank OOTB list and it still does it. Have you seen this and logically does it matter? If(IsBlank(SharePointIntegration.Selected) || IsEmpty(SharePointIntegration.Selected),First([@'LitstName']),SharePointIntegration.Selected). I don't intend to use PowerApps for searching as I am using SP library views to access the Edit form as I am using a filtered SP view. Do you mind commenting?
I don’t think it will matter. 😀
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?
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.
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?
ForAll( Filter doesn’t cause delegation by itself. Maybe your filter has a delegation challenge? But yes, should work the same as Collect
@@ShanesCows I trying to patch a collection to a SP list all in one click, where I'm doing Filter(SPlist.id=collection.id)
Great vid!!
I seen that 'in' cant be used when the data source is SharePoint (delegation waning will appear). Is there any alternatives to this? My function is trying to check if the logged in user email is within a muti-person column in Sharepoint..
LookUp(Staff_List,userEmail in Manager.Email)
I don’t think that is ever going to be delegable 😑 Need to think about a different approach. Maybe store usernames also in a text field?
That's what I ended up doing in the end. For future projects I will make sure to use databases over SharePoint haha. Thanks for the help :)
Gracias por esta ayuda...!!! Greatings from Chile
Thanks Marco! 🐶
Too bad Power Apps can't consume SharePoint views. That would be a great way to exclude items if there were certain items you never wanted to show up in your list, but it's based on a complex column or something like this.
That would be cool!
Hi Shane ,
I am having an sql backend of 60000 to 80000 records how can i paginate over 2000 records as there is a limit in powerapps gallery with pagination.
Regards,
Bhanu
Power Apps loads the records 100 at a time but in theory should be able to scroll to the end assuming the browser doesn't crash from the load.
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?
You can’t use AllItems this way without that exact issue. Maybe replace it with your filter formula.
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?
Can you please let me know the Data Rows limit in Sharepoint list and CDS?? Thanks very much in advance
I don't know them off the top of my head.
@@ShanesCows any rough idea regarding SharePoint list limit? I am searching for it for so long🙁
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?
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. ruclips.net/video/lYi24okXDPs/видео.html
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?