Search code examples
powerapps

Get a list of distinct managers from all users that is sorted

I'm trying to get a distinct list of all the managers from all the users in a Power Apps combo box that is sorted.

I currently have the below code that is producing a list of unsorted mangers and it's very slow. It can take several minutes to get the list.

EditScreen OnVisible  = ClearCollect(varUsers, Filter(Office365Users.SearchUser({top:500}), StartsWith(CompanyName,"Company"),!StartsWith(JobTitle,"CEO"),!StartsWith(JobTitle,"Chairman")));

Combobox Items = Sort( ForAll(varUsers, Office365Users.ManagerV2(Mail).displayName),DataSourceInfo.DisplayName,SortOrder.Ascending)

If I add distinct to the Items it will only display "displayname".

Distinct( Sort( ForAll(varUsers, Office365Users.ManagerV2(Mail).displayName),DataSourceInfo.DisplayName,SortOrder.Ascending),DataSourceInfo.DisplayName)

Edited to add code formatting.

Solution

  • DataSourceInfo.DisplayName is a value from the DataSourceInfo enumeration which has an internal value of "displayname" - your code is looking at all records from the result of ForAll, and getting the distinct values of "displayname".

    The result of the ForAll call to get the managers is a table with a single column called 'Value', so you should use that column (and not DataSourceInfo.DisplayName) to get the distinct values (and also sort based on that):

    Sort(
  Distinct(
    ForAll(varUsers, Office365Users.ManagerV2(Mail).displayName),
    Value
  ),
  Value,
  SortOrder.Ascending)