Unanswered

3 combo-boxes affecting each others options

(0) Share

Report

Posted on by RobChilds

I have 3 combo boxes, which link to 3 columns in a dataverse table.

I would like to have each combo box change it's options depending on whether another combo box already has something selected.

For example: -

ComboBox1

Dataverse Column Name = Production

Production Entries = ProductionA, ProductionB, ProductionC

ComboBox2

Dataverse Column Name = Episode

Episode Entries = Episode1, Episode2, Episode3

ComboBox3

Dataverse Column Name = Operator

Operator Entries = Operator1, Operator2, Operator3

So if someone selects ProductionA in ComboBox1, ComboBox2 will only show Episode values that were used on the same row as ProductionA. The same applies to ComboBox3.

Here's the code I have on my ComboBoxes...

Distinct(DataverseTable, Production)

Here's the code I have on my Gallery...

Filter(
DataverseTable,
Or(
IsBlank(cmb_Production.SelectedItems),
IsEmpty(cmb_Production.SelectedItems),
Production in cmb_Production.SelectedItems
),
Or(
IsBlank(cmb_Episode.SelectedItems),
IsEmpty(cmb_Episode.SelectedItems),
Episode in cmb_Episode.SelectedItems
),
Or(
IsBlank(cmb_Operator.SelectedItems),
IsEmpty(cmb_Operator.SelectedItems),
Operator in cmb_Operator.SelectedItems
),
Or(
txtbox_1.Text in Production,
txtbox_1.Text in Episode,
txtbox_1.Text in Operator
)
)

(I have a text box as well for searching but this isn't essential)

The above code works great, but it doesn't change the other comboboxes options when you select an item within ComboBox1 for example.

Hopefully all makes sense!

Any advice, great appreciated.

Many thanks,

Rob

Categories:

General Questions

Connecting to Data

All responses (9)

Answers (0)

RobChilds 94 on at

Like (0)

Report

Re: 3 combo-boxes affecting each others options

Hi @Ethan_R

Please see attached pics. It's a delegation warning, but the Episode and Operator ComboBox's aren't working i.e. when you click on them nothing happens. And the Gallery is blank.

Have attached pics...

Thanks kindly,
Rob

Episode ComboBox Items codeEpisode ComboBoxGallery
Ethan_009 4,840 on at

Like (0)

Report

Re: 3 combo-boxes affecting each others options

Hi @RobChilds ,

Can you share the error if you are getting.
RobChilds 94 on at

Like (0)

Report

Re: 3 combo-boxes affecting each others options

Hi @Ethan_R

I tried turning off the multi-select but it didn't seem to make a difference.
I then turned it back on and changed '=' operator to 'in' as you say and the red X is now a yellow warning triangle, but still doesn't seem to work.

I have checked all the name of the DataverseTable and ComboBoxes are all correct.

Thanks,
Rob
Ethan_009 4,840 on at

Like (0)

Report

Re: 3 combo-boxes affecting each others options

Hi @RobChilds ,

The name might be different or
if you have used multi-select option then use need to use 'in' operator instead of '='.

Hope this helps
RobChilds 94 on at

Like (0)

Report

Re: 3 combo-boxes affecting each others options

Hi @Ethan_R

Thanks for your reply.

I tested it but get red X’s on ComboBox2 , ComboBox3 and the Gallery, so not sure why that’s happening.

Thanks,
Rob
RobChilds 94 on at

Like (0)

Report
Re: 3 combo-boxes affecting each others options

Hi @v-qiaqi-msft

I tested your suggestion and it does work, but not quite as I was hoping…

Have a few questions if I may: -

Is it possible to be able to use the ComboBox’s in any order, so for example the user can start with ComboBox3 which filters the results shown in ComboBox1 and ComboBox2 (as well as the Gallery of course)
How can I add the Distinct function onto ComboBox2 and ComboBox3?

Thanks,
Rob
Ethan_009 4,840 on at

Like (0)

Report
Re: 3 combo-boxes affecting each others options

Hi @RobChilds ,

You need to dependent (cascading) combobox data, to achieve this try the following:
//For the Items Property in ComboBox 1 that is for Production Distinct(DataverseTable, Production) //For the items Property in Combobox 2 that is for Episode Filter(DataverseTable, IsBlank(Combobox1.Selected) || Production = Combobox1.Selected || Combobox1.SelectedItems in Production) //For the items Property in Combobox 3 that is for Operator Filter( DataverseTable, ( IsBlank(Combobox1.Selected) || Production = Combobox1.Selected || Combobox1.SelectedItems in Production) && ( IsBlank(Combobox2.Selected) || Episode = Combobox2.Selected || Combobox2.SelectedItems in Episode) ) //For Items property in your Gallery Filter( DataverseTable, ( IsBlank(Combobox1.Selected) || Production = Combobox1.Selected || Combobox1.SelectedItems in Production) || ( IsBlank(Combobox2.Selected) || Episode = Combobox2.Selected || Combobox2.SelectedItems in Episode) || ( IsBlank(Combobox2.Selected) || Episode = Combobox3.Selected || Combobox3.SelectedItems in Operator) || (txtbox_1.Text in Production || txtbox_1.Text in Episode || txtbox_1.Text in Operator) )

Hope this helps
RobChilds 94 on at

Like (0)

Report

Re: 3 combo-boxes affecting each others options

Hi Qi, thanks for your reply.

In response to your questions...
Do you want to filter Episode and Operator based on ComboBox1 selected?
Yes, but I would also like it to work the other way around...
So ComboBox1 is filtered by what is selected in either ComboxBox2 and/or ComboBox3.
ComboBox2 is filtered by what is selected in either ComboxBox1 and/or ComboBox3.
ComboBox3 is filtered by what is selected in either ComboxBox1 and/or ComboBox2.

Could you please tell me that if all the ComboBox allow multi select or single select?
Currently all the ComboBoxes are set to allow multi-select.

Many thanks,
Rob
v-qiaqi@microsoft.com on at

Like (0)

Report
Re: 3 combo-boxes affecting each others options

Hi @RobChilds,

Do you want to filter Episode and Operator based on ComboBox1 selected?

Could you please tell me that if all the ComboBox allow multi select or single select?

Well, you need to set the Items property of the ComboBox corresponding to Episode and Operator as below:

Filter(DataverseTable,Production =cmb_Production.Selected.Value).Episode//For cmb_Episode
Filter(DataverseTable,Production =cmb_Production.Selected.Value&&Episode =cmb_Episode.Selected.Episode).Operator

The above formula works only when you have ComboBox allowing single select.