PowerApps Multiple Filters on Gallery

hello everyone this is raza dharani in today's video we will cover creating multiple filters to powerapps galleries we will look at various types of columns choice columns multi-select choice columns text dates and we will explore how we can create these multiple filters to our powerapps galleries with respect to dataverse and sharepoint as a data source so let's get started with the video but first my introduction [Music] in this scenario i have a gallery that is connected to a data source the data source in question right here is microsoft dataworks the table in dataverse is a table for tasks wherein i am tracking all my task information so in powerapp now the items property of this gallery is tasks info that means it's going to get me all the records from my data source now let's start applying the filters the first filter i would like to apply is on the name field which is of type text in my data source so the first step is we will go and add a text input control i will remove the default property add a label called name so the user knows that they are filtering based on the name i have renamed the text box to txt name filter now the use case here is i would like to search based on the name that the user has provided so there's a function called search so i'm going to leverage the search function and the search function as you can see intellisense is guiding me through it requires the data source which is my table and then it is asking me for the text that i want to search for and in my scenario the text is the text control that i created dot text and finally it requires the name of the column the name of my column here the internal name is crc o5 underscore name close my function and just like that i have added a very simple filter here wherein i can search based on the name control which is of type text so if i play the app right now and if i start searching so let's say i start searching for the word learn so it will return all the items from my table where the name contains the word learn if i was to search for the word power it's going to return everything in my name field that contains the word power another way that many folks try out is use the filter function and they provide the table name and then we try and use the in operator now in is not a delegateable operation so it is not delegable but the search function is delegable with respect to data works so it's very important to understand the concept of delegation and there is really good documentation on docs.microsoft.com which will explain what delegation is basically delegation means that your power app can work with large data sets you're not limited to the 500 delegation limit in power apps which can be extended to 2000 and there are data sources that support delegation there are three today one of them is dataworks formerly known as cds sharepoint and sql it's very important when you're building the app you take into account delegation otherwise you will get false positive results in scenarios wherein your tables are large and if i go to file settings and advanced settings the number one feature right here is that delegation limit which by default is 500 it can be extended to 2000 you cannot spend beyond the number 2000 okay so i have changed my function back to using the search formula now next i would like to apply a second filter and this time i would like to apply a filter on the task status column now in my table if you look at task status it is a column of type choice now for this now for this i will add a combo box now i typically prefer a combo box over a drop down purely because combo boxes have additional features where and i can actually search for items and i can even turn on multi selections if required and i've gone ahead and renamed my control to cb task status filter now for this combo box control for the items property typically what we end up doing is this we try and hard code our values in here for example my choice column for the task status has these values and what we end up doing is we end up hard coding those values right here now the challenge with this is one you're hard coding second because you're creating this array with type text many times there are errors when you're trying to actually apply the filters because the data types are not matching in this scenario i'm using dataverse and i'm using choice the type is an option set so if i try and map this and try and filter it will throw me an error so the better way of doing this is actually directly reading the values from your data source so i can use the choices function and pick my table and then pick my column and the moment i do this if i was to play the app right now if you observe the combo box i'm getting all my values which are my choice values directly from my choice column option set so now if i go back to my gallery and if i would like to add my second filter the way i would do that is this i will apply the filter function now on my data source which is tasks info pick my column which is task status is equal to my combobox control dot selected dot value and just like that if i was to go ahead and play this app now it's empty so it's not giving me anything but if i pick completed it's going to show me everything in completed status and if i pick deferred it's going to show me everything in deferred status and so and so forth now notice what happened right here i picked deferred and i picked in progress it only respected the last one that i picked if i completed it's only showing me completed although i have multiple choices here and the reason is because currently my combo box has the select multiple option to true i can turn this to false and now this will act like a drop down control when the user can only select one value at a time so i have to pick only one value and that makes a lot more sense in this scenario of course we haven't handled one thing yet if this does not have any values it's technically not applying any filter so i would like to show all the options and the way we can handle that is this so back to my gallery control right here we have the query which says the status is equal to the selected value now what happens if my combo box is blank or my combo box is empty and the way we can add that logic in here is by adding this formula and here's the formula i'm checking to see if the combobox.selecteditems.value is blank or is the selected items in the combo box empty why am i adding these two options here well this is one of the challenges in a combo box actually if you select a value and then unselect it then the is blank function covers it initially when it is empty in that case the is empty function covers it so you have to add these two formulas in there also if you notice i have applied the or operator in between all of these functions now so basically what's going to happen if the combo box is blank this is going to return true so my query is filter my data source true that means get me everything right so if it's blank it's going to get me everything or if it is empty it's going to give me everything and if it has a value both of the first conditions are going to be false and then the logic is going to fall on the third condition which is my actual condition of filtering my task status column so if i play the app right now if you notice my combo box right now has an empty selection so it's showing me all the records in my table if i go ahead and start selecting values it will go ahead and filter them based on the selection that i have made and let's say i go and clear my selection right here this will go back to its original state which is the empty state so it is going to give me all the items in my gallery now i can replicate the same behavior for another choice column that i have in my data source in this case it's a column which is priority and the behavior in my case is going to be exactly the same i have my combobox control i have made it single select and this time i'm going to pick choices dot priority so now if i head back to my gallery and if i need to add a second filter to this all i have to do is add another logical test all i have to do is put a comma and then put my second filter criteria right here and this is what my second filter criteria looks like exactly the same as the first and here you can keep adding additional filter criterias and the more criterias you add the more logical tests you basically add to your filter function they will act as and operations that means this will be an additional filter that would be applied so now if i play my app right now it's showing me everything now in my case let's say i want to see all my tasks that are in progress so it's showing me all my tasks that are in progress and i would like to see my tasks that are high priority and just by selecting that supplying both the filters now together and plus there is also a name filter being applied so if i was to search for something by name it would also apply that additional filter to this now let's try and add a little bit more complexity to the filtering scheme right here so let's say in my case right now task status is a single select column but i want the user to make multiple status selections so the user can say show me everything that's in progress and show me everything that's completed now in order for me to achieve that of course this is a combo box so i can go to the combobox property which is select multiple and i can turn this to true and now i can easily make multiple selections but when i do this it's not going to give me the correct results and the reason is i have to make a change in my filtering condition so if i head back to my gallery now and if i look at my filter condition wherein i'm checking if the task status is equal to the selected value in the combo box now instead of using equal to all i have to do is change this to the in operation and selected means it's always expecting like the one selected value i am going to change this to selected items that means whatever items are selected is the actual task status in any one of them and notice i'm not getting any delegation warning in this case and the reason why i'm not getting a delegation warning here is because if i go to file settings and advanced settings if you look at one of the advanced settings it's called the enhanced delegation for the common data service which is dataverse it's a preview feature it is turned on by default for new apps for your older apps what you have to do is you need to ensure that this is turned on so this is enhanced delegation support for the in operator and that is supported in dataverse so now back to my app if i go ahead and start making selections for the task status properties so show me everything that's completed it's going to show me everything that's completed show me everything that has been not started as well so now it's showing me both the values values that are completed and values that are not started now there's another thing right here if you look at this combo box of course as i start adding values the combobox does not show me the actual value it just says that two items were selected but maybe i would like to give the user a visual indicator of what are the task statuses that they are selecting so how can we go about doing that so i can go ahead and add a gallery i'm going to just add a very simple blank vertical gallery right here for now pretty simple so for the items property of the gallery i will leverage the same choices function to give me all the statuses of my task and once i do that in the gallery i will go ahead and add a checkbox so i am going to allow the user to check multiple options right here and the text of my check box is going to be this item dot value and once i do this this is going to go and grab all the information from my choices field one thing i could do is i can go and wrap my choices so in this case i know that i only have four choices i'm just going to wrap my choices right here i'm going to try and squeeze this in further so i don't have a scroll box right here and maybe i can even squeeze this a little bit down here so this now could be my filters that the user can make a selection in now whenever the user selects an item in the check box that means there's a property in the check box called on check we will go ahead and add the selection to a collection so i'm going to call this collection task status filter and i'm going to add this item to the collection that means whenever the user checks the check box it's going to add it to the collection on the other side there is the on uncheck function so when the user unchecks it we would like to go ahead and remove this item from the same collection the user unchecks the item gets removed from the collection and now in order for me to apply the filters based on this collection and for the combo box there is a property called default selected items this is a multi select combo box here so what i can do in this case is i can go and provide the collection that i just created here dot value to it now if i play this app and let's say i pick deferred notice how deferred is getting selected if i pick in progress in progress is getting selected if i remove these it's going to remove it and show me all the values now the user does not have to see this so i can go and change the visible property of this combo box to false and i just moved my filters around here and this now gives the user a completely different experience when they can see all the choices they can make multiple selections and the filters are happening live here against my data source and everything in a delegated manner with dataworks as a data source now i'll add one more filter right here i have a due date column as part of my data source and this one is of type date so i've gone ahead and added a couple of date picker columns and i have renamed them to date from and date 2. for the from date i would like to make this 30 days before today as default so i will use the date add function provide today's date and subtract 30 days so this now will show me a date which is 30 days before today and the two date i will go and make this 90 days from today so now if i play this app i see the due date range right here for the user so the user can go and make the selections i will go and add another logical test right here and this time my formula is going to look like this my due date column so get me all my tasks where the due date column is greater than or equal to the from date so that's my from date control dot selected date and my due date column once again this time is less than or equal to the to date column and the selected date now if i play this app this is actually getting me all the data based on the date range that i have provided so for example if i would like to see all my tasks that are due in january all i had to do is just change the from date to january and now this is showing me all the tasks that are due for me from january onwards up until march 13th so that's how easy it is to apply additional filters to my gallery and this time i added filter of type date and of course all the other filters are still going to work if i pick in progress it's going to give me in progress if i pick medium priority it'll show me that if i want to look for state machine workflows it's going to apply that filter one last thing i would like to add to all of this is the option for the user to reset the filters so for that i will go ahead and add a button call this reset and when the user selects this all i would like to do is literally just go ahead and reset all my filters and the way i can reset all my filters is this just call the reset function and then start calling your control so in my case i have my name filter i will go ahead and reset that next my priority combo box next my from date and my two date and then for my task status column which is that hidden combo box because the default value of the combo box is being driven by the collection we just have to go ahead and clear the collection now if i was to go ahead and play this app if i click on reset everything goes back and gets reset but if you notice my task status selections are still in place and the reason is because if i go back to my check box the check box has a default property and that default property right now is set to false so we would like to change this based on whether or not the collection actually has the value of that checkbox the collection is cleared we just have to check if this item is in the collection now if i play the app you notice it's cleared if i start picking my values removing my values it will apply the filter if i click on reset it's going to clear everything and give me all my results of course i would like to also show you what happens in the case of sharepoint yes the behavior the formulas are all quite similar but certain formulas that are delegable and dataverse may or may not be delegal the important thing is to follow the documentation if i head over to the documentation and for example select sharepoint it clearly highlights what are the different functions that are delegable and what data types are delegable i'm going to apply my filter for the name property once again so if i go to the gallery here which is my data coming from a sharepoint list in this case so if i apply the search function because i want to search in my list in sharepoint it's asking me for the text that i want to search which is my column dot text and then it's asking me for the name of my column again intellisense is guiding me through i have a column called title i'm just going to select this and i'm done now i want you to observe something if i go and play this app yes it's showing me the data and yes i can search if i search for flows it will show me everything wherein the title contains flows but there's a warning here and the warning is nothing but the delegation warning that is coming up right here so if i go back to my formula it clearly says that the search function is not delegable with sharepoint so basically you cannot perform the contains operation with respect to sharepoint for large data sets there is another formula called startswith and as you can see that this formula works with a text column and the delegation function operation documentation states yes that means if i head back to my gallery here and change the filter so instead of using search i will use the filter function starts with function gonna give my column name which is title and then now notice there is no delegation warning and if i play this app and if i start searching it is going to use the startswith operation so the title that begins with or starts with the text that the user is entering so if i say sharepoint it's going to give me everything that begins with sharepoint so if i was to search for example for power it's going to only get me those titles that begin with power although i have a lot of other tasks where the word power is in them but it's not a contains operation this is a starts with operation so this is one of the limitations of working with sharepoint specifically with respect to delegation in powerapps i have done an exclusive delegation series i have shown a lot of tricks and tips which will help you to get around most of the cases and sharepoint so do go ahead and check that out i'm going to place the link to my series in the description of this video now let's try and add a second filter right here this time for priority and for the combo box i'm going to apply the same logic here my priority column in sharepoint right here if you look at the settings for this column this column is of type choice i'm going to use the choices function and this time pick my sharepoint list dot the priority column and just like that if i was to go ahead and play the app notice it's going to give me all the values and that's exactly what i need it's getting it from my data source back to my formula right here now i've already applied one logical test which is my condition and i want to apply a second logical test condition and in this case my condition is where the priority is equal to my combobox dot selected dot value and notice i'm getting an error here that is because the priority column is a choice column so you have to pick the value of that column so priority dot value is equal to combobox.selector.value and if i was to go ahead and play this app now as i pick my priority it will start showing me the data of course if there's no value it's showing me empty so what do we have to do in this case exactly the same as before apply the logic to check to see if my combo box is empty or not and for that we use this blank and the is empty technique so if i go ahead and play this right now showing me everything as i start picking my priority it will go and make the selection now if i try the same case as before when i want to make this multi-select so if i go to select multiple and if i try and make this true and if i go back to my items function in case of cds we did something wherein we changed the equal to to the in function and it worked let's try that with sharepoint notice what happens straight away sharepoint the in function is just not delegable that means i cannot perform a contains kind of an operation right here wherein the user can make multiple selections and get the values that means i would have to stick to a single selection is there any way around this well yes i've kind of shown a hack in my delegation series so do check that out and next i've gone ahead and added the date controls here and to apply the date filter query right here it's going to be very similar to what i did for dataverse and yes date columns are delegable with respect to sharepoint as a data source and as i start making my selections so show me everything that is due from january onwards it's going to go and apply the filter and once again i have applied the same filter reset logic if i reset everything goes back and resets to its original state so this is how you can go and apply multiple filters to your galleries you can also provide the users with additional filter types like the one that we applied for the dataworks gallery when we added this gallery for the task status selection i hope you found this video helpful if yes then do like comment and subscribe to my youtube channel and make sure you hit the bell icon so that the next time i upload a video you get notified about it thank you so much for watching

Channel: Reza Dorrani

Views: 58,821

Rating: 4.9648352 out of 5

Keywords: powerapps gallery multiple filters, powerapps filter gallery by multiple dropdown, powerapps multiple filters on gallery, multiple filters powerapps gallery, powerapps multiple filters, powerapps multiple dropdown filters, powerapps gallery filter by column, powerapps multiple filter criteria, powerapps multiple filter conditions, Power Apps Multiple Filters for Gallery, powerapps filter gallery multiple conditions, powerapps filter gallery by combobox

Id: 5dSk5iOgT68

Length: 23min 44sec (1424 seconds)

Published: Tue Dec 15 2020