You’re offline. This is a read only version of the page.
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.
Hi, So what is Result in this?
Share
Report
All responses (
Answers (Clear(secondaryuser);Collect(secondarytest,Ungroup(ForAll(ComboBox1.SelectedItems,Filter(list12,Primary=Result).Secondary),"Value"))i get an error while using this
Can you please explain how the item property has to be updated in case we have duplicate values and has to be listed in the dropdown only once.Also what has to be written in the "Update Property"
AddColumns(RenameColumns(thirdtest,"Title","Value"),"Id",LookUp(FruitDetails,Title=Value,ID))
Thank you so much, your solution works perfectly.
Hi @Mikello-6626 ,
You can think of Reset function, use this function on OnChange property of combo box based on your need.
For example, on DataCardValue2 control, after each change action, Reset DataCardValue3 control and DataCardValue4 control.
The only downside is that all selected options in the second and the third combo box will be clear, user need to select again). But this did work for avoiding users selecting wrong values.
Best regards,
Allen
@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?
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
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
@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.
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
#1
WarrenBelz
146,518
Most Valuable Professional
#2
RandyHayes
76,287
Super User 2024 Season 1
#3
Pstork1
65,692
Most Valuable Professional
