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

CDS One to Many Relationships in Canvas Apps with cascading dropdowns/comboboxes

Hi All, 

 

We have an existing canvas app that we're excited to move to using the new Relational Data, option sets, and other new features for CDS functionality.  

 

We have a number of cascading dropdowns and comboboxes in our app.  By cascading, I mean that you first choose the top level dropdown, then the items in the next dropdown are filtered by that selection.  Then a choice is selected in that dropdown, and the next dropdown is filtered by those choices.  As we've got some proprietary stuff in the data types, I'll use Country, State, City, Street, Street Number as an example.  

 

Data source is CDS, with the following relationships built in Dynamics/Powerapps

Country is 1:N to State

State is 1:N to City

City is 1:N to Street

Street is 1:N to Street Number

 

First you select the Country, then the next dropdown shows a lists of states in that country, then a state is selected, and the next dropdown shows the list of cities in that state, one is selected, and so on.  At the bottom level, we have 10,000 - 100,000 records, so the hierarchical dropdowns are super important to getting to the proper record quickly.

 

Currently, the app is written using Filter commands for the items in each combobox/dropdown and works very well.  I'll use ComboBoxes for the example, but we have a mixture of comboboxes and dropdowns

 

Items are defined as such:

CountryComboBox: 'Countries'

StateComboBox: Filter(States,CountryText=CountryCombobox.Selected.'Primary Name')

CityComboBox: Filter(Citys,StateText=StateCombobox.Selected.'Primary Name')

StreetComboBox: Filter(Streets,CityText=CityCombobox.Selected.'Primary Name')

StreetNumberComboBox: (StreetNumbers,StreetText=StreetCombobox.Selected.'Primary Name')

 

The workaround we've had to use (that we're very excited about getting away from) is that for each Entity, when we add records, we copy over the Looked up Field for each relationship from the GUID into a Text field, which we then use for the filtering.  For example, when we add a new State, we associate it to a Country in our model-driven app to link the GUID, but then we have to copy over the Country Look up field into the CountryText field (this is a clunky workaround to the non-delegable Filter onto a Look up field, since you can delegate a filter onto a text field in CDS)

 

Based on the new guidance, it seems that the new items for the dropdowns should look much simpler, and avoid the workaround for filtering based on text fields copied over from lookup fields.  From what we can gather, the items lists should look like this for the comboboxes with the new functionality

 

CountryComboBox: 'Countries'

StateComboBox: CountryCombobox.Selected.States

CityComboBox: StateCombobox.Selected.Citys

StreetComboBox: CityCombobox.Selected.Streets

StreetNumberComboBox: StreetCombobox.Selected.StreetNumbers

 

This follows the formula as I understand it - the items for a Second Control based on selected value from a First Control should be FirstControl.Selected.'Related Entity Name'

 

The problem is that while those functions do not throw errors, they return no records.  Each dropdown/combobox shows no items (aside from Country). 

 

What am I doing wrong? I've tried everything I can think of over the past couple days and can't get it to work.

 


We've looked through these posts to no avail
https://powerapps.microsoft.com/en-us/blog/new-cds-capabilities-for-canvas-apps-enter-preview/
https://powerapps.microsoft.com/en-us/blog/one-to-many-relationships-for-canvas-apps/
https://powerapps.microsoft.com/en-us/blog/option-sets-and-many-to-many-relationships-for-canvas-app...

 

Helpful resources

Announcements
firstImage

PowerApps Monthly Community Call!

Join us next Wednesday for our Demo Extravaganza, October 16, 2019 8am PDT.

firstImage

Microsoft Business Applications Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

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

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

SecondImage

Power Platform Summit North America

Register by September 5 to save $200

Users Online
Currently online: 176 members 5,287 guests
Recent signins:
Please welcome our newest community members: