Cascading Dropdown with SharePoint Lookup column

[ray3]

Hello,

I am struggling to build a cascading dropdown based on a SharePoint Lookup List:

The SharePoint Lists look like this:

The PartFeatures_Material list can be considered as a n to n mapping list.

Now I have a first dropdown list which is connected to the PartFeatures List where I can see all available PartFeatures:

Distinct(PartFeatures;Title)

In the second dropdown I would like to see all Materials related to my selection on dropdown1. F.e. If I select PF3 at dropdown1, dropdown2 should show me M1,M3,M4

In dropdown2 I have the following code:

Filter(PartFeatures_Materials;PartFeature = Dropdown1.Selected.Result)

I am not able to see any Material value (I think because its a lookup list and a more complex datatype?)

Thank you in advance!

Best Regards

[mcolbert]

@ray3 

First, if you put just PartFeature_Materials as the Items for the second dropdown, do all values appear as expected? 

Not sure how large the list is, but maybe you can try pre-loading the SP lists into collections in the App OnStartup

ClearCollect(colPartFeatures, 'Part Features');

ClearCollect(colPartFeaturesMaterials, 'PartFeatures_Materials')

then use 

Filter(colPartFeaturesMaterials;PartFeature = Dropdown1.Selected.Result)

this essentially removes SharePoint from the equation. May not be realistic if the lists are very large.

Hope this helps, 

Mike

[v-yutliu-msft · Accepted solution]

Hi @ray3 ,

Could you tell me the data type of PartFeature field and Material field in PartFeatures_Material list?

Is your datasource sharepoint list?

If their data type is not text, then the formula should be a little different.

Based on your description, I assume that your data source is sharepoint list and these two fields are choice type(single choice).

If so, please set drop down1's Items:

Choices(PartFeatures_Materials.PartFeature)

set drop down2's Items:

Distinct(Filter(PartFeatures_Materials,PartFeature.Value=Dropdown1.Selected.Value),Material.Value)

Then drop down2's items will filter based on the selection of drop down1.

Best regards,