cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ahockaday
Advocate I
Advocate 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?

15 REPLIES 15

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.

SytzeATCTU
Frequent Visitor

Its 2022 and still no solution. I have also tried everything I can think of. It's is highly frustrating. Wonder if MS is not considering the misuse (or perhaps performance?) of a function where you can just dump the entire user complement from AAD. Nevertheless I need this for an HR solution. Irritating. 

 

I promise to post something if I find a solution or get the wind of an improvement function. 

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Difinity Conference 2022

Difinity Conference 2022

Register today for two amazing days of learning, featuring intensive learning sessions across multiple tracks, led by engaging and dynamic experts.

European SharePoint Conference

European SharePoint Conference

The European SharePoint Conference returns live and in-person November 28-December 1 with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Power Apps Ideas

Changes to Ideas Coming

We are excited to announce a new way to share your ideas for Power Apps!

Top Solution Authors
Top Kudoed Authors
Users online (3,960)