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

How to populate drop down with distinct values in excel Column A based upon value in Column B

 

Hello 

 

I have a table called skill_scores_table with several columns. It is iin Excel in OneDrive.

Two of the columns are FULLNAME and MANAGER. FULLNAME is used to capture the name of members of staff MANAGER is for their manager's name. So each manager will have several staff. Here's a simple example

FULLNAME | MANAGER

John | Manager A

Mary | Manager B

David | Manager A

Steve | Manager B

Joanne | Manager A

Tim | Manager A

 

I want to populate a drop down with the staff that are applicable to the manager who is currently logged in. I can get thier name using the User() function. So if the current user was Manager A , I want the drop down to only show John, David, Joanne and Tim.

 

Whats the best way to achieve this?

 

I've tried a few variations of Filter() and Distinct() but it doesn't work. I tried this in the Items property of the Drop Down:

 

Filter(skill_scores_table .FULLNAME, MANAGER=User().Fullname) - nothing returned

Distinct(skill_scores_table , FULLNAME) - nothing returned with a drop down but tried this with a listbox and it did populatge so what's different about a drop down?

 

I also thought I could create a new collection containing the FULLNAME of the filtered table MANAGER column but I can't figure out how to do that either.

 

I just need a nudge in the right direction so hopefully somebody might be able to help me out?

Thanks

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
RandyHayes
Super User
Super User

@winwell 

Try this for a nudge...Set your Items property on your dropdown to this:

   Filter(skill_scores_table, Manager=User().FullName)

 

Make sure your DropDown has its Value to display set to "FULLNAME".

 

Also, to be nice and sort...

  SortByColumns(Filter(skill_scores_table, MANAGER=User().FullName), "FULLNAME", Ascending)

 

Hope this is the nudge you're looking for.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

2 REPLIES 2
RandyHayes
Super User
Super User

@winwell 

Try this for a nudge...Set your Items property on your dropdown to this:

   Filter(skill_scores_table, Manager=User().FullName)

 

Make sure your DropDown has its Value to display set to "FULLNAME".

 

Also, to be nice and sort...

  SortByColumns(Filter(skill_scores_table, MANAGER=User().FullName), "FULLNAME", Ascending)

 

Hope this is the nudge you're looking for.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

Thanks very much - that **bleep** Value field!! Forgot all about it!!

 

I used this 

 

Distinct(Filter(skill_scores_table,MANAGER=txtManagerName.Text),FULLNAME)

 

which correctly displays the staff names as expected.  (There is another PERIOD column so there could be multiple same names for one manager so I needed it to be distinct.

 

I then added your suggestion like this:

 

Distinct(SortByColumns(Filter(skill_scores_table,MANAGER=txtManagerName.Text),"FULLNAME",Ascending),FULLNAME)

 

which now shows the distinct staff names sorted in ascending order.

 

Thank you for the nudge - it was jsut what I needed 🙂

 

,

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

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

Users online (2,252)