cancel
Showing results for 
Search instead for 
Did you mean: 

Implement JOIN in AD filter command in Power Apps

In this article, I want to discuss one of the repeating issues in the community, how to implement a JOIN while retrieving the manager details in a loop in Power Apps.

 

Business case:

User wants to implement a gallery with all the details of the user with their manager name as part of the results set.

 

Technical Challenge:
To get the manager name of the user we will need to perform the below query:

Office365Users.ManagerV2(User().Email).mail
But if we need to the manager email in a loop will be an expensive call as the app need to perform that many AD calls.
 
Solution:
To avoid this iterative calls in a loop we can perform the JOIN the filter query as below:
ClearCollect(
        collUserProfile,
        AddColumns(
            Office365Users.SearchUser({txtEmail.Text}),
            "ManagerEmail",
            If(
                IsError(Office365Users.ManagerV2(Id).mail),
                Blank(),
                Office365Users.ManagerV2(Id).mail
            )
        )
    );
 In the above query txtEmail.Text will hold the text provided by the user, so if the user provides the full email it will give only one recordset into the collection, if the user-provided partial text of first name or last name the result will be more than one record.
 
With the above JOIN query, the result with 150 records will be fetched in 3 seconds against 100K records AD DB.
 
Happy development in Power Apps.
Meet Our Blog Authors
  • PowerApps Community Mentor 2018, Practicing surgeon and former Professor of Surgery, University of Illinois, Chicago.
  • I am the Owner/Principal Architect at Don't Pa..Panic Consulting. I've been working in the information technology industry for over 30 years, and have played key roles in several enterprise SharePoint architectural design review, Intranet deployment, application development, and migration projects. I've been a Microsoft Most Valuable Professional (MVP) 12 consecutive years and am also a Microsoft Certified SharePoint Masters (MCSM) since 2013.
  • Systems developer
  • My name is Timothy Shaw and I create digital solutions using the Power Platform, Office 365, and Azure SQL and handle the IT for a small company in the energy sector. When not building the future :), I enjoy playing guitar, good (or really bad!) sci-fi, Xbox therapy, and hanging with my wife and son. Twitter: @ShortForTim