SharePoint: Cascading Dropdowns in 4 Easy Steps!

[audrieg]

Preparation (data sources):

I've decided to use two SharePoint Lists for this example. One as the app data source, and the other for the dropdown list values. Using a SharePoint list for my dropdown values enables end users to modify the form logic on their own. You could use any data source you prefer.

The control values list (named: Impacts)

Keeping it simple, I created a list called Impacts with two columns as shown below. Title (main request type), and SCategory (aka sub category - I didn't want to use any spaces or symbols there). The list is sorted by Title, Ascending. (I keep these in a separate list so that I can always show the same set of values in my dropdowns, even if there is nothing in the datasource for my app related to these selections.)

The PowerApp List/Data Source (named: CR):

The SharePoint List that will collect the submissions from the PowerApp is very simple so as to facilitate this demo. It has three columns:

• Change Impact (which is really just the Title column renamed)
• Sub-Category (single line of text) - optional note: You could make this a choice list if users might be updating directly in SharePoint classic mode as well, only it can not be a multi-select choice list.
• Answer (multi-line plain text field)
  
  You'll notice I entered 1 item/record manually using the regular SharePoint "New Item" command, that's just to make it easier to customize my forms. (It can be difficult to customize the forms until you have at least 1 record in the list. You can always delete that configiration record later.)

Adding and Configurating the Dropdown Controls in 4 Easy Steps

OK. Now we're ready to have fun.....

Step 1: Generate a New Power App from the SharePoint CR List. PowerApps does most of the work for you, leaving you with a fully functional app with Browse Gallery, Display Form, and Edit/New Item Forms.

Step 2: Add another connection to the app (Content Tab>Data Sources>(far right of screen) + Add data source) that references the "Impacts" list from the site as well. You should now have two data sources in your app, the one PowerApps added for you (destination data source), and the one you just added to the other list.

Tip: You can reuse data sources for controls by dedicating a CDS entity, or other data source like SharePoint for that purpose. For instance, I have a SharePoint site collection that is open to "Everyone except external users", where I keep a bunch of controls stuff - but CDS is perfect for this too! Just remember to permission the shared data source so that all users have the rights to read (but not write), and when using SharePoint also disable search results on each list so that the items don't come up in enterprise search results. Aren't you lovin' the fact that you can add additional data sources to your SharePoint PowerApp views so easily!?

Step 3: Make some room above the gallery on the first screen and add a Dropdown Control (Insert>Controls>Dropdown), I named mine "ddSelectType". Set the "Items" property to Distinct(Impacts,Title) to get all the main topics from our Impacts list into that dropdown. Run to test (F5).

Step 4: We're almost done already! Let's make some more room and add another dropdown, which I named ddSubCategory. We will simply filter the ddSubCategory dropdown based on the selection from the first dropdown.

The Items property of ddSubCategory:

Filter(Impacts,ddSelectType.Selected.Value in SCategory)

Run and test! I just works!

I plan to share many tips on making your apps interesting, and incorporating data validation. Let me know if you'd like any particular topic and I'll do my best to make that happen for you! Keep visiting the Community because I'll be posting video demos with your popular questions asked as well.

Enjoy your PowerApps experience!

Audrie

[UB400]

Great start @audrieg I'm looking forward to seeing more of your demos. As for ideas for more demos, I would love to see an example of calling screens dynamically i.e. based on User input the User gets directed to a related screen e.g. if it was an Inventory App, if the User selected  "Laptops" they would be directed to Screen2, whereas if they selected "Printers" they would get re-directed to Screen3. This would be particularly useful in scenarios where the presentation or collection of data could be handled differently, if it's type A use a screen with a listbox, if it's type B then use a screen with multiline text as an example.

[audrieg]

@UB400 Thank you for reading the blog, and for planning to come back again. I really like your idea to demo dynamic screens because that is a scenario that comes up fairly often in business forms and apps. Tell you what, I will blog about that on Tuesday next week! Just to give you a hint; the Navigate() function can be used in an If/Then statement....more on that Tuesday. Thank you again for your encouraging comment. --Audrie

[UB400]

@audrieg just keeping you honest  Where's my Tuesday blog 

[audrieg]

@UB400 - I like that you keep me honest. It's written and all I have to do it add screenshots! It's late but you'll have it by the end of the week, I promise!

[SanchezCSG]

@audrieg

I need some help...

I need to do a simple formulary. I create a list in Sharepoint called "ServiciosCSG" with two colums "Empresa" and "SCategory".

The first DD works well but the second dont filter. Where is my problem? This is my configuraton

[audrieg]

@SanchezCSG

If your SCategory is a choice list, then try adding .value at the end of your filter formula like this:

Filter(ServiciosCSG;Dropdown1.Selected.Value in SCategory.Value)

If that doesn't work - check the internal name of the SCategory column (where you may have typed something different when the column was created). Let me know if your internal column name is encoded (with x0020 or something like that in the middle), and if that's the case....just reply with the internal column name.

Tip: The easy way to find out what an internal column name is in PowerApps is to look at the column names in the advanced panel.

Thank you,

Audrie

[SanchezCSG]

@audrieg

I created another list and started over, now it works (dont know why :S) but have this message (in Spanish)

[Audrie-MSFT]

@SanchezCSG You can ignore the blue circle message in this case. They are not error messages, they are more informational in nature. I'm glad you got it working too! I am posting the blog you requested right now, so it will be live later today. So sorry for the delay!

Let me know what else you'd like to see,

Audrie             

[audrieg]

@UB400 Here is the post I promised you! I hope you'll forgive me for the delay!

https://powerusers.microsoft.com/t5/PowerApps-Community-Blog/Conditional-Navigation-Triggered-by-Use...

Side Point: If you'd rather that they navigate to the other screen 'right after' the selection in the drop down, then move the "If()" function I explain in the above blog to the OnChange property of the drop down box.

Enjoy!

Audrie

[UB400]

@audrieg awesome, thank you! I knew I could count on you

[VS-Office]

I would like to add one more thing.. if you have repeated items in second dropdown you can enclose this Filter function with Distinct function like this Distinct(Filter(Impacts,ddSelectType.Selected.Value in SCategory), SCategory)

Greetings from Croatia

Vedran

[Audrie-MSFT]

@VS-OfficeThank you for adding that! 

[GizmoVFS]

So, I have three lookup fields in my SharePoint list called Contacts.

1. The first is a lookup from list STEMRegions which contains and ID and STEMRegionFullName.
2. The second is a lookup from list Districts which contains ID, DistrictShortName, STEMRegion (which is a lookup field itself).
3. The third is a lookup from list Schools which contains ID, SchoolFullName, and District (which is a lookup field to Districts).

Districts should only show a list based on the STEMRegion, and School should only show a list filtered on the district chosen.

How would I alter the formula to filter on the IDs of the lists but show the name of the field (STEMRegion, DistrictShortname, and SchoolFullName).  It would need to save the ID as the value.

[Anonymous]

Great blog post @audrieg (@Audrie-MSFT), love this. Was helpful in achieving one of the tasks I kept pondering on how to implement. I know have another task that I need help with. This time around I have one of the list columns as admin name (People field) with a lookup to another Drop down column (Department). So if an item selected in department, then Contacts field should show the list of admin name only (person field). Please how can I achieve this or otherwise should I change the person field to Text field? Would that work. Problem is I need that person field for flow so a notiification is sent to that user base on the department name selected and in the lookup list. Save me from pondering please??!! 🙂 Many thanks in advance.

[AC1]

@audrieg

Hi Audrie, I'm able to setup the cascading dropdowns following you instructions. However, after I save and try to edit the record, the dropdown fields don't show what was saved. How can I correctly display the saved data?

Thanks

AC

[Anonymous]

@audrieg
I have exactly the same issue as @SanchezCSG. I have looked up the correct column name but still hasn't resolved the issue.

Any other suggestions on what can be done?

[Anonymous]

 Hi @audrieg,

I have used a slightly different way because I get a blue dot that says something along the lines of "limited service" . Instead, I used the below in the Item formula, which seems to work but the problem is the drop down items are not visible! 

I think its looking up the information but it just isn't visible. Would you know by any chance and how to fix this?

Thanks in advance.

I have tried 

[Audrie-MSFT]

@Anonymous try adding .Title right after the first close parenthesis. Let me know if that works. I think you just need to say what column will be the result of the filter.

[Anonymous]

Thanks very much @audrieg! Yes this workek - you are a life saver! Thanks again.

May I also ask one more thing please. The cascading drop down works but I'd like the drop down to display as blank first when the user creates a new list item. At the moment, the drop down field displays the first item in the list straight away. I thought a workaround would be to add a "no value" item at the start of the list but this means I then need to add "no value" item in the other cascading drop downs and theres a lot! Is there a formula that I can add so that the drop down controls display a blank field first?

Thanks again

[Hulu]

Hi @audrieg

I had a warning when creating the second dropdown, it says "Delegation warning. The highlighted part of this formula might not work correctly with column "fjsw" on large data sets. The data source might not be able to process the formula and might return coreect results or behave correctly if the data set is incomplete." And the part "ddSelectType" is highlighted and there is wave line under "in". Could you please take a look? Thank you!

[ishantk]

One feasible option is you can add control from Insert tab. Add dropdown list and add list as a datasource which you need take as cascading.

Need to set filter in dropdown lists. so basically same datasource will be used for all drop down lists,  but filter will make it work as cascading.

[Anonymous]

@audrieg

i have cascaded a drop down for multiple column on same sharepoint list and its working fine.
but after saving the data ...the cascading frop down is reseting to default value .and i am not able to see the saved data from cascading drop down......!

Can you please help me with this....

[Anonymous]

Ensure that the DataCard:  Update function is set to'mydropdown'.Selected.Value

[JoanaVB]

Hello @audrieg,

Thank you so much fot this very clear expanation. I was able to reproduce your steps and its working fine, but this newly created dropdowns are not associated with the fields on  list CR or did I miss something? I need to be able to add new records to the CR list where the first 2 fields "Change Impact" and "Sub-Category" will be filled with the selections I make in the 2 Drop Downs. Is that possible? If so, how to do it?

If the answer is  the DataCard:  Update function is set to'mydropdown'.Selected.Value. I cant find it

Thanks in advance, Joana

[Audrie-MSFT]

@JoanaVB The method I posted long ago is no longer necessary (although it should still work). Instead, I suggest trying out the new features for dependent dropdowns. This may get you exactly what you need:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/dependent-drop-down-lists

Thank you for your questions and feedback,

Audrie

[JoanaVB]

Thank you Audrie, I managed to put it to work with the new method and the hints you gavee in this article, but unfortunatly I need this to work in a Document Library and although the dropdown works I am not uble to update the document item with th new edited dropdown values. It simply dosen't save the item. Can you help?