PowerApps filter gallery by dropdown

in today's show when we learn about power-ups filter a gallery by drop-down so this comes up a lot and so what we're gonna do is we start the real simple scenario we're just filtering by drop-down but then people say hey I want to add a blank row or an all rows so that way I can see all the data at the same time so we're gonna talk about that then we're going to filter by to dropdowns at the same time pretty cool and then finally we're going to wrap all that and be all search function so that we can also do some rich text searching at the same time should be a lot of fun but first here's our intro hi my name is Shane young with power apps nine-one-one those guys in today's show we're what power-ups filtering dropdowns all right so one of the things that comes up a lot is people hey I got a gallery data I got it I love it boom did they say all right I know how to filter the data I can I want to show all the active or inactive kind of you know a steadfast way but what if I want to give the users that ability to dynamically filter the data so they won't be able to go in and choose from different dropdowns type in some stuff and kind of get in there and so what I wanted to do today will just kind of go that next mile with you guys and talk a little bit about you know intermediate levels of filtering and then I'll set up the stage for a really advanced filtering stuff that maybe we'll do in another video but not today so was this Witcher 2 my desktop take a look at the demo app I built and then we'll talk about how to actually build it so okay so over here my desktop I've got four buttons make this nice and easy for us so let's see the first one right so this is your typical distinct filter and this is where you're okay I want to be able to filter this employees list and I'm gonna filter it by the different jobs right so here's all the people in IT here's all the people in finance but the biggest complaint is well what if I want to see all of those records that comes up so much let's show you how to do that OOP so over here I've got a button distinct filter plus all and so in this button you can see that right now I see all my data yay all the records but then if I'm like all right well now I just want see the people in accounting who knew I was in accounting I haven't doing tax stuff so I guess that makes me accounting I don't know but anyway so now you can see that we have that lovely capability but we can always just go back and select the all option and do that and so we're going to talk about how to use a collection and then dynamically add the all records so that'll be what we do for this one so then be able I got it that was great I really want to be able to filter by not only the department therein but people's favorite colors fair enough so we go over here to two drop downs and so by default once again I'm showing all my records yay but then I'm making you know what I want to only see people in the executive department that's the cola and Jennifer very cool and then I only want to see people whose favorite color is green that's just Jennifer so we're able to have two levels of filtering oh yeah Bank says show me all the people say Jennifer's only person the whole company has green but what if we said how about Balu I think there's some blue people there you go so Gregg and Jeff they both like blue so this is showing you what a lot of people really want right this ability to have a couple of different ones and so we're going to talk about how we do this and we do this with some if formulas on top of some other techniques that will warn when we got here and then last but not least for today two dropdowns plus search so now we have the ability to get in here and back hey show me all the people whose favorite color is red there they are but now what if I want to be able to search by the name so I started pinching and so now we've added search so pretty fun right we've got several different little pieces going on here so let's go look at kind of how all these work and just a reminder if you're a subscriber from over on training got power apps 9-1-1 comm you can download this app so you don't have to rebuild all this and in the app i've tried to kind of break it up a little comments here and there to make it easier for you guys that are you know subscribers cuz i really appreciate that so anyway alright so back over here let's go and let's just start a new screen right let's just start from the very beginning so that way we don't have to you know unravel what i did was just build it all together but we'll do it all in one screen okay so we're insert a gallery and what are we going to do for our gallery we're gonna use employees group there they are so there is all of our global employees and then we're just real quick change this to be first name here and then we're going to put in a department here and ryan we're just doing this so that way we can see the data and validate we don't have to use or show these fields i just want to go to validate that we were seeing the rights control C control V and so then pull this down here and so then down here we're gonna do here favorite color and in this case my data source is SharePoint and favorite color is a complex column somebody favorite color dot value right nothing here that we're going to do has anything to do with SharePoint other than that one little you know nugget there so this is not a datasource dependent video in any way alright so now we've got our people in here and so the first thing we typically want to do is we want to do a filter right and so what I'm going to do I'm gonna put a little drop down over here someone say hey give me a drop down and so then in the drop down I'm going to say alright I want to whatever sure there I want to show the employees list and weren't instead of using compliant asset ID let me use department alright so then that would show me basically the same data but just all the departments ok so then over here you can then say alright the items property for your gallery what we're gonna do is we run into a filter to filter employees and then we're going to say I want to filter employees where Department equals writing departments name of the column so Department equals drop-down one that's never dropped down there we go that's selected and then we want to do dot Department alright and once again your you know your list might vary but we're gonna have different things you're going to see it but there you can see that now we get the list and this works the way you'd kind of want the only downside here is that we've got executive listed twice because we have two people's executive so what we're going to do now is instead of doing items be employee we're going to use a function called distinct so distinct gets all of the employees soaks with employees table and since all right give me the distinct value out of department and so now when we do this everything should recompile here a second oh so now this formula is broken why is this formula broken the reason is because and this is a pathology right you're like hey why isn't this working I'm following exact what you did well the key here is if I let's just delete all would you select it and do dot it's going to show you right here right so the only valid value is result for me so we hit results and then it'll work and so that's going to show you the different columns being returned by your drop-down control and what happens is when you do a distinct function the distinct function you know it doesn't return the whole record so doesn't return all the columns it just returns a special column named results that happens to have just the value of you know in this case our department and so that's why now on the drop down it says executive if we hit the drop down we don't see that all record right we're gonna have to go add that that's meant but this is who onus all the different pieces and it works exactly the way we'd expect okay so that's the first one now I'm very important little side note here right distinct the distinct function is not delegate all right and if you're not familiar with that concept I'll put a link down below or maybe up there I don't know somewhere there's a link to the video I did on power apps delegation but what that means is that our datasource can't do the work for us our datasource can't find all the distinct values so instead what happens is by default power apps we did it's the first 500 records from the data source and then it looks for the distinct departments in there so if you had a scenario where you had a data source with 50,000 items then what would happen right is only the first 500 would be you know parsed to find the distinct values so distinct would not work very well on a large list like that but remember also you have you know we have a lot of flexibility here someone cut this out so control X and so I could just be like hey I know that the only valid departments here are executive and accounting right so we could just hard code these values in and everything that we were do in this video would work exactly the same right this is not valid anymore dot result that's fair what is it it's not value and so then now because I've got executive we should see Jennifer and Nicola again if I choose accounting we should see just me yeh so your drop-down doesn't have to use distinct but we're going to use distinct because this is what comes up the most for me the other thing to remind you guys about right we're gonna fix this again because now this is result the other thing to remind you guys when you're thinking about this is this just has to be a table so I just showed you how to hard code it but if I had a separate data source it was just the list of valid departments I could feed that in here because all our filter function is matching is text right this department field is a text field the drop-down selected result returns tax so it's saying does this these words meet match these words okay all right and really I guess I'll also put a link below there's a video on filtering the filter function and detail on its own so anyway all right rolling along so now we've got this working so now you guys like hey I want to add an all function right I want to be able to see all the records so we know this returns everything I want except for the all so I'm going to copy this and then we'll go up here to my screen I'm a-saying screen I'm visible right every time someone comes this screen I'm going to build myself a collection so I'm clear to collect so wipe out anything was there build a new fresh collection we're gonna call this collect video just so it's not confusing and so we're gonna say in collect video I want to store the distinct employees department and so if we do just this and so then we leave the screen we come back to the screen on visible fire we can then come in here and we would be able to delete this out and we would say I forget collect video there it is and so everything works exactly the way it did before because the collection is just those same records right if we double click on here we can see you don't tell us what's in there look there's our results like you'd expect so our collection is the first step in this puzzle right that it was an ability to give us all of the records in a collection and the reason I wanted you to do it in a collection is because what we're going to do now that we understand how that works is we're going to add another row before that we're gonna actually say clear collect clear collect and we're going to same collection right cuz I want to use it do and what do I want to do here I'm going to say we know that the column name is results I'm gonna say they recall name results to the text all like that and so then what do you mad about let's get rid of oh I didn't cause that should be a curly braket I'm like what did I do wrong there we go so critical I collect video boom so what that's going to do is create a or wipe out the collection and then we load the first record as all and then now we don't want to do a clear collect here we just want to do a collect so if we leave the screen come back to the screen now if we click on collect video you can see that in there is all and then executive through janitorial just like we've had every other time so then the awesomeness now is my drop-down shows all but you can see that when I choose all I get nothing but if I choose finance I get Greg right so how do we make the all work so what we have to do now is we have to go over here and do an if so we're going to do is real say if we're gonna say if drop-down one dot selected dot button the wrong way I'm so sorry did result equals all right so if they choose all from the list what do you want to do I just want to show them everything so I'm just going to show them the employees list if they didn't choose all then we know that choosing chose an executive janitorial or something else like that and so then in that case we would then filter employees by whatever they've chosen so now we see everybody right in akola through Daniel danos pictures terrible he's an ugly guy we go down here and we go to janitorial we see Jeff if we go to executive we see the ladies again finance Greg isn't not a finance person but whatever but so now you can see that with this if formula we're able to go that extra step right and the other thing I mentioned is so I always use like all but some people put dashes in there some people they want to do like completely blank you can do like a blank space but I gets really weird because in your testing for blank spaces so I typically either use all or use a - in my collection just to make it work a little better remember also here with your drop down we can set the default you know and so we'd say hey I want you to default to all just so that way you know you're tying up all your loose ends okay so that is filtering off of one now what we want to do is we want to filter off of two all right so what I'm gonna do is I am going to add a second drop down first so input drop down you know and while I'm doing this one of the things I want to remind you guys also here is that typically what I do is I build with these things I literally build stuff in the same exact order because I do it in baby steps that way I'm never trying to update everything at once I'm you know learn I'm like alright this works this works this works this works oh that doesn't work I don't have to go back very far because I only did one little piece at a time so another pro tip like I really do believe in baby pieces or tiny pieces I so what I'm gonna do is I'm going to do a filter or a mundo distinct here on favorite color value like this and so now you can see that I've got the red and we should just see red blue orange green purple yep everyone's favorite colors are not listed so I did this in the drop-down first to make sure that that was the right way and I could truly see what I wanted so because once again this is a complex SharePoint column and use a stop value alright so we'll copy this out Boop so then now we're going to go back to on visible and so I'm gonna make another collection so I'm gonna do this and I'm gonna say clear collect collect video how about colors like that what do we want to do we're going to do the same thing I'm going to put result and we're going to set that to all oh wait this want to - just to show you guys can see another way that I typically do it so we'll do that and that so that puts that in there and then I would say and then once you've got that first record in there I want you to collect video colors and what we're gonna do there we're going to just paste in our formula that we know returns the right data because we just tested it we're going to leave the screen we're going to come back to the screen and so now that we're back on the screen instead of distinct here this will be collect video colors all right so then now we've got all on this but this one currently doesn't do anything but it's at least over here and why I said we did the - just to be different so then now you gotta go over here and get a get a little fancier so before we had just a simple if right we were like hey if it's this scenario do this it's this scenario do this now we're actually going to need four different evaluations because you know exponential numbers blah blah blah but basically there's four scenarios here so the first scenario is going to be we're just going to wipe all this out so what's the first scenario the first thing I opt out one selected result is all and let's go ahead and just rename these dropdowns real quick let's change this drop-down to be DD departments Department and they're going to change this one to be DD color hope I've broken my app how rude alright so this is an issue I've been having well one of the things remember some of those power-ups this goes we'll walk here with you the easiest thing to do say file save and then close your app and then open your app again and now if we go back to screen1 DD color DD apartment everything's happy again it's just one of those weird things that's been happening the last few days for me and so I refer to that as rebooting power apps I mean it's how I reboot power apps so anyway alright so I want this one to be so if depart drop-down Department that selected that result equals all and drop-down color dot selected dot result equals a dash so if both of those are true what do we want to do then we just want to show the entire employees list okay and so what we should see here if we give it a second so we know our first if works and more importantly if I switch this to let's choose a color now Swiss cheese red now we see nothing because there's no formula to deal with that so then what I'm going to do is I'm going to come in here and back I so that one worked for that so then now what we want to do am i hitting shift inner as I'm going to say hey if drop-down departments of departments drop down deeper I got to spell these things right you know it really helps dot selected dot result equals all again but this time and drop-down color dot selected dot results and what we're going to do is we're going to use this symbol right there right that means not so if it does not equal this then what do you want to do well what I want to do here is I'm going to say filter employees where Department or not we're Department because we want all the departments right we're going to do we're favorite color dot value equals drop-down color dot selected dot results like that and so if we close our parentheses now what we should see there you so now it works for if you do all and all right both of those snares work and then all in green that scenario works cool cool cool so then now what I want to do is I'm going to do the reverse of that one just because that's the way that my brain thinks the other thing I would probably do if I was doing this I mean if I was really writing this right I was like coming here and I did this in the the app that everyone can download but here what I did I do like this is for all and all and so then I put this right here and then you know and I'm going right here on imitating shift inner and so these are comments right so this is for all the part minutes I have more times for departments and elected color but see so if you put in a little comments like that when you start writing these big giant if statements it makes it a lot easier on so then because this one's going to be this is for selected Department and all colors and so then now I know exactly don't want to write so I got to write first my evaluation I'm going to copy this instead of typing all that again because that typing is painful but someone changed this drop-down selected here to be drop-down selected result is not equal to all and this one is equal to all and so then what do I do in that case I want to know we're just copy yeah we don't have a good one for this sort of tight this one we're going to filter employees but so this time I'm going to filter employees we're Department equals drop-down department dot selected dot results like that and the formula Tory mad at me it's because I'm missing a comma right here so we'll put a comma in that is good and so then now we need our clothes for our filter and just to be a good little boy I'm gonna go over here again oh I'm going to ask power ops where it's so mad about I'm missing a close parentheses for my if there we go so then now write all in green words all it all works and then what about executive nice alright one more to go folks so now what we just need to do is we just need to do the final one technically you could just say this would be the default behavior but I don't like to do that I think that's sets you up to be confused I don't something to say this is for selected departments and the selected color boom shift enter what we're going to do and just say well paste in our formula again if dropdown selected it's not equal to all and color is not all then we're going to and we can just copy this out here what's a copy this could undo this filter and then now we need to do inside of our filter functions when you do an an so i say and drop down color oh no I'm sorry and favorite color dot value equals drop down color dot selected dot result awesome possum look at that just like that so now we should go to say give me all the executives at our red yeah give me everyone the thread yeah give me everyone everyone yeah look at that that's pretty cool all right so I will be honest with you right as you can see that that's not difficult there's just a lot of it there so if you're like oh I want to do three dropdowns technically you could do it you'd need nine combinations there gets a little weird so we have some other techniques that we use but they're very specific to the data sets they involve using other collections and filtering collections and pawn collections so maybe in a future video we'll cover that but it is possible to do now the last thing I did show was what if you wanted then to have the ability to search right so we'd say all right give me a text input here and so I'm gonna change the default value to be nothing I'm going to change the hint to be search first first and last name so that way we get this cute little gray text there ooh you could put a little search icon you know whatever you want to do but so now that we have the ability to do a search input what do we do well search just requires a table of data this function returns a table of data so you can literally just go right here but hey I want to search cuz search says what table well I want the table that comes out of there where do you go here we go comma and so then what text so I want to search I want to search whatever is in text input to text and then what fields or columns I want to search I want to search and I'm just gonna hit the drop down here and back I want a search first name and a last name remember the search function only works with text columns so we're going to you know give it to text columns here but that works just right there and so then now it doesn't even matter rank your gaze search for all the people Young is the last name so Nicola Shane and Chewie search for je Jeff and Jennifer so we're searching with first or last name and then we can still use our refiners but only blue and that's just Jeff because we have built right in tiny little baby steps we have built all this together right and that's one of the things I say it all the time I'm gonna say it again build this in baby steps don't try to boil the ocean don't try to do this in one big pass right it's okay to write the first filter all right that works then write two filters okay that works and then three and then four and then whatever right work your way through it in baby steps and you can give up the answer here now there are things I want to mention you know as asking that my Twitter friends kind of what do they thought I should talk about here so just remember you know large data sources was their number one answer so everything that we've done here is delegate all on this screen right all of this stuff is is delegate all to a you know in a depending on your data source so if you're running the delegation issues remember that distinct is not eligible remember that collect is not eligible and search may or may not be delegate will depending on your data source so you're gonna see a lot of different things if you get the little yellow triangle the blue squigglies you've got to figure out why you did that and typically my answer is going to be is find a way to refine your data before you get into this loop so for example it's real to me and say hey I want to search all the purchase orders well all the purchase worth might be a hundred thousand purchase orders right you're busy companies I have a client that does tens of thousands a month it's crazy but when they do that type of stuff right I'm like alright we can't search all of them and so what we do is we find ways that makes sense right so maybe it's just the open ones maybe it's just the draft ones we try to get into a filtered subset before we try start applying refiners we have a lot more luck there so you just have to start wrapping your head around it and remember there's a separate video on delegation but if you're seeing that yellow triangle or the blue squiggly you need to figure out why and make sure that you're comfortable with it and that's a topic for the other video so I'm not gonna get into it um other than that you know if you guys have got other scenarios like this you want to see you know I'm guessing spies right well what if I would date column either this or that send those over will kind of queue them up and try and see if we can get another video going around the next level of this but but I thought this was this was something I had seen with some people in the last month so I just wanted to kind of walk you guys through how we do this and at least get you you know halfway down the road there's there's more deeper stuff to be had here but that's for another day also just another reminder if you go out to training that power apps 9-1-1 comm you know there's free classes out there there's the paid subscription to this library and so if you get the library then you can download this app and all the little code so you would just have a working version that's app without any effort upon yourself so other than that I'm just gonna say thanks and have a great day before you go be sure to click on the subscribe button over here that way be notified when new videos come out if you need any help or you all work together whether your problems big or small check us out at power apps 9-1-1 we do it all i ron't or if you're looking for more formal training offerings we have those that lean to appear somewhere so check them out thanks and have a great day

Channel: Shane Young

Views: 124,761

Rating: 4.9623609 out of 5

Keywords: Shane Young, powerapps911, power apps, powerapps, powerapps filter gallery by dropdown, powerapps filter dropdown list, powerapps filter button, powerapps filter gallery based on dropdown, powerapps filter sharepoint list, powerapps filter sharepoint list lookup, powerapps filter multiple columns, distinct, clearcollect powerapps, clearcollect from sharepoint list, clearcollect powerapps sharepoint list, learn powerapps, powerapps video, powerapps tutorial

Id: kLGglidmPxg

Length: 28min 5sec (1685 seconds)

Published: Fri Jan 31 2020