Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Simon E]

I am trying to return a list of All staff at a particular office.

The Gallery object has just a very basic:
Filter(Office365Users.SearchUser(), OfficeLocation = "Sydney")

But only 6 records are returned??

Strangely, if I search on “Melbourne” instead 17 records are returned.  I cannot figure out why the result sets are so limited, there are ~1-200 employees at these locations.

The same thing happens if using ‘City’ or other location based parameters.

Searching in the standard search screen by common names like John returns more than 6 rows in some cases.

I have seen examples online referencing {top:100} but this syntax doesn’t work at all for me.
I have changed the Data row limit in app settings, it doesn’t make any difference.

——————————
Simon E
Sydney, AU
——————————

[Chad Kealey]

There are limitations to the SearchUser function. Shane Young provides a detailed explanation and offers a workaround in this video.

——————————
Chad Kealey
——————————
——————————————-

[Rich Burdes]

?Hi,
So search user will search the first 100 records in your Active Directory – so probably awesome for Adelaide or Brisbane. But no good if you have more than 100 people in your tenant. Using the Top query will extend the result set to look for up the first 999 records.

If you have more that 999 people you will want to use the SearchTerm option as detailed here – Overview of the Office 365 Users connection – PowerApps however SearchTerm will only look at these AD columns (display name, given name, surname, mail, mail nickname, and user principal name) no location there to call.

I’m thinking that if you have Azure AD groups per office location you could use the AzureAD connector instead? ‘Get Group Members’ – so potentially if your groups had specific location names eg SydneyUsers – you could get that group, pull into a collection and present them?

Untested at my end – but hoping this helps..

——————————
Rich Burdes
Consultant
Christchurch
274550142
——————————
——————————————-

[Rich Burdes]

?Ran a quick test – Azure AD get group members support office location

So either Create a gallery or collection using the guid of a public everyone AD group or a specific AD group for that location

You’ll want syntax such as this for a general all company group

Filter(AzureAD.GetGroupMembers(“your group ID here”).value, officeLocation = “Sydney”)

or say you have a collection in place of all office AD groups per office  – something like the following could work

AzureAD.GetGroupMembers(“DropDown1.Selected.ID”).value

Hope this helps!

——————————
Rich Burdes
Consultant
Christchurch
274550142
——————————
——————————————-

[Will Page]

Rich is right, your search is only returning the first 100 results and then you’re filtering on those. You might find all your results are people whose names begin with the first few letters of the alphabet.

Assuming you have 999 users or less in your directory, the correct syntax for your gallery’s items property would be: 

Filter(Office365Users.SearchUser({searchTerm:"", top:999}), OfficeLocation = "Sydney")?

If you have more than 999 users then the method is a bit different, you have to set up an App Registration for the Graph API in Azure AD then use a Flow or Logic app to hit the Graph using HTTP:
Hit the 3 dots and go Settings and set the pagination. You can get up 100000 pages of results. By default this means you can have up to 10 million results, but if you’re within a million miles of that then this probably isn’t the solution for your problem!

Lastly, parse the JSON that comes back and save the results into a delegable data source such as SP online or SQL.

You can run your flow/logic app on a schedule. Clear out the existing table on each run and repopulate it using get-items, then a for-each to delete them, and another for-each to create the new items. Again, use the pagination setting of get-items to increase the results returned.

You can now use this SP or SQL data source in your app and run delegable queries on it. It won’t be right up to date, and if that’s a problem for you, there’s a solution for that too…

——————————
Will Page
Technical consultant
+64210436716
——————————
——————————————-

[Simon E]

Thanks for your insight, I’m investigating the Azure AD angle.

I have seen this {top:999} syntax a lot while researching but I’m very confused why it won’t work for me. See attached???

——————————
Simon E
Sydney, AU
——————————
——————————————-

[Will Page]

You need the searchTerm parameter with it. e.g:

Filter(Office365Users.SearchUser({searchTerm:"", top:999}), OfficeLocation = "Sydney")??

A blank search term returns all items in the directory up to your top value.

——————————
Will Page
Technical consultant
+64210436716
——————————
——————————————-

[Simon E]

I tried that. I just can’t get top to work, at all.

——————————
Simon E
Sydney, AU
——————————
——————————————-

[Will Page]

That’s really odd eh. Works fine for me. I tried this just now with a text label:

Sorry I can’t offer more help on that one.

——————————
Will Page
Technical consultant
+64210436716
——————————
——————————————-

[Eric Sauve]

Quite a head banger!

I’ve tried copy / paste the Filter formula as Will posted and I was getting Errors at the end of the formula.. the intellisense was working fine and recognizing all the parameters correctly but somehow no way to remove the unexpected error.

The only way I’ve been able to make it work was to restart the formula at the SearchUsers part and use the automatic fill for each items in the formula then BOOM it works.. odd!!

——————————
Eric Sauve
aka PowerDiver
XRM Vision
Montreal QC
——————————
——————————————-

[Simon E]

Resolved the issue with using the top:999 parameter by adding a second Office 365 Users Data Connector and removing the first one. For some reason, it works fine now.

——————————
Simon E
——————————
——————————————-

[N Meier]

This was just the answer I was looking for, it helped a lot. Thank you, Rich!

——————————
N Meier
SharePoint Administrator
Australia
——————————
——————————————-