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.
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!
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 ✌️
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.
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'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!
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
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.
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?
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.
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!
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?
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")
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!
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.
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. ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-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. :)
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...
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
@@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.
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!
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, 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 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?
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. 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?
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).
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 !
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.
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. ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-TiFVQ8UrjDM.html Hopefully that video helps.
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?
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
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)) )
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 ?
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
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?
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. ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-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?
Great Video ... I m New in this PowerApps world .. I have problem with filtering. with your video I can filter if UserName = Displayname (Modifed sharepoint list with flow from person to Line Text to another column), but ......delegation problem comeback (Aaaargh) when I dont want exact value ex Username contain Display name. I have column Inter disicplin check (IDC-multiple person to check one document)., and in powerapps I already setup when user open application... only person/user task will appear. when he task is to IDC I can't filtering that column if contain user UserName/DisplayName.. aaah I'm bad story teller. Can you help me (if you understand what I'm saying - English is not my native language) If bottom line is, how to filter if one column contain certain text I want, not exact text I want without make delegation monster appear
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?
All of the workarounds I use are in this video. Power Apps Search and Filter Function with SharePoint + Workarounds ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-lYi24okXDPs.html
Thanks for the great video I do have a question i cant seem to solve, is it solvable? I just want to ClearCollect all the files in a sub-folder in a document Library. Say For example; This folder only has 10 PDFs in it, but the entire library has 50000 items.