cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ahockaday
Level: Powered On

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?

9 REPLIES 9
PowerApps Staff CarlosFigueira
PowerApps Staff

Re: How to search Office365Users and filter results by department?

There's no way to search in the connector directly by the department - see https://docs.microsoft.com/connectors/office365users/ for a list of all operations on that connector. What you can do is to search users by some other field (such as name, for example), then use a Filter expression to only show the users for the chosen department.

 

For example, if your department list is on a dropdown called ddDepartments, then you can use this expression to only show results from that department:

Filter(
    Office365Users.SearchUser({ searchTerm: SearchTerm.Text }),
    Department = ddDepartments.Selected.Value)

Notice that by default SearchUser only returns 100 results, so if it's possible, for example, that you have more than 100 John's in your company, but only 10 of them in the 'Sales' department, this may not return all of them. What you can do is to increase the number of users that is returned by SearchUser, for example, using the expression below:

Filter(
    Office365Users.SearchUser({ searchTerm: SearchTerm.Text, top: 500 }),
    Department = ddDepartments.Selected.Value)

And that will return the first 500 John's in your directory, which will then be filtered by their departments.

ahockaday
Level: Powered On

Re: How to search Office365Users and filter results by department?

Since I did not want to search by name, and just by Department only I set the SearchTerm.Text box to Default to "" and made that field not visible, but when I try to select the Department from the dropdown it is only retuning one record for "Human Resources" and that persons name began with the letter "A" and it returns none when I select "Marketing" or "Information Technology". All 3 of these departments have multipe people in them. When I try to add this expression Filter( Office365Users.SearchUser({searchTerm: SearchTerm.Text,Top:500}), Department = ddDepartments.Selected.Value) as you suggested - it gives me an error stating "No Parameter. This function has no optional parameter named 'Top'.". 

 

Am I adding the Top:500 to the wrong place in the expression?

PowerApps Staff CarlosFigueira
PowerApps Staff

Re: How to search Office365Users and filter results by department?

Can you try parameter "top" instead of "Top"? I believe it is case-sensitive.

ahockaday
Level: Powered On

Re: How to search Office365Users and filter results by department?

that did not make a difference. Still got the same error. See attached. 

Community Support Team
Community Support Team

Re: How to search Office365Users and filter results by department?

Hi @ahockaday,

Do you just want to filter your Gallery items based on the Department?

I agree with @CarlosFigueira's thought almost. Currently, the searchTerm property of Office365Users.SearchUser function could not be used to filter Department value. The searchTerm property could be applied to display name, given name, surname, mail, mail nickname and user principal name.

More details about Office365Users.SearchUser function, please check the following article:

https://docs.microsoft.com/en-us/connectors/office365users/#search-for-users

 

I have made a test on my side, and don't have the issue that you mentioned. The screenshot as below:3.JPG

Set the Items property of the Gallery control to following formula:

Filter(
Office365Users.SearchUser({searchTerm:"",top:500}),
Department=Dropdown1.Selected.Value
)

On your side, you could consider take a try with the following:

Filter(
    Office365Users.SearchUser({searchTerm:"",top:500}),
    Department=ddDepartments.Selected.Value
)

or

Filter(
    Office365Users.SearchUser(),
    Department=ddDepartments.Selected.Value
)

I think your issue is related to the Office365Users.SearchUser function, please take a try to re-create a new connection to Office 365 Users connector, then remove your formula within the Items property of the Gallery control, and type above formula I provided, check if the issue is solved.

 

Best regards,

Kris

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
ahockaday
Level: Powered On

Re: How to search Office365Users and filter results by department?

Adding a new connection to Office365Users did work as far as the error I was getting. Only problem now is there are so many users in our AD that even when I set Top to 999, some prople are still not getting pulled in. Do you happen to know if 999 is the maimum number you can use...whenever I add more than that it seesm to stop working? Also, is there a way to designate a specific AD container you want to use - right now it seesm to be pulling in everything in our AD - retired staff, conference rooms, etc. and I just want the active employes in a certain container?  

Babalaul
Level: Powered On

Re: How to search Office365Users and filter results by department?

Hello,

 

Any solution for this problem ? I face the same thing. 

Allthough i set it up to top:999, is returning maxim 20,30 users. 

Administrator
Administrator

Re: How to search Office365Users and filter results by department?

Please review and advise if the following video is helpful. It was uploaded after this thread was created. 

 

https://www.youtube.com/watch?v=QhGV4AhCFRc

 

@TopShelf-MSFT 

LucasYuen
Level: Power Up

Re: How to search Office365Users and filter results by department?

 


@ahockaday wrote:

Adding a new connection to Office365Users did work as far as the error I was getting. Only problem now is there are so many users in our AD that even when I set Top to 999, some prople are still not getting pulled in. Do you happen to know if 999 is the maimum number you can use...whenever I add more than that it seesm to stop working? Also, is there a way to designate a specific AD container you want to use - right now it seesm to be pulling in everything in our AD - retired staff, conference rooms, etc. and I just want the active employes in a certain container? 


 I have the same problem here. Any new suggestion?

Helpful resources

Announcements
firstImage

Microsoft Business Applications Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

firstImage

Demo Extravaganza Championship Voting Open

Voting Ends: October 30, 2019!

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

Top Kudoed Authors
Users Online
Currently online: 351 members 4,865 guests
Recent signins:
Please welcome our newest community members: