Answered

Cascading dropdown based on common data service

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

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

Categories:

Building Power Apps

All responses (20)

Answers (1)

Sort by

WarrenBelz 156,566 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
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.
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

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 156,566 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

HI @Anonymous ,
Sorry mistype - should be MatchField - I will correct original reply so as not to confuse others looking at it.
eka24 20,925 on at

Like
a
(0)

Report
Copy link

Link copied!

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)
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

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
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

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 20,925 on at

Like
a
(0)

Report
Copy link

Link copied!

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

What about trying SelectedText.Value
Or SelectedText.Result
WarrenBelz 156,566 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

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.
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

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
WarrenBelz 156,566 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
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.