Cascading Multip Select Combo box

[vfdd]

Looking for suggestions on the best way to approach this or syntax solution !

Sharepoint list 1:   Field1: DataCatID  Field2: DataCategory

Sharepoint List 2:   Filed1 DataCatID Field2: DataSubCategory

I have CB1  which uses Sharepoint List 1 Field2(DataCategory) for it's items and it's a multi-select combo box.

I then have CB2 also a multi select combo box and I want to filter this to show only Sharepoint List 2  Field2 (DataSubCategory) where DataCatID is the same as those chosen in CB1

Hope you can follow !    Thanks

[vfdd]

FYI   here's what I put. I'm getting an error against the equals sign but I know I'm close because it is working !  shows the right items just showing the error in edit mode  

Distinct(Filter(CollDataAttrib,DataCatID=
Text(LookUp(CollDataCat,DataCategory=CBDataCat.SelectedItems.Result).ID)
),Title)

[vfdd]

Any suggestions?  if I change selectitems to selected  then the error goes away however it only picks up the items from the last choice on the previous combo box

[v-qiaqi-msft]

Hi@vfdd,

Based on the issue that you mentioned, do you want to create cascading Combo Box?

Could you please share a bit more about the DataCategory and DataSubCategory, are they all Text type?

I have a test on my side, please take a try as below.

I advise that you could combine the two SP lists into a whole collection, then you could filter one column based on another column.

Add a Button and set the OnSelect property as below:

Collect(ColTT,AddColumns(ListA,"SubCa",LookUp(ListB,DataCatID=ListA[@DataCatID],DataSubCategory)))

Note: To avoid the  disambiguation issue, please refer to the Table[@FieldName].

Set the first Combo Box Items property as below:

Distinct(ColTT,DataCategory)

Set the second Combo Box Items property as below:

Filter(ColTT,DataCategory in ComboBox1.SelectedItems.Result).SubCa

Hope it could help.

Best Regards,

Qi

[vfdd]

thanks @v-qiaqi-msft  That almost works, I think the approach with the collection is right but I'm getting an incompatible item error 

Here's my syntax 

Collect(CollDataAttributes,AddColumns(DataCategory,"SubCa",LookUp(DataAttributes,DataCatID=DataCategory[@DataCategoryID],DataAttribute)))

DataCategoryID is an ID field where as DataCatID is text field, I've tried putting Text( etc... before the look up but can't quite get it right

And when this field was a dropdown and not a multi-select combo this worked for me.  (Unfortunately I had to change it to multi-select combo for operational reasons)

Distinct(Filter(CollDataAttrib,DataCatID=
Text(LookUp(CollDataCat,DataCategory=DDDataCat.Selected.Result).DataCategoryID)
),DataAttribute)

[v-qiaqi-msft · Accepted solution]

Hi@vfdd,

Could you please tell me the specific error message and how you set the Items property of these 2 Combo Boxes?

In my scenario, I set the Items property for the Combo Boxes as below:

Set the first Combo Box Items property as below:

Distinct(ColTT,DataCategory)

Set the second Combo Box Items property as below:

Filter(ColTT,DataCategory in ComboBox1.SelectedItems.Result).SubCa

Best Regards,

Qi