In this blog, we will show you how to create a cascading dropdown in PowerApps using SharePoint custom list as a data source. If you are new to the PowerApps platform then you can go through this link to get started.
Create a SharePoint custom list.
In the below screenshot you can see that I have created a “States” custom list.
Now we will create a new screen under PowreApps section as shown below and add drop down lists.
Now we will bind the items to state drop-down list with SP states custom list as shown below.
We will use the “Distinct(States,Title)” function to bind the items.
Here I am using a distinct function in order to get the unique column values.
Now we will bind the District drop-down with SP custom list on the basis of State selection.
Use “Filter(States,Title=StateDropdown.Selected.Result)” function to achieve this as shown above.
Complete the above steps and preview the App in order to see the result. It looks like something given below.
We saw how to implement cascading dropdown in PowerApps. 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 to our site for more stuff on PowerApps.
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
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.
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?
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.
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?
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)
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 ??
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.
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.
Thank you Rakesh !! I will try that option.
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??
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 ?
Hi Rakesh,
Yes i want to save in sharepoint list.
I can cascade but i am not able to save or update..
Plz see the shot: https://ibb.co/3Ngw1GL
Hi Hita,
Pls see the screen shot of an example below.
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)
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.
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.
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.
Hi Navin,,Title=Vendordropdown.Selected.Result.
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(
And now remember to select the value as Platform for value drop down under advanced tab of property window
Thank you very much for your assistance, Rakesh.
It’s working now with your help.
Regards,
Navin.
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
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.
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
Hi Bala,)).Result Field.
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
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.
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
Hi Bala,
I created a sample app where I am taking data from my Employee Table list.
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:
Hope this will help you. If not please let us know, we will try our best to help you out.
Thanks
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 ?
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.
I have an SP list where I am using Customize Form. Was able to get the cascading dropdowns to work for a single department’s choices. I have a Cascading Drop Down list which has 3 columns
Title (Group) they choose their group
Discipline (shows 5 different group choices)
Assigned (shows the person’s name)
I need to add a dropdown in the form now for people to choose a department and then have it show the appropriate choices for THEIR department. I tried creating a new Cascading list that had 4 columns
Title (department)
Group – this now showed every choice in the group column
Discipline – showed all disciplines
Assigned – had the person’s name listed mutiple times
Is there a way to use a single list for both Departments and have it filter out just showing the appropriate choices for them?
Thanks for any words of wisdom you can provide.
Hi Diana,
Can you provide the screenshot and formula of the list and form respectively? So that I can understand the structure.
Thanks.
Hi, is there a way to set default value in cascading drop-down? For example.. if you have three fields, Field A = A, B, C Field B = Red, Green, Yellow and Field C = Banana, Mango, Orange .. so the idea is if you select an item from field A, fields B and C should stay blank until user click to pick but still do the cascading? thank you
Hi Rakesh,
Great tutorial. In fact I used it to implement my own cascading dropdown however I am facing an issue where I have a gallery and am populating it with the results of my cascading dropdown.
How do i filter the results to exclude the results where the third column is blank ?
My expression is : Filter(colRiskRegisterSource, RiskCategory = cbxRiskCategory.Selected.Result && RiskGroup = cbxRiskGroup.Selected.Result) and this is working fine but in some cases the results may return and empty item. I want to filter that out. Any idea ?
Thanks
Hi Darren,
you can use IsBlank() function to validate the blank values.
Check this link. If you are still facing the issues, please let us know. We will try to resolve it.
Thanks