cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Cascading Dropdowns - Cant seem to get it.

Hello, Iam having the hard time with cascading dropdowns. I have a user who simply wont use the powerapp form I created for her until there is a way to limit choices from the "Company Name" field to the "Market" field. I have tried looking it up but none of the solutions seem to apply. Is this something that can be accomplishe through "Rules"? ThanksSharepoint1.JPG

18 REPLIES 18
Highlighted
Solution Sage
Solution Sage

What is the relationship between Company and Market? Are we talking SharePoint lists, so the source for the Items of the Market dropdown is Choice() function? Post what you've tried as the formula for the Items of both so we can try to help.

Highlighted

THey are both choice columns in a sharepoint list. I cleared the app and started fresh, so right now its just the ordinary  choice column setup

Highlighted

You're going to need to provide more information. I take it this is a form for a SharePoint list. That list has a field for Company which is a lookup to another SharePoint list. It also has a field for Market which is a lookup to a third SharePoint list. Am I right so far?

 

Because that still leaves the question... what do you want to see happen between the two dropdowns? Do you pick the Company first and then you want a filtered set of Markets? What is the relationship?

1) Are the Markets associated with only certain Companies (one to many)? That is, Company A has Markets 1, 3, and 5, and Company A is the ONLY Company to have those. Company B has (and is the only Company to have) Markets 2, 4, and 7.

2) Can Companies be associated with multiple markets (many to many)? That is Company A has Markets 1, 3, and 5, while Company B has 1, 3, 4, and 7. If I pulled up Market 1's associated Companies, I should see Company A and Company B listed.

Highlighted

No there are no lookups to other lists, its just choices. 

Highlighted

And the relationship of Company and Market?

...

...the behavior you want when you select Company?

Highlighted

Company and Market have no defined relationship in the sharepoint list. In reality Company "A" has gas, power, electrical, communication Markets. Company "B has gas and communication Markets. Company "C" has communication and "other". I want the behavior when I select company to only show the markets that it has. 

Highlighted

This is where a bit of experience with relational databases helps. The situation you describe is a many to many relationship:

Each Company can have Many Markets, and

Each Market can be associated with Many Companies

 

In this case, you need another list. In a database where your data was normalized, you would have one list (table) for Companies, one list (table) for Markets, and one that was the intersection of Company & Markets.

 

For your setup, you can probably accomplish it with single field lists, but be aware that the purpose of the separate lists/tables is to track the data that relates to that thing... so all of the data that is specific to (and only specific to) a Company would have a place in the Company list. No matter if you have one field (the company name) or multiple, you need to make sure you have a unique identifier to the record (maybe the company name isn't unique; there might be multiple 'CT Industries' ... one of which is into Calibrated Turkeys and one that is into Cat Translators). That unique identifier is what you'll use to create the intersection with Market.

 

Enough theory. Let's get practical.

Move your choices out to their own list. The listed company options will go to a new Companies list; the listed market options will go to a new Markets list.

Create a third list, I'll call it CompanyMarkets that has 2 lookup fields: one to the Companies list and one to the Markets list

Populate that list with the various intersections of Company and Market that you know will exist. For instance, Company A exists in the Companies list, and gas, power, electrical, communication, and other exist in the Markets list. If Company A has gas, power, electrical and communication, you would add 4 records to your list:

Company      Market
=======================
   A         Gas
   A         Power
   A         Electrical
   A         Communication

Company B has gas and Communication Markets... so 2 more records in the same list:

Company      Market
=======================
   B         Gas
   B         Communication

And Company C has Communication and Other... so 2 more records:

Company      Market
=======================
   C         Communication
C Other

In all, that's 8 records in the new list.

 

Once you have that architecture in place, your Company and Market dropdowns need to change. The Items property of the Company will point at the Company list. The Items property of the Market dropdown will point to the CompanyMarkets list, but it will Filter() based on the selection in the Company dropdown. Something like:

Filter(CompanyMarkets,Company=yourCompanyDropDown.Selected.Value)

...and you would set the Value property of the Market dropdown to be the field with the name of the Market.

 

Straightforward, but it takes a change to how you structure your data.

 

(BTW, there are ways where you could dynamically decide you wanted to Filter the Company based on the Market you chose, in case you wanted to go in that direction -- pick the Market first, then see the Companies operating in that Market -- but that doesn't sound like what you're after).

 

Post back if you still need more help.

Highlighted

Thank you so much! I didnt realize I was missing that much of a foundation unfortunately. I will work with this information and if I have any issues I will post back here. 

Highlighted

YPowerappserror.JPGeah I think I got so far and its not exactly working. See attached. I tried adding the "Choices" to the filter and its just not getting there. 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Community Highlights

Community Highlights

Check out the Power Platform Community Highlights

Top Solution Authors
Top Kudoed Authors
Users online (7,337)