cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper III
Helper III

Having difficulty with cascading dropdowns

So I'm having difficulties with multi-level cascading dropdowns.   I use these all the time  but can't figure this one out at all..  I keep getting circular reference areas but can't figure out where they are and I must be missing something obvious !

 

Here's the setup

 

DD1   -datasource is a sharepoint list with one column BU.  It's actually in a collection called CollBU  the ID for this list is renamed BUID.  I need this ID for the next dropdown so I have a text box  TxtBUID.txt  which pulls it out like this.. 

First(Distinct(Filter(CollBU,Title=DD1.Selected.Title),ID)).Result

that works fine.

 

I then want to use that ID for the next dropdown  DD2  which comes from a collection called CollSpoc the sharepoint data source of this dropdown has 3 columns BSUID (which is the BUID number from list 1), Area, SPOC   I want this dropdown to only show the relevant Area's for whatever is chosen in DD1  Here's what I put

Distinct(Filter(CollSpoc,BSUID=TxtBUID.Text),Area)

 and I immediately get a circular reference error.  Not only that but the next level down, which works normally if I haven't filtered DD2 also gives a circular reference error.

Here's what it contains and it works fine if I just leave DD2 with the full unfiltered list but the error carries down if I try to filter DD2.

First(Distinct(Filter(CollSpoc,Area=DD2.SelectedText.Area),SPOC.DisplayName)).Result

 No clue ! 

 

Any ideas?  what am I doing wrong?

 

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support
Community Support

Hi @vfdd ,

 

Probably due to some of the properties in these three controls, the best way to do is to remove and rebuild these controls.

 

In addition, you said there is a incompatible error on the formula, please use Text() function to convert the ID to string type.

 

Distinct(Filter(CollSpoc,BSUID=
Text(LookUp(CollBU,Title=DD1.Selected.Title).ID)
),Area)

Hope this helps.
Sik 

View solution in original post

6 REPLIES 6
Community Support
Community Support

Hi @vfdd ,

 

What is the Items property of DD1?

If you want to pull out the ID,  the following formula should be more reasonable.

DD1.Selected.BUID

 

 And modify the Items property of DD2:

Distinct(Filter(CollSpoc,BSUID=DD1.Selected.BUID),Area)

 

Hope this helps.

Sik 

Thanks @v-siky-msft  The items of DD1  are  CollBU.UnitName  not the ID so unfortunately your suggestion to pull out the id on the label doesn't work which in turn means the next suggestion doesn't work either.  But it has given me a thought and maybe I could use a combo box on DD1 and bring in UnitName as well as ID. Although I can't figure out why it won't work when I have an almost identical set up in another app with no issues? 

Community Support
Community Support

Hi @vfdd ,

 

The reason for the circular reference error is that it is trying to reference other properties that affected by its value, so please try to re-create a new TxtBUID box, or add the formula to reference the ID of selected item directly in Items property of DD2 as follows. I also make a bit simplification on that formula.

Distinct(Filter(CollSpoc,BSUID=
LookUp(CollBU,Title=DD1.Selected.Title).ID
),Area)

 Hope this helps.

Sik

Thanks Sik still can't get it.. your option there gives me a yellow triangle warning of comparing different types. I'll try changing the txtBUID box see if that works.. What I don't understand is that this set up is almost identical to another app I have with no issues there at all !

 

Community Support
Community Support

Hi @vfdd ,

 

Probably due to some of the properties in these three controls, the best way to do is to remove and rebuild these controls.

 

In addition, you said there is a incompatible error on the formula, please use Text() function to convert the ID to string type.

 

Distinct(Filter(CollSpoc,BSUID=
Text(LookUp(CollBU,Title=DD1.Selected.Title).ID)
),Area)

Hope this helps.
Sik 

View solution in original post

yeah that one worked thanks ! 🙂

Helpful resources

Announcements
News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

Power Apps Community Call

Power Apps Community Call- January

Mark your calendars and join us for the next Power Apps Community Call on January 20th, 8a PST

PP Bootcamp Carousel

Global Power Platform Bootcamp

Dive into the Power Platform stack with hands-on sessions and labs, virtually delivered to you by experts and community leaders.

secondImage

Power Platform Community Conference On Demand

Watch Nick Doelman's session from the 2020 Power Platform Community Conference on demand!

Top Solution Authors
Top Kudoed Authors
Users online (10,211)