How to search Office365Users and filter results by department?

[ahockaday]

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?

[CarlosFigueira]

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]

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?

[CarlosFigueira]

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

[ahockaday]

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

[v-xida-msft]

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:

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

[ahockaday]

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?  

[Anonymous]

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. 

[Anonymous]

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

@Anonymous 

[LucasYuen]

---

@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?