Implement JOIN in AD filter command in Power Apps

[KrishnaV]

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.