PowerApps Exchange

Please login or click SIGN UP FOR FREE to create your PowerAppsUG account to join this user group.
Expand all | Collapse all

Office365Users.SearchUser only returning limited results

Jump to Best Answer
  • 1.  Office365Users.SearchUser only returning limited results

    Posted May 14, 2019 10:51 PM
      |   view attached
    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
    ------------------------------


  • 2.  RE: Office365Users.SearchUser only returning limited results

    Bronze Contributor
    Posted May 15, 2019 09:53 AM
    There are limitations to the SearchUser function. Shane Young provides a detailed explanation and offers a workaround in this video.

    ------------------------------
    Chad Kealey
    ------------------------------



  • 3.  RE: Office365Users.SearchUser only returning limited results

    Silver Contributor
    Posted May 15, 2019 08:14 PM
    ​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
    ------------------------------



  • 4.  RE: Office365Users.SearchUser only returning limited results

    Silver Contributor
    Posted May 15, 2019 09:22 PM
    ​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
    ------------------------------



  • 5.  RE: Office365Users.SearchUser only returning limited results

    Bronze Contributor
    Posted May 15, 2019 09:31 PM
    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
    ------------------------------



  • 6.  RE: Office365Users.SearchUser only returning limited results

    Posted May 15, 2019 10:40 PM
    Edited by Simon E May 15, 2019 10:40 PM
      |   view attached
    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
    ------------------------------



  • 7.  RE: Office365Users.SearchUser only returning limited results

    Bronze Contributor
    Posted May 15, 2019 10:49 PM
    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
    ------------------------------



  • 8.  RE: Office365Users.SearchUser only returning limited results

    Posted May 15, 2019 10:54 PM
    I tried that. I just can't get top to work, at all.

    doesn't work

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



  • 9.  RE: Office365Users.SearchUser only returning limited results

    Bronze Contributor
    Posted May 15, 2019 11:40 PM
    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
    ------------------------------



  • 10.  RE: Office365Users.SearchUser only returning limited results

    Silver Contributor
    Posted May 17, 2019 04:35 AM
    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
    ------------------------------



  • 11.  RE: Office365Users.SearchUser only returning limited results
    Best Answer

    Posted May 21, 2019 07:41 PM
    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
    ------------------------------