Multi-value cascading dropdown

(0) Share

Report

Posted on by 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

Categories:

Building Power Apps

All responses (12)

Answers (1)

Sort by

Filsdegandalf 44 on at

Like
a
(0)

Report
Copy link

Link copied!

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 Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
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 24 on at

Like
a
(0)

Report
Copy link

Link copied!
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 Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
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 24 on at

Like
a
(0)

Report
Copy link

Link copied!

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 Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!

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 24 on at

Like
a
(0)

Report
Copy link

Link copied!

Model is a single line of text
akosila 24 on at

Like
a
(0)

Report
Copy link

Link copied!

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

v-xida-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
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-operations-for-sharepoint

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,
akosila 24 on at

Like
a
(0)

Report
Copy link

Link copied!

yes 2nd combobox contains the correct values