5 Drop-downs with same Source, filter each selection from each drop-down dynamically

[Phillip-JCI]

Hi guys, been trying to tackle this one, but no luck. I did get somewhere but my workaround did not work.

Basically I have 5 Dropdowns. They all contain the same list of Vendors. However as you select a vendor, I want the other dropdowns not to display that vendor anymore as a choice. I did some crazy stuff, with variables and collections but basically ended up with a final collection that has only the "not selected" vendors. I am updating this in realtime using a timer.

The problem I have is I am hitting a loop. If I set the dropdown Items property with that collection that only shows the available Vendors. As soon as I click the value, the dropdown resets itself. I am assuming it is because as soon as the value is selected, it tells that Collection I am no longer available, so it then is no longer part of that collection and is no longer a valid choice, and not an Item. I tried trigger this OnChange instead of timer, but no luck, and brain fried, so maybe I am overlooking something.

I am open to other approaches, etc. But basically I just need all 5 dropdowns to be unique. They cannot be the same value. Once a Vendor is selected it can only be selected once. I can make rules, to auto reset, the fields if I can't solve this, but ideally they wouldn't even see the value as a choice in their dropdown. Basically the dropdowns are dependent on each other.

Hopefully someone can help! I will have several thousands of vendors, so we have to be mindful of delegation.

Thanks!

[RandyHayes]

@Phillip-JCI 

Yikes!  The problem will be that you will run into all kinds of circular references with this (I bet you have in your testing).

I have another option which would be to use a more visual feedback to the user that they have a duplicate and not allow them to proceed until fixed.  They would still see all the vendors in all the dropdowns, but if they should choose a duplicate, they would have a visual cue that there is a problem to fix.  

It's a simple solution, but I know you had said the lists "ideally" should not have the other vendor in them, but that's going to be a challenge to nail down properly.

So, I didn't bother in this reply to post it as it may not be valid for you, but if so, I'd be happy to share.

[v-yuxima-msft]

Hi @Phillip-JCI 

Do you want the 5 dropdowns filter each selection dynamically and if the one is selected in one dropdown,it will not show in the other 4 dropdown items?

You can test with the following workaround.

Data Source:

App Test:

Add one button and rename it as Button_Reset,

Button_Reset.OnSelect=UpdateContext({List1:Choices('2019test'.ChoicesTest)});UpdateContext({List2:Choices('2019test'.ChoicesTest)});UpdateContext({List3:Choices('2019test'.ChoicesTest)});UpdateContext({List4:Choices('2019test'.ChoicesTest)});UpdateContext({List5:Choices('2019test'.ChoicesTest)})

Add 5 dropdown controls and Rename them as DropDown1,DropDown2,DropDown3,DropDown4,DropDown5.

DropDown1.Items=List1

DropDown1.OnSelect=UpdateContext({List1:Filter(Choices('2019test'.ChoicesTest),!(Value in Dropdown5.Selected.Value) && !(Value in Dropdown2.Selected.Value)&&!(Value in Dropdown3.Selected.Value)&&!(Value in Dropdown4.Selected.Value))})

DropDown2.Items=List2

DropDown1.OnSelect=UpdateContext({List2:Filter(Choices('2019test'.ChoicesTest),!(Value in Dropdown5.Selected.Value) && !(Value in Dropdown1.Selected.Value)&&!(Value in Dropdown3.Selected.Value)&&!(Value in Dropdown4.Selected.Value))})

DropDown1.Items=List3

DropDown1.OnSelect=UpdateContext({List3:Filter(Choices('2019test'.ChoicesTest),!(Value in Dropdown5.Selected.Value) && !(Value in Dropdown2.Selected.Value)&&!(Value in Dropdown1.Selected.Value)&&!(Value in Dropdown4.Selected.Value))})

DropDown1.Items=List4

DropDown1.OnSelect=UpdateContext({List4:Filter(Choices('2019test'.ChoicesTest),!(Value in Dropdown5.Selected.Value) && !(Value in Dropdown2.Selected.Value)&&!(Value in Dropdown3.Selected.Value)&&!(Value in Dropdown1.Selected.Value))})

DropDown1.Items=List5

DropDown1.OnSelect=UpdateContext({List5:Filter(Choices('2019test'.ChoicesTest),!(Value in Dropdown1.Selected.Value) && !(Value in Dropdown2.Selected.Value)&&!(Value in Dropdown3.Selected.Value)&&!(Value in Dropdown4.Selected.Value))})

Check:

Hope this could be helpful.

Best Regards.

Yumia

[Phillip-JCI]

Hi Randy, that is exactly what I ran into. The quick and dirty solution for now, has been to force the dropdowns to be selected sequentionally. Meaning it set a value, then Dropdown 2, uses an Item list derived from the result of the first dropdown. I am using seperate collections. The issue arises if someone changes them out of order. However What I put is a second logic, the if any of the two dropdown values match. It will reset all rows prior to the first dropdown that had that value. It's not ideal, but works in most situations.

I will try to see if Yumia's responce below works in my use case, and will report back later.

Thanks!

[Phillip-JCI]

Hi Yumia, this is very close to what I want to do, so thank you so much, very appreciative, but looking at the setup, I have a few challanges.

My Vendor list, is thousands of records,let's just say for now its 3000 vendors, so we are above the 2000 limit in delegation in case that comes into play. Can I just paste all of those as choices into the list?

The second issue, My Vendors are supposed to be filtered by two more other dropdowns. I was planning on creating a cascading dropdown first, into another cascading dropdown, now I have my available vendors. Let's call them State, and County for this example. Then apply the list logic that behaves in the way we discussed. Ideally maybe even make it into a combobox as a bonus if thats possible.

That would be the dream scenario.

So as a recap:

First Select State Dropdown, then that cascades into County Dropdown which then cascades into Dropdown1 - 5 choices. As each is selected, the other will not be able to select the same vlaue. There will be 5 Dropdowns total. They all must have unique values, generated from the result of State/County as the baselist.

Hopefully this is clear.

Thanks!

Ps. As just some mental math solutioning; Since I was checking seems SharePoint Choices column is limited to 255 choices. We could possibly use multiple sharepoint datasources. I.E Cascading Dropdown from 1 to 2, the resulting list of vendors (under 255), then is patched into another sharepoint list as choice column datasource, or similar. This is working under the assumption, the corresponding total vendors will be less than 255. Also multiple people can be using the app at the same time, so this new choices list would have to be unique to each session. Anyhow food for thought, hopefully you have a better idea Yumia! Haha!.

Thanks again!

[RandyHayes]

@Phillip-JCI 

So what I was mentioning was the potential to provide user feedback of the issue and prevent further action until corrected.  This by far seemed the easiest as there is very little formula magic involved.

I've attached a sample app the demonstrates this clearer than I can explain it.  But, basically, it uses a count of the grouped values of the 5 dropdowns to determine if there are any with more than 1...if so, it displays the appropriate error and does not allow further action (the "Next" button)

Area of most importance - the Items property of the hidden Gallery.  It determines duplicates and then all the other controls feed off of that.  

Take a look and see if that is something that might work for you.

[Phillip-JCI]

Its definitely a great idea Randy, I thought of something similar as a backup plan. I did Yumia's suggestion, and it works, however the challange is populating the sharepoint list, with the choices values. Which mine are dependent on State/County. 

Once I figure that portion out, I will update accordingly to help others.

Thanks for your help as well.