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"? Thanks
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.
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.
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.
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
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:
...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.
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.
Fill out a quick form to claim your user group badge now!
Find out where you can attend!
Features releasing from October 2019 through March 2020
Learn how to build the business apps that you need.