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
Super User
Super User

@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
Super User
Super User

@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
Super User
Super User

@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
Super User
Super User

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

Power Platform Call June 2022 768x460.png

Power Platform Community Call

Join us for the next call on June 15, 2022 at 8am PDT.

PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

Top Solution Authors
Top Kudoed Authors
Users online (1,734)