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

 

 

 

 

View solution in original post

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.

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 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 (2,726)