Multi-value cascading dropdown

[akosila]

How can I implement a multi-value combobox to have a cascading functionality.

For example I have this form which asks the users what cars do they have. Brand can be multiple and same with Model.

user can pick for example both Honda and Toyota, then the next dropdown will show all applicable Model of toyota, and user can have Honda Civic and Toyota Camry for example.

Brand	Model	Variant
Honda	Civic	S
		V
	CRZ	S
		V
	HRV	S
		V
Toyota	Prius	2018
		2019
	Camry	2018
		2019
	Avalon	2018
		2019
Chevrolet	Camaro	2019
		2020
	Colorado	2020
	Bolt	2019

[Filsdegandalf]

Hello,

Assuming your database is called "Car"

You should have in the first dropdown called drpBrand you should set "Distinct(Car)" as Datasource

In the second dropdown called drpModel you should set "Distinct(Filter(Car, Brand = drpBrand.Selected.Value))" as Datasource

In the third dropdown called drpVariant you should set "Filter(Car, AND(Brand = drpBrand.Selected.Value, Model = drpModel.Selected.Value))"  as Datasource

Hope that help

Regards,

[v-xida-msft]

Hi @akosila ,

Do you want to populate another Dropdown box based on the selected Brands in your first Dropdown box?

Could you please share a bit more about your data source? Is it a SP List or a Excel table?

Based on the needs that you mentioned, I think the Filter function could achieve your needs. I have made a test on my side, please take a try with the following workaround:

Set the Items property of the ComboBox1 (Brand ComboBox) to following:

Distinct('YourTable', Brand)

set the SelectMultiple property to true.

Set the Items property of the ComboBox2 (Model ComboBox) to following:

Distinct(
            Filter('YourTable', Brand in ComboBox1.SelectedItems.Result),
            Model
)

Please consider take a try with above solution, check if the issue is solved.

Best regards,

[akosila]

It seems not to work. I tried using your formula using this to suit my list

Distinct(Filter('MyList', 'Title' in ComboBox4.SelectedItems.Result),'Model')

'Title' is Brand

there's an error/warning that says:

The right side of the "in" operator should be a column name from the correct data source. The data source might not be able to process the formula and might return an incomplete data set

[v-xida-msft]

Hi @akosila ,

Based on the formula you provided, I think there is something wrong with it. Please consider modify your formula as below:

Distinct(
         Filter('MyList', Title in ComboBox4.SelectedItems.Result),         /* <-- Please type Title rather than 'Title' */
         Model                                                              /* Please type Model rather than 'Model' */
)

Please consider modify your formula as above, then check if the issue is solved.

Best regards,

[akosila]

Hi @v-xida-msft 

I modified it and still the same error. Take note that the columns are all in one sharepoint list. Title is a column, same with Model. 

[v-xida-msft]

Hi @akosila ,

Could you please share a screenshot about the error within your app?

Could you please share a bit more about the Model column in your SP list? Is it a Text column or Choice type column?

Best regards,

[akosila]

Model is a single line of text

[akosila]

@v-xida-msft Here's an image that shows the controls, formula bar, and error

[v-xida-msft · Accepted solution]

Hi @akosila ,

Does the second ComboBox list values properly within your app?

Based on the issue that you mentioned, I suppose that you have faced a Delegation warning issue with your Filter formula. Firstly, the Delegation warning issue is not an error, it just told that you could not delegate the data process to your SP list data source itself, instead, you could could only process your data locally in your app.

In default, you could only process 500 records locally at most within your app. You could change the "Data row limit for Non-delegable queries" option to maximum value -- 2000 within Advanced settings of App settings, then you could process 2000 records locally at most. If the amount of your SP List Records is not more than 2000, you could ignore this warning issue.

Note: Currently, the 'in' operator is not delegable within SP list data source, please check the following article for more details:

https://docs.microsoft.com/en-us/connectors/sharepointonline/#powerapps-delegable-functions-and-oper...

If the amount of your SP List is not more than 2000, and you want to get rid of this Delegation warning issue, please take a try with the following workaround:

Set the OnSelect property of App control to following:

ClearCollect(RecordsCollection, 'MyList')

Then set the Items property of the second ComboBox (Model ComboBox) to following:

Distinct(
         Filter(RecordsCollection, Title in ComboBox4.SelectedItems.Result),
         Model                                                              
)

then re-load your app (fire the OnSelect property of the App control), then check if the issue is solved.

Best regards,