Power Apps Sort and Filter on Multiple Columns

hello everyone this is razer durrani in today's video i will show you how to perform sorting and filtering to your data in powerapps we will use the sort function sort by columns function and the filter function all together and look at different use cases so let's get started with the video the use case here is a power app the data for which is coming from a simple sharepoint list i have leveraged the microsoft list template work progress tracker let's look at how we can add sorting and filtering capabilities for the gallery in my app the items property is the association to that sharp point list so basically the query says go and get all the items from my sharepoint list to sort the data based on columns we can use a function called sort by columns and this function expects the data source which in my case is my work progress tracker list and next it's going to ask me for the column on which i would like to perform the sort on and intellisense will provide all the columns associated with my sharepoint list now in my case i would like to sort based on the id column so i will select id and i will complete the function now if i go ahead and preview this app all the data in this app would be sorted based on the id column in ascending order back to the function if you would like to change the order of the sort once i have the column name defined i also have the option of defining the order of the sort by default it will sort in ascending order i can go ahead and define the sort order as descending if i would like to sort my results in descending order so here is my gallery now showcasing all the data from my sharepoint list sorted based on the id column in descending order now with sort by columns you can sort on multiple column types so for example let's say i would like to sort based on the due date so i've selected the sorting of my columns now based on the due date in ascending order and next i can even perform additional sorts by defining additional columns and the order in which i would like to sort them so let's say i go and add an additional sort that is on the title column in ascending order so now if i preview my app first my result set will be sorted based on the due date and ascending order and within that it will be sorted based on the title column once again in ascending order so if you see right here i have records that have the due date set as 30th march if you observe they are sorted based on the title in ascending order if i change my sort order for my title to descending and if i go back and preview my app you will observe that for the due date 30th march 21 i have all my titles now for that matching due date sorted in descending order now the sort by columns function specifically in case of sharepoint works with certain column types if you have complex column types examples choice columns lookup columns person type columns then this function will not work so let's try and see if i can sort based on my priority column my priority column is a choice column in my data source now if i pick the priority column i will run into an error that says that my function has invalid arguments so how do i sort based on complex type columns well for that we have another function called sort and with the sort function you need to provide the data source and then an expression now in my case my expression is going to be my priority column and this is a complex type column it's a choice column so if i need the value i have to go ahead and put a dot and select the value of that column and once again i can define the order of my sort in ascending or descending so let's say i sort this based on the priority values in ascending order and i complete my function now if i go ahead and preview my app you will note that the results are sorted based on the priority column now one key thing to observe here is that i get this delegation warning that clearly says that this will not work with large data sets if i head over to the app checker and go to formulas i will see that same delegation warning being highlighted right here while i'm writing the function itself i clearly see a blue dot warning that clearly highlights that i'm running into a delegation issue and if we look at the documentation around delegation for canvas apps there are only three data sources that support delegation right now we are focusing on sharepoint so i will focus on the delegable functions and operations related to sharepoint if we look at the sort function the complex type columns do not support delegation the only columns that support delegation are number text boolean and dates so if you need to work with large data sets data sets that grow beyond the delegate limit which by default is 500 in an app and can be extended to 2000 if you need to work with more records you have to ensure that you're not running into any delegation warning now that we are aware that we cannot sort on complex type columns without running into the delegation warning if you truly need to perform sort capabilities on these complex type columns what you would have to do is also ensure that you have an additional text type column in your data set and you're maintaining that value there as well so it'll be a bit more work that you have to do at the back end to ensure that you're maintaining those values on two different column types but if you have that value maintained in a separate column let's say of type text in that case you can go ahead and perform the sort without running into a delegation warning now next why do we have these two different functions sort by columns and sort why not just one function for sorting well sort by columns allows me to sort based on multiple columns whereas the sort function you can only sort based on one expression so i cannot sort based on multiple column types here now important to note it says expression in my case here i have picked a column let's say i change this to the title column so this will now go ahead and sort my data based on the title column in the sending order and i'm not running into any delegation warnings now that's because the text column is delegable now i can sort based on expressions here so let's take a scenario i would like to sort my data based on the upcoming due dates now for that i need to write an expression and that's where sort comes in handy i can sort based on the due date column where the due date column is greater than or equal to today's date now this expression will return results that are either true or false either the due date is greater than or equal to today or not if i sorted by ascending it will put the false records first but if i need the true results first i can then change my sort order here to descending now if i go ahead and preview my app i'm recording this video as of the 3rd of april it will now first sort based on all the tasks that are upcoming so as you can see these dates are all those dates that are greater than or equal to the third of april and once those dates are completed it will then showcase all the data for the past dates so from this point onwards here these are all the dates that are in the past now what if i would like to even sort the data within this in ascending order because i would like to see what are the tasks that are due next so for that i can first sort my data set based on the due date column in ascending order and then go ahead and apply the expression sort now if i preview my app once again i'm recording this video as of the 3rd of april it's going to show me all the tasks that are due next for me so here i have my next house that's due on the 15th then i have tasks that i do on the 12th of may and so and so forth and once all the tasks that are due it will then place all those tasks that have due dates in the past that do in a selling order let's see how we can add sorting and filtering both to work together now as part of my app i have added a few controls here on the top so that the user can filter the results based on the data that the user selects or enters in these controls so i have a text box where the user can search based on the task name i have the priority column here so the user can filter based on the priority and on similar lines the user can filter based on the status now back to the items property of my gallery i'm just sorting my data based on the id column in descending order now in order to filter my data i can just simply go ahead and add the filter function to my data source right here and here i can define my filter criteria so the first criteria that i will define here is filter using the starts with function the title column in my data source starts with the text that the user enters in that text box that i have defined let me format my function so the function filters my data set where the title column starts with the text that i enter in this text box right here and then we'll sort my results based on the id column in descending order so let's test this out if i click preview and let's start entering some data to search so i'll search for the word power it's gone ahead and performed a query against my data source to grab all those tasks that start with the word power and these are my tasks and then it has sorted my data based on the id column in descending order next let's try and add a second filter for the priority column so the function would be and the priority column dot value is equal to my drop down of priority not selected not value now if i preview my app it's not showing me any results that's because my priority column is empty we have to handle the empty scenario but if i pick a specific priority and if the criteria matches in this case the criteria is all the tasks that begin with the word power and the priority is medium it will grab that information and at the same time sorted based on the id in descending order now if i reset my filters basically resetting my controls i don't see any data that's because the priority column is empty so we need to handle the empty criteria for my priority filter i will start with the round braces and in this i will add an or condition and for this condition i will check to see if the priority dropdown dot selected dot value is equal to blank and i will close my function now if the priority value is blank this will equate to true so this function will result in true and in case of true it will get me all the records and apply the other filters so if i preview my app now this is showcasing all the data from my data set once again as i start typing it will go and grab the data that begins with the letters that i have entered in that text box and now if i go ahead and select a specific priority let's say priority high this will now only showcase those records that have the priority high and the task begins with the text that i have entered i can reset my filters now on similar lines i will add the logic for the status column so here's my formula for the status column i have again appended and and then exactly the same logic as the priority column because this also is a choice column in my data source now if i preview my app i can filter based on priority and i can even go ahead and filter based on the status so it's going to work in combination and the results that are going to come in are going to be sorted based on the id column in descending order so now that i have sorting and filtering working together my filters are dynamic but my sort condition right now is hard coded to id in descending order let's try and make the sorting dynamic as well so for that i need a visual indicator first for the user so that the user knows what's the sort criteria plus i need a button or an icon so the user can click on the sort column and even define the sort direction so for that i will head over to insert head over to icons and i will pick the icon sort i have placed this icon right next to the id column now what i need are two variables one variable which tells me which column is being sorted and a second variable that tells me the direction of the sort i will head over to the app onstart function and create two simple variables here so i've created a variable here called where sort column and in this i've defined the column that i would like to sort on first in my case it's the id column and next i'm creating another variable which is for the sort direction and for this i will pick my default sorting direction that i would like to begin the sorting from let's say i start in descending order and to ensure that i have these variables set as part of my app while i'm editing this i will head over to the app object and run the onstart function so i have these variables set now for this icon control that i have added next to the id column on select of this icon i would like to set those two variables and i will set those two variables as follows i will set the sort column variable to id because that's the column that's being sorted and i will set the sort direction as follows if the current sort direction is ascending then make it descending next i can copy this icon and paste it next to any other column that i would like to sort on so let's say i would like to sort on the task column as well that's my title column in this case on select of this i will just go ahead and change the sort column this time to title and i can repeat this for any column and here's my due date icon on select function now when the user clicks on these icons those variables will be set so let's see how we can leverage those variables and change our formula for the items property of our gallery now if you look at the items property right now it's being sorted based on the id column and descending order we can change this now so i can change the column to the variable where sort column and i can change the direction to the variable where sort direction if i now preview my app if i click the id column you'll see it sorting id based on the sending if i click this it's id sorted and descending if i select this it's sorting the title and ascending so you see the the formula is working for me by looking at these icons there is no visual indicator as to which column is currently being sorted on and what's the direction of the sort if i head over to the icon property of let's say the id column sort icon the icon property is set to icon.sort but i need to change this based on whether the column that is being sorted upon is actually the id column and what's the direction so for that i will go ahead and plug in the following formula the formula is if the sort column variable is not equal to id in that case put the icon sort which gives me those two arrows up or down else if it is being sorted on the id column then check the direction if the direction is ascending put the icon arrow up else put the icon arrow down now if i go ahead and preview my app if i sort let's say based on the title column you will note that the id column icon is the sort icon but if i click on this it will now show me the direction of the sort in this case it's ascending if i click this it will change the direction to descending so it's a clear indication to the user as to which column is being sorted upon and what's the direction i can even use the same logic to change the color of these icons if required now let's repeat this same formula for the other two icons as well here's the formula for the task or my title icon and here is the formula for my due date column icon so right now it's being sorted based on id and descending if i click on task it's sorting based on the task column in ascending order so here's a clear visual indicator to the user as to which column is being sorted upon and all of this sorting works in conjunction with the filtering so if i was to filter based on tasks that are high priority is going to perform those multiple filters that i set on the top at the same time it will also respect the sort that the user performs now with all of this working there is still one issue and the issue is the issue of delegation if you observe my formula i'm getting a delegation warning in the sort column and that's because i'm defining my sort column dynamically if i change this and i hard code this to a specific column it will work but then i get a delegation warning in my sort direction that's because i'm defining my sort direction as well dynamically if i change this to a specific direction i don't get any delegation warnings and that's exactly what i want so how do i leverage those dynamic variables and at the same time go and perform the operations of sorting and filtering well for that you have no option but to write a long formula i will add a switch function and i will switch based on the sort column now if the sort column is id in that case what's my formula or function that i would like to perform and my function is as follows sort by columns here's my filter criteria and right here i have my hard-coded id column that i'm defining and now i need to change my sort direction based on the direction variable and for that i've just gone ahead and plugged in a very simple function that checks to see what's the sort direction if it's ascending then well sort on ascending i'll start on descending so i've just gone ahead and completed my switch function for the id column scenario now i will just go ahead and replicate this for my title column and all i have to change here is the sort column which is my title column and next i will repeat the same thing for my due date column and once i'm done with this i will go ahead and close my switch function yes it looks complex yes it's a repeat formula but guess what all of this supports delegation and it is still dynamic because based on the sort icon that the user clicks the respective switch function would be called so let's go ahead and preview this app and test this out now so get me all my tasks that begin with power go ahead and filter this based on priority medium status as not started and let's sort this based on the id column in descending order sending order change the sort of the title column i can reset if you enjoyed this video then do like comment and subscribe to my youtube channel and thank you so much for watching

Channel: Reza Dorrani

Views: 169,987

Keywords: power apps sort and filter, powerapps sort and filter gallery, powerapps sort and filter, powerapps sort and filter datatable, sort and filter function in powerapps, powerapps sort gallery, powerapps sort gallery by date, powerapps sort, powerapps sort button, powerapps sort dropdown, powerapps filter sort, powerapps filter sortbycolumns, power apps, powerapps, sort filter and search in powerapps galleries, filter and sort in power apps, filter, sort, sorting, sortorder, tutorial

Id: 6KlI1iZ_KD0

Length: 21min 41sec (1301 seconds)

Published: Mon Apr 05 2021