Cascading dropdown in PowerApps

In this blog we will show you how to create a cascading dropdown in powerapps using sharepoint custom list as data source. If you are new to PowerApps platform then you can go through this link to get started.

  1. Create a SharePoint custom list. In below screen shot you can see that I have created a “States” custom list.

  1. Now we will create a new screen under PowreApps section as shown below and add drop down lists.

3.Now we will bind the items state drop down list with SP states custom list as shown below.

4. We will use the “Distinct(States,Title)” function to bind the items. Here I am using the distinct function in order to get the unique column values.

5. Now we will bind the District drop down with SP custom list on the basis of State selection.

6. Use “Filter(States,Title=StateDropdown.Selected.Result)” function to achieve this as shown above.

7. Complete the above steps and preview the App in order to see the result. It looks like something given below.

You can make the default value as null or blank if you want. We will see in our next post how to make the default value as null or blank. Thanks for visiting my blog. Please share and subscribe our site for more stuffs on PowerApps.

Sharing is Caring

26 thoughts on “Cascading dropdown in PowerApps”

  1. Hi sir,

    in the above statement district dropdown advanced under item values Distinct is not getting for me what i need to do . please guide me

    1. Hi Sudhakar,
      Distinct is a powerapps fucntion which evaluates a formula across each record of a table. Distinct returns a one-column table that contains the results, with duplicate values removed. It should come automatically in the formulae (fx) textbox automatically. Can you share your formulae.

  2. Hi,
    I followed this example and created a list and a powerapp for that list. I created two drop down columns in the list and applied the Distinct function for the first drop down and it worked. When I tried to apply the Filter function for the second formula, it does not work and it does show any errors either. Also the Value tab is also not visible maybe because I used a sharepoint list dropdown rather than going for a dropdown in powerapp. Can you please explain what possibly can be done?

    1. Hi Imesha,
      Can you create a powerapp drop down and use the filter function on it to make sure whether it’s working or not. And also can you share the screenshot of your existing formula used in PowerApps studio.

  3. Nice tuto: I was looking for little advance one; What if we need multiple selection cascading combo box or list box.
    suppose: combobox1 : (Fruits,Hardware,Furniture)
    combobox2: If i choose fruits and all the items related with fruits should show and if i choose fruits & hardware then all the items related with fruits and hardware should show and can select as per my choice and save.
    Also i must able to view and edit..
    is it possible?

  4. Hi Hita,

    Thanks for visiting our blog. Yes, it is possible to filter items based on cascading dropdown or Combobox. In this example we have a list with the name ‘Product Sales’ which has SalesOrderNumber column. we are selecting multiple salesordernumber from combo-box and showing item details in a datatable. I have written this formula for datatable items Filter(‘Product Sales’,SalesOrderNumber in ProductsComboBox.SelectedItems.SalesOrderNumber)

    PowerApps Combo-box filtering

    1. Hi Rakesh,

      If you don’t mind, could you please let me know steps to create cascading combo box.Is combo box a SharePoint lookup field (with allow multiple values) or have you set a Powerapp datacontrol ?

      Also once you populate it to datatable , if I want to save one specific column to SharePoint field, how can I do that ??

      1. Hi Vishnu,
        My Apology for delayed response.
        Yes we can create a cascading dropdwon using sharepoint look up field.
        For example, see in below screen shot state and district values are coming from a sharepoint list.
        Cascading combo box
        Formula used to show the State value is Distinct(States,State)
        Formula used to show the District is given below.
        Items = Filter(States, State in ComboBox1.SelectedItems.Result)
        DisplayFields = [“District”]
        In my opinion instead of using datatable, you can use gallery to save data to SharePoint.

    2. Hi Rakesh,
      I tried to follow the example but it did not work as expected.
      As i said: i have two combobox and Items List having column ‘stuff’ in which i have fruits, fruits ,hardware, hardware, furniture, furniture.
      and ‘related’ column in which i have apple,orange,iron,Nail,chair,box
      combobox1 : (Fruits,Hardware,Furniture)
      combobox2: Apple,orange,Iron,Nail,chair,Box

      now if i select fruits and furniture in combo 1 then in combo 2 i should be able to see the option Apple,orange,chari,Box.
      AND I should able to save the selected items also i should be able to update.

      i succeed in cascading but unable to SAVE AND UPDATE.
      is this possible??

      1. Hi Hita,
        As I suggested vishnu in my below comment that we can use gallery to save and update the data. Can you let me know exactly where you want to save these details, in SP list or somewhere else ?

          1. Hi Hita,
            Pls see the screen shot of an example below.
            Save Sp Item
            Formula used in this application are:
            Add Icon on select : Collect(Repeating,{Name:TextInput1.Text,Employeeno:TextInput1_1.Text,Supervisor:TextInput1_2.Text});Reset(TextInput1);Reset(TextInput1_1);Reset(TextInput1_2)
            Cross Icon on select : Clear(Repeating)

          2. On borad button on select : ForAll(Gallery2.AllItems,Patch(EmpOnboard,{Title:TextInput2_1.Text}));Clear(Repeating)
            EmpOnboard is a sharepoint list.
            Remove All Items on select : Remove(EmpOnboard,Gallery3.AllItems)
            May be this example will help you in building your application.

  5. KVBharat Bhushan

    Hi in my Power App Application I have created a Main List say Employees with Country,State,City Look up dependency from a Country Master List. Now I developed a Cusomized PowerApp Employees list with Country,State,City Datacards[hidden mode as of now] taken from Country Master datasource and assigned Country,State,City Drop down values. Please tell me what should be the Default, Items, OnSelect, OnChange and any other Advance Property values if required for all the above 3 mentioned Country,State,City Datacardvalues and also Dropdown values. Here I am loading 2 DataSources: Employee Main Form, Country Master for Cascading Dropdown inputs. So please give me a clear detail sample explanation on this.

  6. Hi Rakesh.

    Thank you for the post.

    I’m facing an issue at “StateDropdown” in the “Filter(States,Title=StateDropdown.Selected.Result)” function.

    My list name is “Platform” with 2 columns

    Title is renamed as “Selected Vendor”

    The second column name is “Platform”

    In PowerApps:

    On Selected Vendor Items, I’ve used Distinct(Platform,Title) which is working fine.

    On Platform Items, I’m facing issue.

    Can you please help me with this?

    Regards,
    Navin.

    1. Hi Navin,
      Any error or just the Platform Items is blank.
      Select the Platform drop down under power platform editor -> go in advanced tab of property window.
      under Items write formula -> Filter(,Title=Vendordropdown.Selected.Result.
      And now remember to select the value as Platform for value drop down under advanced tab of property window

  7. Hi Rakesh,

    Thanks for this tutorial , I have couple of questions.

    1, In my case i have 3 drop down lists to create in power apps . The first Dropdown 1 has say Countries (India , US etc.) the second drop down has States (TN, UP etc.) . So when i put a country , i will need to show all states in that country , then when i pick a state say TN in drop down 2 , i should populate Chennai , Ooty in dropn down 3 . How can i accomplish this.

    2. Not sure if you have worked on this . I built a form in power apps and uploaded a file with some metadata to sharepoint library through flow . The file is uploading to SP but its creating 2 versions of the document in SP which is not right . The ver 1 is blank and the 2nd ver has metadata . How can i resolve this issue ? is there an action in the flow which we need to configure to get only 1 version of the file in sharepoint ?

    Any help on this is appreciated.

    Thanks

    1. Rakesh Pandey

      Hi Bala,
      Please find my replies below.
      1) You can again use the filter function on the items of third drop-down, which is city in your case. Filter the items of city based on the selection of country. Please try and let us know the result in comment.
      2) I have uploaded files in document library using flow but not with metadata. Can you try disabling the versioning on document library and then upload. I will also try to replicate the scenario at my end too.

  8. Hi Rakesh,

    Thanks for your support. No 1 works and No 2. i figured its a sharepoint feature itself when i tried to load a file in sharepoint directly without metadata it created version 1 . if i upload the file with metadata its creating 2 versions so the ver 1 is a dummy which is ok for me.

    Thanks

    I have another question , i am creating a form in power apps to populate information of 10 columns from sharepoint library . I have 10 text input fields on the form. When the form is connected to SP library datasource , user enters EMP ID in the first text input field rest of the values corresponding to the emp id should popukate in the 9 text input fields. Can this be done ?

    Thanks

    1. Hi Bala,
      You are welcome.. !
      Actually what I understand from your question that you want to auto populate the other 9 fields based on Employee ID field. I want to know that do you have already a list or data source which has information of other fields corresponding to the employee ID. If yes, then yes it can be done. If no, I will suggest you to create a list with EmpID and other information. Once you connect your app with the source data source, set the default value of other 9 fields using below formula.
      First(Filter(Data Source,condition)).Result Field.
      I tried this above formula in my test application and it is working. My formula is like below.
      First(Filter(Emptable,EmpID=Value(DataCardValue8.Text,”es-ES”))).EmpName
      In above formula I taking the first value from a data source. Also I am filtering the source based on EmpID.
      Please let us know if you want any other assistance.

  9. Thanks Rajesh,I should have posed by question little more clear .

    Yes, i have a data source (sharepoint library) . The library has 3 columns say (Emp ID, Emp Name, Emp Dept Name).

    The Power App Form should look this . When user inputs Emp ID value in Text Input 1 field and hits the Search button (should connect to the datasource (share point library in my case) and retreive values of Emp Name and Emp Dept Name from sharepoint library for the Emp ID and populate the power app form fields text input 2 and text input 3.

    What are the details to get this accomplished . In this case i think should we be applying the filter function on the search button ?

    i am not able to paste the image of my sample form here.

    Regards

    Bala

    1. Hi Bala,
      I created a sample app where I am taking data from my Employee Table list.Employee Table
      Steps:
      1)Created a collection to collect the Employee Table list OnStart of App : Collect(Emptable,’Employee Table’)
      2)OnSelect of search and populate button : UpdateContext({empnameVar:First(Filter(Emptable,EmpID=Value(TextInput2.Text,”es-ES”))).EmpName})
      3) Set default value of EmpName text box = empnameVar
      Result Pic: Result
      Hope this will help you. If not please let us know, we will try our best to help you out.
      Thanks

  10. Thanks Rajesh.

    I tried the function and i am getting an error

    UpdateContext({DocumentTypeVar:First(Filter(Collection1,ProductType=Value(TextInput1.Text,”es-ES”))).DocumentType})

    Error : Expected Operator. We expect an operator such as + , . or &

    In this case of my form , text input 1 is ProductType to enter during runtime and DocumentType should populate in text input2.

    By the way what is this reference : “es-ES” and also if i want to expand on populating more columns from the sharepoint library , how would the formula syntax be ?

    1. Bala, I am using value function to change my string to number because in my case Empid is a number. So in order to compare I need to convert string to number. For more details check here. “es-ES” is the language tag for Spanish in Spain. In Spain, a period is a thousands separator. You don’t have to use that tag.
      May be you can try below formula.
      UpdateContext({DocumentTypeVar:First(Filter(Collection1,ProductType=TextInput1.Text)).DocumentType}) //here DocumentType is column in your collection1
      you can use semi-colon “;” on button to separate different functions, so if you want to populate multiple fields then formula will be
      UpdateContext({DocumentTypeVar1:First(Filter(Collection1,ProductType=TextInput1.Text)).DocumentType1});UpdateContext({DocumentTypeVar2:First(Filter(Collection1,ProductType=TextInput2.Text)).DocumentType2})

      please let us know the result.

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top