cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ahockaday
Helper I
Helper I

How to search Office365Users and filter results by department?

I have a staff Directory and I am trying to filter results by the employees department (preferably using a dropdown menu) so if you select Marketing in the dropdown, the Gallery only displays people in the Marketing Department, if you select Human Resources, it returns people in the that department, etc. 

 

So far I have it working with a text box and a Gallery with the following in Items - Filter(Office365Users.SearchUser({searchTerm: SearchTerm.Text}),AccountEnabled=true). When I type in a name it returns people with that name only. I cannot figure how to have it search and return results based on Department instead of Name. 

 

Anyone have any ideas on how I can accomplish this?

14 REPLIES 14

Based on my research and Tial & errors i guess the Office365Users.SearchUser ist not reliable.

When i filter in a Tenant with at least more than 1000 Users, then the Department-Filter not working.

I use a Combobox on SharePoint People Picker configured as

cmbAssignedTo.Items:

 

Filter(Office365Users.SearchUser({searchTerm:cmbAssignedTo.SearchText,top:999}), IsBlank(Department)=false && AccountEnabled=true && Department=cmbDepartments.Selected.Label)

 

 

When i remove the Department-Filter on the dependent ComboBox, then the Preview retrieves all enabled Account-Users with Department-Value.

cmbAssignedTo.Items:

 

Filter(Office365Users.SearchUser({searchTerm:cmbAssignedTo.SearchText,top:999}), IsBlank(Department)=false && AccountEnabled=true)

 

The Preview in PowerApps is working without dependent Department-Filter, but after Publishing to SharePoint, the ComboBox does not retrieve any users.

 

There are Limitations to Filter in SearchUsers, which cannot limited with the top-Operator?

This is annoying  

You must prefetch the dependent Department-Label in a Text-Label named lblDeparment with the Text-Formula

lblDepartment.Text:

cmbDepartments.Selected.Label

then refer to this lblDepartment.Text in your Filter-Formula:

cmbAssignedTo.Items:

Filter(Office365Users.SearchUser({searchTerm:cmbAssignedTo.SearchText,top:999}), IsBlank(Department)=false && AccountEnabled=true && Department=lblDepartment.Text)

This is working for me in a Tenant with many thousands of users... 

Take care with this Department-Filter. In this Casse the Office365Users.SearchUser() Connector is not reliable in Tenants with more then 999 Users, because the filter on JobTitle, Department or Location is established after the top:999 User Limit. No Pagination in Batches possible with this Connector. So the filters on JobTitle, Department or Location are not delegable. Suboptimal Connector Design.... 

Ptiwari
Helper I
Helper I

Any solution you find for this problem? My requirement is same like yours.

Thank you, Pooja

No, unfortunately I did not find a solution for this.

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

secondImage

Power Apps Community Call

Please join us on Wednesday, October 20th, at 8a PDT. Come and learn from our amazing speakers!

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.

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