Removing an option from a dropdown based on other dropdowns

[forest2002]

Hi guys,

I'm struggling to get my head around this, as per the image I have three dropdowns, dropdrown1, dropdown2 & dropdown3 in a Gallery and they each have the same set of selections 'Desk 1', 'Desk' and 'Desk3 feeding into them as single line of text field from sharepoint list which go into a collection when the app starts. 'Onchange' they update the collection and the save button patchs back the updates to Sharepoint. 

What I'd like to do is say disable or ideally hide 'Desk 1' from a dropdown for person 1 if it's already been chosen by person 2.

I've been trying to do it with Filters but not getting anywhere, any help greatly appreciated

[Alex_10]

@forest2002 

Dropdown1.Items = 

Filter(DropDownSample, !(Value in Dropdown2.Selected.Value) && !(Value in Dropdown3.Selected.Value))

Dropdown2.Items = 

Filter(DropDownSample, !(Value in Dropdown1.Selected.Value) && !(Value in Dropdown3.Selected.Value))

Dropdown3.Items = 

Filter(DropDownSample, !(Value in Dropdown1.Selected.Value) && !(Value in Dropdown2.Selected.Value))

[forest2002]

Thanks that's really useful and works really well, however what I failed to mention as I was simplifying it to explain here is there are a couple of other options in dropdown ie. "Home" that I don't want as part of the filter. Is there any way I can have these in the dropdown but outside the filter??

[Alex_10]

@forest2002 

you need to add to formula for each drop down the code like:

Filter(DropDownSample, 
    !( Value in Dropdown2.Selected.Value && !(Value in ["Home", "Home2"]) ) && 
    !( Value in Dropdown3.Selected.Value && !(Value in ["Home", "Home2"]) )
)

[forest2002]

That's working perfectly on dropdown1 but when I add to dropdown 2 it 'creates a circular reference between properties, which is not allowed'. Any way around this? Thanks

[Alex_10 · Accepted solution]

@forest2002 

if each dropdown.OnCHange patches to the collection, and that collection is the data source for the gallery:

Dropdown1.Items = 
Filter( DropDownSample, 
    ( Value <> ThisItem.dropdown2Column || Value in ["Home", "Home1"] ) &&
    ( Value <> ThisItem.dropdown3Column || Value in ["Home", "Home1"] ) 
)

Dropdown2.Items = 
Filter( DropDownSample, 
    ( Value <> ThisItem.dropdown1Column || Value in ["Home", "Home1"] ) &&
    ( Value <> ThisItem.dropdown3Column || Value in ["Home", "Home1"] ) 
)

Dropdown3.Items = 
Filter( DropDownSample, 
    ( Value <> ThisItem.dropdown2Column || Value in ["Home", "Home1"] ) &&
    ( Value <> ThisItem.dropdown1Column || Value in ["Home", "Home1"] ) 
)

[v-xiaochen-msft]

Hi @forest2002 ,

I did a demo for you.

This example only applies to each drop-down control is selected only once.

In other words, if you have selected 3 values for 3 dropdowns, and if you want to modify the value of a dropdown, the logic will go wrong.

Add a button control and set its Onselect property to:

ClearCollect(Col1,"Desk1","Desk2","Desk3","Home");
ClearCollect(Col2,"Desk1","Desk2","Desk3","Home");
ClearCollect(Col3,"Desk1","Desk2","Desk3","Home");
Reset(Dropdown1);
Reset(Dropdown2);
Reset(Dropdown3);
Set(var1,0);
Set(var2,0)

Add dropdown1 and set its Items property to:

Col1

Set its Onchange property to:

If(var1=0, 
    If(Dropdown1.Selected.Value<>"Home",RemoveIf(Col2,Value=Dropdown1.Selected.Value));
    If(Dropdown1.Selected.Value<>"Home",RemoveIf(Col3,Value=Dropdown1.Selected.Value));
    Set(var1,var1+1);
    Set(var2,1);,
   var1=1 && var2=2,
    If(Dropdown1.Selected.Value<>"Home",RemoveIf(Col3,Value=Dropdown1.Selected.Value));,
   var1=1 && var2=3,    
    If(Dropdown1.Selected.Value<>"Home",RemoveIf(Col2,Value=Dropdown1.Selected.Value));
)

Add dropdown2 and set its Items property to:

Col2

Set its Onchange property to:

If(var1=0, 
    If(Dropdown2.Selected.Value<>"Home",RemoveIf(Col1,Value=Dropdown2.Selected.Value));
    If(Dropdown2.Selected.Value<>"Home",RemoveIf(Col3,Value=Dropdown2.Selected.Value));
    Set(var1,var1+1);
    Set(var2,2);,
   var1=1 && var2=1,
    If(Dropdown2.Selected.Value<>"Home",RemoveIf(Col3,Value=Dropdown2.Selected.Value));,
   var1=1 && var2=3,    
    If(Dropdown2.Selected.Value<>"Home",RemoveIf(Col1,Value=Dropdown2.Selected.Value));
 )

Add dropdown3 and set its Items property to:

Col3

Set its Onchange property to:

If(var1=0,
    If(Dropdown3.Selected.Value<>"Home",RemoveIf(Col1,Value=Dropdown3.Selected.Value));
    If(Dropdown3.Selected.Value<>"Home",RemoveIf(Col2,Value=Dropdown3.Selected.Value));
    Set(var1,var1+1);
    Set(var2,3);,
   var1=1 && var2=1,
    If(Dropdown3.Selected.Value<>"Home",RemoveIf(Col2,Value=Dropdown3.Selected.Value));,
   var1=1 && var2=2,    
    If(Dropdown3.Selected.Value<>"Home",RemoveIf(Col1,Value=Dropdown3.Selected.Value));
)

Best Regards,

Wearsky

[forest2002]

Perfect mate, thank you so much!! Amazing the help that you get in here

[forest2002]

Thank you for helping, I haven't tried it as Alex's answer above worked exactly as I wanted but I'm sure this will help someone else further down the line