cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pocketens
Level: Powered On

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
TimRohr
Level 10

Re: Cascading Dropdowns - Cant seem to get it.

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.

pocketens
Level: Powered On

Re: Cascading Dropdowns - Cant seem to get it.

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

TimRohr
Level 10

Re: Cascading Dropdowns - Cant seem to get it.

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.

pocketens
Level: Powered On

Re: Cascading Dropdowns - Cant seem to get it.

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

TimRohr
Level 10

Re: Cascading Dropdowns - Cant seem to get it.

And the relationship of Company and Market?

...

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

pocketens
Level: Powered On

Re: Cascading Dropdowns - Cant seem to get it.

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. 

TimRohr
Level 10

Re: Cascading Dropdowns - Cant seem to get it.

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.

pocketens
Level: Powered On

Re: Cascading Dropdowns - Cant seem to get it.

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. 

pocketens
Level: Powered On

Re: Cascading Dropdowns - Cant seem to get it.

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
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (5,247)