cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Johnafin
Frequent Visitor

Filter out already present values in Excel column from Dropdown items

Hello,

 

I have been driving myself crazy on how to accomplish a drop down filter.

Basically, I want to have a Dropdown that looks at an Excel column for a value already present, then remove that selection from the dropdown.

 

An example would be that I am changing the priority on a record that is a 1-10 priority scale and want to avoid being able to have the same priority number on multiple records. I don't want to have three records with a 4 priority.

 

I tried to to a depends on rule, but that only filters the dropdown items to the current priority items.

 

Thanks in advance!

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
rubin_boer
Super User
Super User

hi @Johnafin lets try the "in" operator

 

1. you have a scale from 1-10 

2. assign this value to a control

3. have all the value available in control excepts those already assigned

 

I am going to use galleries to solve this

GalleryA: priorities available to assign

GalleryB: priorities assigned

 

1. Sequence(10,1) give me to values from 1

2 & 3 Filter(Sequence(10,1), Not( Value in colAssignPriority))

3. Collect(colAssignPriority,ThisItem.Value) and set the Item of GalleryB to colAssignedPriority

rubin_boer_0-1621529847714.png

 

Click on the priorities on the left and as the move to the new collection they are removed from your list

rubin_boer_1-1621529971318.png

 

hope this helps

 

 

 

 

View solution in original post

3 REPLIES 3
rubin_boer
Super User
Super User

hi @Johnafin lets try the "in" operator

 

1. you have a scale from 1-10 

2. assign this value to a control

3. have all the value available in control excepts those already assigned

 

I am going to use galleries to solve this

GalleryA: priorities available to assign

GalleryB: priorities assigned

 

1. Sequence(10,1) give me to values from 1

2 & 3 Filter(Sequence(10,1), Not( Value in colAssignPriority))

3. Collect(colAssignPriority,ThisItem.Value) and set the Item of GalleryB to colAssignedPriority

rubin_boer_0-1621529847714.png

 

Click on the priorities on the left and as the move to the new collection they are removed from your list

rubin_boer_1-1621529971318.png

 

hope this helps

 

 

 

 

Is there a way to apply this gallery solution to a dropdown? It works with the galleries, but I just don't want them to have the option to double up on the same number in the drop down selection.

If you have the needed options available in a gallery, just set the dropdown value to the items in the gallery. Though the code what he has provided you will work with your excel table I believe as well, and was just shown in a gallery for visual reference.

_____________________________________________________________________________________
Like my answer? - Hit that Thumbs Up. Resolved the Issue? - Hit Accept as Solution.
This helps others find solutions to future issues!

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

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