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 ,
I will use some names in blue that you will have to change to your control names.
The dropdowns are DropDown1 and DropDown2, the data source is MyDataSource, the common field is MatchField .
Assuming you want Distinct values - DropDown1

Distinct(
   MyDataSource,
   MatchField
).Result

for DropDown2

Distinct(
   Filter(
      MyDataSource,
      MatchField=DropDown1.Selected.Result
   ).MatchField,
   MatchField
)

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.

[Anonymous]

Hi @WarrenBelz ,

Thanks very much for your reply. Unfortunately I can't get the second bit to work on my end! Zooming in on the second filter:

Filter(
MyDataSource,
MatchName=DropDown1.Selected.Result
).MatchName

What is 'MatchName' referring to? I couldn't see it mentioned in your blue text. Also, if I replace 'MatchName' with the name of the field I'm trying to compare, I get an 'invalid argument type' error at the '=' section of the formula.

Cheers 🙂

Joel

[WarrenBelz]

HI @Anonymous ,

Sorry mistype - should be MatchField - I will correct original reply so as not to confuse others looking at it.

[eka24]

Where your Table has no Duplicates:
On Dropdown1:
Table1. Level1

On dropdown2
Filter(Table2,Level1=Dropdown1.Selected.Value,Level2)

If Table1 has duplicates then:
On Dropdown1:
Distinct(Table1,Level1)

On dropdown2
Filter(Table2,Level1=Dropdown1.Selected.Result,Level2)

[Anonymous]

Hey @eka24  and @WarrenBelz ,

Unfortunately these formulas aren't working for me. They break at the '=' sign with 'invalid argument type'. I've seen this error in countless other posts and it's been fixed by appending .Value or .Result or wrapping in Text() etc... but nothing is working for me so it appears circumstantial.

Thanks very much for your assistance. I'll keep working away on my end and if I can figure it out I'll post back.

Cheers 🙂

Joel

[Anonymous]

Hey again @eka24 and @WarrenBelz ,

I did a test with some hardcoded values and got these results:

Dropdown1: Filter(Table1, Level1 = "Australia") - works normally

Dropdown2: Filter(Table2, Level1 = "Australia") - fails with 'invalid argument type'

The 'Level1' field in Table2 is a lookup column that points to Table1, so there might be something I'm missing with converting the lookup value to a text value for comparison in the filter. If you have any suggestions they would be greatly appreciated!

Cheers 🙂

Joel

[eka24]

Is Level 1 in table1 and level1 in table2 both same datatypes.

What about trying SelectedText.Value
Or SelectedText.Result

[WarrenBelz]

Hi @Anonymous ,

Nice to help another Aussie.

We are both able to help you here - I was focusing on the Distinct option more.

Please tag whichever approach is suitable to your situation to continue the thread with.

[Anonymous]

Hey @WarrenBelz ,

Yes, I'm very glad to be able to get help here! I think that just the Filter is the source of my issue. Once I can get that working I should be able to deal with duplicates etc...

@eka24The data types are indeed different. Table1/Level1 is "text" and Table2/Level1 is "lookup". Table2 looks up the Level1 value from Table1. Looking back at the hardcoded tests, it seems like I need to convert the Table2/Level1 value to text , or I'm referring to the lookup value in a completely wrong way!:

Dropdown1: Filter(Table1, Level1 = "Australia") - works normally

Dropdown2: Filter(Table2, Level1 = "Australia") - fails with 'invalid argument type'

Cheers 🙂

Joel