Cascading Dropdowns in Power Apps from SharePoint list or Excel

hello everyone this is raza durrani today's video is about creating cascading drop downs in power apps we will utilize these cascaded drop downs in multiple scenarios inside a form control as well as for filtering gallery data so let's get started with the video but first my introduction the sample app here for the demo is a help desk application where users can create tickets in the system the home page is listing out all the tickets that are currently created the user can click on the plus icon to go ahead and create new tickets through the form control or the user can view the ticket or the user can go ahead and make modifications to the ticket that was created now the data for these tickets is coming from a simple sharepoint list at the back end the list is called service desk and the cascading drop downs that we would like to create for this scenario is when the user selects a department the request type should only showcase those requests that are associated with that specific department now in my case the department column is of type choice and i have four choices that i have defined right here now this column type depends upon your use case you could have this as a lookup column this could even be a text column that you can transform into a drop down on the form in my example here this is a choice column and request type is a single line of text column currently we will transform this into a drop down inside the app and make it a cascading drop down so that when the user selects a department only the respective request types show up now the mapping for the cascading drop downs i'm going to show two cases one i have an excel file in which i have a table created which has the request types mapped to the departments so for example if the user selects the marketing department then only the following request types which is business card and event banner should show up so we will leverage the table in this excel file to create the cascading drop-downs and i also have a secondary sharepoint list in which i have all my request types mapped to the department once again request type is a text column and the department is a choice column which has those same values so to begin with creating the cascading drop downs and power apps i've gone ahead and inserted a couple of labels one for department one for request type and i have gone ahead and inserted two drop down controls and i have renamed these to drp department and drp request type so first i will go ahead and import the excel file so i'll go to add data search for excel and import from excel selected my excel file and right here i have my table and that excel file which has the mapping for the departments and the request type so i'm going to select that and click connect my table is called ticket category table in that excel file now for the department drop down i would like to get the information from the department column right here now department in my case in the excel file as having repeating values i only need the distinct values so in order for me to fetch the distinct department names and place them in this drop down here i will use the following formula i will call the distinct function it's asking me for the source the source in my case is the ticket category table which comes from the excel file and then the column in my case that i would like to perform the distinct operation on is department and once i complete that and if i preview the app my department drop down is now populating the distinct values from the excel table column department next for request type for creating the cascading drop down based on the selection of the department i would like to read the data of the request type so the formula for request type would be as follows i will use the formula called filter wherein i will filter the table which is my ticket category table which is coming from that excel file where the department column is equal to the drop down department which is this drop down dot selected dot result now once i complete this i would also have to define from which column i would like the data to come from so in my case i'll put a dot and then pick the request type column from my excel file and this now will populate the request types based upon the department selection so let me preview this app and test this out currently the department selected is marketing so the request type is business card and event banner and that is true based on the data in the excel table if i change this to it this will now give me the request types related to it so this is how you can quickly create a cascading drop down leveraging data from an excel file now the next scenario for the cascading drop downs is where the data source is coming from my sharepoint list the sharepoint list is connected in my app right here called ticket category so for the department column now i will use the following formula for the items function i will use choices because my department column is of type choice and i will pick my list which is ticket category and then select the column and in my case the column here is called department the department values right here which is ite facilities marketing and security is coming from the choice values in this sharepoint list if this was not a choice column and a text column for example just like the excel table i would have to use the distinct function to give the distinct values now to get the request type values depending upon the department selection for the request type drop down the items property would be as follows filter the ticket category list where the department now it's a choice column so dot value is equal to drp department which is my drop down right here dot selected dot value for the value property of the drop down control ensure that you're selecting the column that you need the values to come from in my case i want the values to be picked up from the title column so once i pick title that's my title column where the request types are coming from so now if i go ahead and preview this app once again i have my cascaded drop downs department selected id i see the request types for it if i change this to for example security i see the request types for security and all of this information is coming directly from this sharepoint list now the next scenario is to add these cascading drop downs into if form control so when i create a new record here i have a form control that is connected to my main service desk list wherein i have my departments and i have my request type in my case request type single line of text column so in my app the request type is showcasing as a text box but i would like to create a cascading drop down effect wherein when the user picks a department i would like to showcase the request types associated with the department right here in a drop-down format so to achieve that the first step is i will select the request type data card and go ahead and unlock this data card the reason why i've unlocked this data card is because i want to change the control of type text here to a drop down control so first step i will go ahead and delete this text box this will result in some errors that we will fix shortly next i will go ahead and insert a drop down control i will increase the height of my data card and adjust the position of the drop down control i will rename this drop down control to drp form request type now the items property for the request type drop down needs to come from my secondary sharepoint list which has the cascading drop down values populated so to achieve that i have my department drop down right here in this case it's a combo box control so i will go ahead and pick the name of that control head over to my request type drop down and for the items property i will go ahead and plug in the following formula so filter my data source first which in this case just ticket category where the department which is a choice column so dot value is equal to the value that selected in this drop down control the name for which i copied earlier so the name of that control dot selected dot value and once i'm done with this once again ensure that you have the drop down control selected and for the properties ensure that the value for this drop down is coming from the title column because in my case the values are contained in the title column also i have these two errors that we have to fix so let's check the errors out the first error is associated with the y property of the error message label because we deleted the text box control and i replaced it with the drop down control so all i have to do is change the control name right here with the new control that i just added so that fixes the first error and then the second error is around the update property of the data card previously it was relating to the text box control now i have to replace this with the drop down control dot selected dot title because my data is coming from the title column now let's go ahead and preview this app if i select a department let's say i select the department id notice how the drop downs are cascading i get request type associated with my department i t if i change this to marketing it will only show me information regarding marketing if there is no department selected the request type drop down would be empty one more thing that we have to do is for the drop down control right here if i head over to the default property it's hardcoded to one i just have to replace this with parent dot default and the parent for this drop down control is the card right here and the default property of the data card is the actual value in the request type column now if i go ahead and preview the app and let's say i go back to my home page if i select an existing record to edit this will now populate the values for me based on the data for that record so for laptop connectivity issue the department was i t and the value selected was computer issue so let's go ahead and create a new ticket i've just added the title and description so in my case i'm trying to create a ticket wherein i need a banner for an event so the department that i will select here is marketing and the request type will only show me those request types that are associated with the department marketing i will select event banner enter the remaining details and click save and just like that this new ticket got created right here on my home screen i can see that my ticket has been registered if i view the ticket information here is the department here is the request type and if i go back and try to edit this request i can see that my cascading drop downs are in effect and the default values are set correctly there's marketing here's event banner and if i go ahead and select a different department you will see that the cascading effect is taking place and i can only see the request types pertaining to the department that i have selected and now one final use case of the cascading drop downs is in filtering data so in my home screen i can see all the tickets that have been created now i would like to create a filter right here so the user can select a department and select the respective request type and the data in this gallery should be filtered according to that information so for that i will go ahead and add some controls right here so i just added a couple of label controls and a couple of drop down controls right here so i have my department and my request type now to populate my department for the items property once again i will use the choices function so choices of my list dot the name of the column because it's a choice column in my case i can use this formula the department column is of type choice now if i preview my app i will see my department values showing up right here now i would also like to add an option here where the user can select all departments right the choices does not have that value so how do i go about doing that on the app onstart function which is the function that gets executed the first time when the app is launched i have this formula called clear collect so i'm creating a collection and in this collection i'm creating this object wherein i have a column called value and then the value for that i'm plugging in as all and then i'm also going ahead and adding additional data into this collection and this data is coming from that same column department and i'm using the choices function to grab all the values now why did i add value all if you look at the table that the choices formula returns the table has a column called value and in that it has all the values and that is the reason why when i was adding my own value of type all i had to respect that same structure now that i have this collection created and because i'm in the edit mode of the app i'm designing this app right now i will go to the app object right click and run onstart so that this collection has values in it and if i go to view and go to collections i can see the collection right here and i can see all the values in it including my value my custom value all for the drop down control right here for department for the items property i will leverage that collection which is collection department and now if i preview the app i get the all value as well as part of my drop down now next step is to create that cascading drop down so for the request type column for the items property the formula will be very similar to what we did before the request types are coming from the secondary list so filter that list where the department dot value because it's a choice column is equal to the drop down department which is this drop down dot selected dot value and once i'm done with this for the request type drop down in the value property i will select the column from where i would like to get the values from in my case it's the title column now if i preview this app because my department is all i don't have a mapping for all so it's not going to show me any request types and if i select any other request type that exists the cascading drop down effect will come into play and right here i can see my request types related to id or marketing or so and so forth now for all i would not like to show the request type filter at all because there is no values in it so for that all i have to do is for the label and the drop down control i've selected both of them i will head over to the visible property and set the visible property as follows so it will only be visible when the department drop down dot selected dot value is not equal to all so if i preview this i won't see those filters if it's all and if i select anything other than all the request type cascader drop down will show up next step now is to filter the gallery right here and for that i will head over to the items property of the gallery and i will go ahead and plug in my formulas so my formula will be filter my list where the department dropdown dot selected dot value is equal to all or the department column in my list dot value is equal to the department drop down dot selected dot value now if the value selected in the filter drop down that i provided here if that value is all then this will evaluate to true and in this case it is actually true if you look at the boolean value it's showing as true so filter my data source and then this evaluates to true or i have another condition now if the first condition is true it won't even evaluate the next condition and true means get me everything from my data source that means when all is selected it will go ahead and grab everything from my data source so if i preview this app i'm looking at all my data currently there are only six tickets so i see all those six tickets right here but if i go ahead and change the drop down so let's say i select marketing i will only see the data related to marketing and this is because the first formula right here evaluates to false and then there's an or condition now it goes ahead and evaluates the second part of my formula which is actually applying the department filter now there is another filter that i would like to apply and that's my cascaded drop down filter in this case it's showing me all the data related to department marketing in my case i only have two tickets but i need to also filter the data now based on the request type that is selected right here and for that once again in my formula i will go ahead and add a second condition now this is my first condition so i'm going to put this under brackets and now i'm going to add my second condition so i will go ahead and plug in the and operator right here so my second condition is if the request type dropdown.selected.title is blank or the request type column in my data source is equal to the selected title in my drop down control of request type now why have i added this condition here which says drop down request type dot selected or title is equal to blank well if i select all in that case my request type drop down is blank it doesn't have any value so for that i'm actually making this check hence when i select all this condition evaluates to true and this condition also evaluates to true so true and true that's true that's why it will show me all the data otherwise if i go ahead and select any department other than all in that case it will filter the data based on department and then filter the data based on the request type that is selected in that drop-down so now if i preview my app you can see that it is showing me the information for department marketing request type business card and if i change this to event banner now this is the ticket that we created during this demo and if i change this now to any other department it will go ahead and get me the data associated with that request type and once again if i select all it will show me all my tickets so that's how you can create cascading drop downs even for filtering your data if you enjoyed this video then do like comment and subscribe to my youtube channel and don't forget to hit the bell icon so that whenever i post my latest video you get notified about it thank you so much for watching

Channel: Reza Dorrani

Views: 28,121

Rating: 4.9755101 out of 5

Keywords: Cascading Dropdowns in Power Apps, cascading dropdowns powerapps, powerapps cascading dropdown, powerapps cascading dropdown sharepoint list, powerapps cascading dropdown excel, powerapps cascading dropdown filter, powerapps cascading, powerapps dependent dropdown, cascading dropdown powerapps sharepoint list, cascading dropdown in powerapps, reza dorrani, cascading power apps

Id: IWEQwGLcW7Q

Length: 20min 42sec (1242 seconds)

Published: Tue Feb 16 2021