Having difficulty with cascading dropdowns

[vfdd]

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

[v-siky-msft]

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 

[vfdd]

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? 

[v-siky-msft]

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

[vfdd]

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 !

[v-siky-msft · Accepted solution]

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 

[vfdd]

yeah that one worked thanks ! 🙂