cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
forest2002
Helper I
Helper I

Removing an option from a dropdown based on other dropdowns

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

1 ACCEPTED SOLUTION

Accepted Solutions
Alex_10
Memorable Member
Memorable Member

@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"] ) 
)

 

View solution in original post

8 REPLIES 8
Alex_10
Memorable Member
Memorable Member

@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))

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
Memorable Member
Memorable Member

@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"]) )
)

 

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
Memorable Member
Memorable Member

@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
Community Support
Community Support

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));
)

 

AAA.gif

 

Best Regards,

Wearsky

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

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

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Carousel_PP_768x460_Wave2 (1).png

2022 Release Wave 2 Plan

Power Platform release plan for the 2022 release wave 2 describes all new features releasing from October 2022 through March 2023.

365 EduCon 768x460.png

Microsoft 365 EduCon

Join us for two optional days of workshops and a 3-day conference, you can choose from over 130 sessions in multiple tracks and 25 workshops.

Top Solution Authors
Users online (5,146)