Unanswered

Multiselect Combobox/Checkbox in 3 cascading levels

(0) Share

Report

Posted on by Mikello-6626

I'm trying to setup a form where users can select multiple options from CB1 leading to cascading CB2 leading to CB3, all multiple select. Data needs to submit to a SP list, preferably 3 separate columns where users should be able to filter for each value. I'm open to ideas on how to setup the data, but in my scenario, CB3 has about 100 options in total, so I was thinking along the lines of a lookup column to another SP list - but I'm open to anything that is easier.

Example:

Combobox1 dropdown (Primary Col)	Combobox2 dropdown (Secondary Col)	Combobox3 dropdown (Tertiary Col)
Fruits	Fresh	Apples
Veggies	Frozen	Bananas
		Blueberries
		Asparagus
		Eggplant
		Spinach

Mapping is as below:

Fruits	Fresh	Apples
Fruits	Fresh	Bananas
Fruits	Frozen	Blueberries

Vegetables	Fresh	Asparagus
Vegetables	Fresh	Eggplant
Vegetables	Frozen	Spinach

User should be able to select Multiple options from Combobox1, Combobox2 or Combobox3.

Ex. of my selection:

Fruits->Fresh->Apples, Bananas

Vegetables ->Fresh->Asparagus, Eggplant

Vegetables->Frozen ->Spinach

Combobox1, 2 and 3 need to be prompted to select something and not leave it blank.

@RezaDorrani @yashag2255 @Pstork1 @PowerAddict

Categories:

Building Power Apps

I have the same question (0)

All responses (14)

Answers (2)

v-albai-msft on at

Like (0)

Report

Hi @Mikello-6626 ,
Do you already have lists that shows different relationships like your second mapping example?
This modal is something like relationships between "State-City-District".
If no, you must first create two lists that has such relationships. The first list must show relationships between Column1 and Column2, the second list must show relationships between Column2 and Column3. After we have these records, we can do this.
Best regards,
Allen

Was this reply helpful? Yes No
Mikello-6626 90 on at

Like (0)

Report

Yes of course I have it. I’m trying to understand how I can display 3 combo box selections so that it is clear to user that “Apples” is a subset of “Fruits” and “Fresh”. I need help with designing the form.

Was this reply helpful? Yes No
v-albai-msft on at

Like (0)

Report
Hi @Mikello-6626,
I did a test on my side for your reference. You can refer to my formula and edit based on your situation and data structure to meet your own requirement.
1. For simple test, I create just one list called "list12"(all columns are Text column).
2. In my app, I have three combo boxes called "ComboBox1", "ComboBox2" and "ComboBox3".
3. For ComboBox1, set its Items property to(use Distinct function to remove duplicate😞
Distinct(list12,Primary)
Set its OnChange property to:
Clear(secondaryuser);Collect(secondarytest,Ungroup(ForAll(ComboBox1.SelectedItems,Filter(list12,Primary=Result).Secondary),"Value"))
4. For ComboBox2, set its Items property to:
Distinct(secondarytest,Secondary)
Set its OnChange property to:
Clear(tertiarytest);Collect(tertiarytest,Ungroup(ForAll(ComboBox2.SelectedItems,Filter(list12,Secondary=Result&&(Primary in ComboBox1.SelectedItems)).Tertiary),"Value"))

5. For ComboBox3, set its Items property to(you can also use Distinct function to remove duplicate):
tertiarytest
Best regards,
Allen

Was this reply helpful? Yes No
Mikello-6626 90 on at

Like (0)

Report

@v-albai-msft Can you please let me know the column types for Combobox1, 2 and 3 in the master list? Is it a lookup column to list12? I need the value of Combobox1, 2 and 3 get submitted to master list in SharePoint.

Was this reply helpful? Yes No
v-albai-msft on at

Like (0)

Report
Hi @Mikello-6626 ,
Depends on your own configuration, you need to do some edit based on my formula.
In my example, I am using three Text columns.
If you are using lookup columns, for example for the first combo box, you should try formula like this:
Distinct(list12,lookup1.Value)
Best regards,
Allen

Was this reply helpful? Yes No
Mikello-6626 90 on at

Like (0)

Report

@v-albai-msft I am getting kind of stuck here with multi-select lookup value. It seems like as soon as I add a Filter and Distinct function to the Items property of the comboboxes, the selected values don't get recorded in the SharePoint master list. This is my setup:

Master List:
Master list column setup:
Fruit_Mul, FruitType_Mul, FruitDetail_Mul are all lookup columns.

Lookup lists:
I have 3 lookup lists - Fruit, Fruit Type, Fruit Detail
All columns in lookup tables are Text fields
Second lookup list is Fruit Type with 2 columns: Title and Fruit
Third lookup is Fruit Detail with 3 columns: Title, Fruit, Fruit type
In Powerapps, as soon as I set Items property to Distinct(Fruits,Title) my values don't get recorded in SharePoint after submission:

Same problem with 2nd combobox displaying Fruit Types, both cascading and updating after submission is not working

Why is the multi selection value from Lookup column with Distinct/Filter functions not updating list item in SharePoint. Your help is much appreciated, Allen.

Was this reply helpful? Yes No
Mikello-6626 90 on at

Like (0)

Report

I created 3 lookup lists instead of one because I figured that if I can just use the “Title” column from each lookup list into each combobox, I can avoid the use of Distinct. But then, how can I filter the values from one to another. I think a lookup column is making some difference and is not the same

Was this reply helpful? Yes No
Mikello-6626 90 on at

Like (0)

Report

@v-albai-msft any assistance is appreciated

Was this reply helpful? Yes No
Verified answer

v-albai-msft on at

Like (0)

Report
Hi @Mikello-6626 ,
It is more clearly after you provide your data structure.
Based on your current four lists, I did a test on my side for your reference.
1. For the first combo box(called “DataCardValue2” in my form), use the default code for its Items property:
Choices([@'Master list'].Fruit_Mul)

Set OnChange property of the first combo box to:
Clear(secondarytest);Collect(secondarytest,Ungroup(ForAll(DataCardValue2.SelectedItems,Filter(FruitType,Fruit=Value).Title),"Value"))

Pay attention about the result format of the first combo box(because this is a lookup column in your list), the returned table has two columns: Id and Value. See below screenshot, so for other two combo box, we need to change their result to this format too, then you can save your choices into your list.

2. For the second combo box (called “DataCardValue3” on my side), set its Items property to:
AddColumns(RenameColumns(secondarytest,"Title","Value"),"Id",LookUp(FruitType,Title=Value,ID))

Set its OnChange property to:
Clear(thirdtest);Collect(thirdtest,Ungroup(ForAll(DataCardValue3.SelectedItems,Filter(FruitDetails,'Fruit Type'=Value).Title),"Value"))

3. For the last combo box, set its Items property to:
AddColumns(RenameColumns(thirdtest,"Title","Value"),"Id",LookUp(FruitDetails,Title=Value,ID))
Check result this time.
Best regards,
Allen

Was this reply helpful? Yes No
Mikello-6626 90 on at

Like (0)

Report

@v-albai-msft Hi Allen, thank you so much for getting back to me with this. I think this is working great, filtering and recording properly. The only issue I see is, when user is filling the form, if they select Fruit, Fruit type and if they delete one of the fruit choice, the associated fruit type does not get deleted and it gets submitted. In other words, while the new form is open, is it possible to dynamically eliminate FruitType_mul and FruitDetail_Mul selected choices based on Fruit if it gets changed before submitting the form - so that mismatched Fruit Type/Detail does not get submitted? Below is the problem:

New form, first I select these as choices:

As user, before submitting I realize I make mistake in selecting fruit, so I change it to just Dairy:

As you can see in above, "Fresh Fruits" and "Apples" Bananas" still remain in form as valid selections. Even though if I click on dropdown, it s filtering properly. But user can make mistake and submit this form. But it will not match actual data mapping. Hope this is making sense. Any ideas to dynamically delete associated lookup values from selection and force user to submit with proper choices?

Was this reply helpful? Yes No