Cascading dropdown based on common data service

[Anonymous]

Hey All,

I am having great difficulty creating cascading dropdown menus based on tables with lookup fields in the Common Data Service. Here is the basic table structure:

Table1

Level1 - Datatype = Text

Table 2

Level2 - Datatype = Text

Level1 - Datatype = Lookup (get data from Table1, Level1)

I want two dropdown menus. The first should get data from Table1, field Level1. The second should filter the results of Table2 so that only Level2's that have a Level1 equal to the selected Level1 are shown. 

I would've thought something like Filter(Table2, Table2.Level1 = Table1.Level1) would've sufficed, but nothing I try along those lines returns anything but errors.

Cheers 🙂

Joel

[WarrenBelz]

Hi @Anonymous The only issue here (and yes, lets get the basics right first) is that the second filter should be

Filter(
   Table2,                                       //your second data source
   YourMatchingField = Dropdown1.Selected.xxxx    //all records with matching field to dropdown
).WhateverFieldYouWanttoDisplay                   //display field from theese in second dropdown

All you have to do is find all the matching records in the second list that match the first drop down selected value and then display a field from that list. It can be the same field as matched, or a different one.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[v-xida-msft]

Hi @Anonymous ,

Are the Table1 and Table2 both CDS Entities?

Based on the formula that you mentioned, I think there is something wrong with your formula. I have made a test on my side, please  consider take a try with the following workaround:

Set the Items property of the Dropdown1 to following:

Distinct(
         Table1,
         Level1
)

Set the Items property of the Dropdown2 to following:

Filter(
       Table2,
       Level1.Level1 = Dropdown1.Selected.Result
)

Please take a try with above formula, check if the issue is solved.

For the Filter formula in your second Dropdown box, you should modify it as below:

Filter(Table2, Level1.Level1 = "Australia")

Note: For Level1.Level1, first Level1 represents the Level1 LookUp column in your Table2, the second Level1 represents the Level1 Text column in your Table1.

Best regards,

[WarrenBelz]

Hi @Anonymous ,

Please note the additional information (and thank you @v-xida-msft for confirming the direction we need to head after getting the basics correct).

[Anonymous]

Hey @v-xida-msft ,

We are steadily getting somewhere! Thanks very much for your input. In answer to your CDS query, yes, both tables are CDS entities. Half of your suggestion is working on my end. Check this out:

Filter(Table2,Level1.Level1="Australia") - works perfectly (hooray!!)

Filter(Table2,Level1.Level1=Dropdown1.Selected.Result) - fails with 'Name isnt valid. This identifier isnt reconized' (red line under 'Result')

If I make the following change:

Filter(Table2,Level1.Level1=Dropdown1.SelectedText) - fails with 'invalid argument type' (red line under '=')

I've seen heaps of similar issues on the forums, so if we can get to the bottom of this then I'm sure it will help many people!

Cheers 🙂

Joel

[v-xida-msft · Accepted solution]

Hi @Anonymous ,

Could you please share more details about the formula you typed within the Items property of the Dropdown1 box?

Please make sure that you have type the following formula within the Items property of the Dropdown1 box:

Distinct(Table1, Level1)

If you set the Items property of the Dropdown1 box to following:

Table1.Level1

then you need to modify the Filter formula within your Dropdown2 box to following:

Filter(Table2,Level1.Level1=Dropdown1.Selected.Level1)

Please take a try with above solution, check if the issue is solved.

best regards,

[Anonymous]

Far out, you've done it! Thank you very much!! @v-xida-msft , @WarrenBelz  and @eka24 : you're all bloody legends :D. I'm going to spend the next few minutes summarising everything so we've got a consolidated solution. I'll post back when I've done so.

Cheers 🙂

Joel

[WarrenBelz]

Thanks @Anonymous ,

Each of us can help you. I am glad you have solved it.

You can consider accepting whatever post/s assisted you in the solution.

[v-xida-msft]

Hi @Anonymous ,

Have you solved your problem?

If you have solved your problem, please consider go ahead to click "Accept as Solution" to identify this thread has been solved.

Best regards,

[Anonymous]

A cascading dropdown menu based on related Common Data Service entities

The following example uses this entity structure:

Entity1
Level1 - Datatype = Text

Entity2
Level2 - Datatype = Text
Level1 - Datatype = Lookup (get data from Entity1, Level1)

Step 1 - Create your entities and set up their relationship (not covered here)

Step 2 - Create two dropdown menus in your Power App and call them Dropdown1 and Dropdown2

Step 3 - Set the Dropdown1's 'items' property to: Distinct(Entity1, Level1)
* 'Distinct' ensures that only unique results will populate the first dropdown and isn't necessary if you already have unique values
** Take note of the text shown against the 'value' property in the right hand panel as it is used in the next dropdown

Step 4 - Set the Dropdown2's 'items' property to: Filter(Entity2, Level1.Level1 = Dropdown1.Selected.Result)
* The 'Result' text should be changed to whatever text is shown against Dropdown1's 'value' property

[Anonymous]

Hey @v-xida-msft ,

Thanks very much for your help. I will mark your final post as the solution. I just wanted to summarise everything in a post so that it's easy for others to follow 🙂

Cheers!

Joel