Office365Users.DirectReportsV2 Filtering

(1) Share

Report

Posted on by DCHammer

714 Super User 2025 Season 1

I have three dropdowns on the a form.

Current Director, Current Manager, Current Prime.

First dropdown is populated with a collection named listDirectors. The contents are populated within the OnVisible property of the screen where they're needed.

Second dropdown is populated using a clause I'll share below that needs modifying.

Third dropdown is populated using exactly the same clause as the 2nd but references dropdown 2.

As is, it works. But because some of the people returned in the second dropdown do not have direct reports, dropdown 3 errors. Which is just a symptom really.

The problem is that we need to filter the second dropdown to only return users with direct reports themselves.

The current clause populating the 2nd dropdown is below. How do I filter this list to only include users with direct reports?

Ungroup(
    ForAll(
        Office365Users.DirectReportsV2(drpProposedDirector.Selected.Email).value,
        Table(
            [
                {
                    Claims: "i:0#.f|membership|" & mail,
                    Department: "",
                    DisplayName: displayName,
                    Email: mail,
                    JobTitle: "",
                    Picture: ""
                }
            ]
        )
    ),
    Value
)

Categories:

General Questions

Connecting to Data

All responses (3)

Answers (1)

DCHammer 714 Super User 2025 Season 1 on at

Like (0)

Report
Office365Users.DirectReportsV2 Filtering

If you still see replies to this FLMike, I could still use some help.

I've modified my code to build a collection when the Director is chosen in the first dropdown.

ClearCollect( CurrentDirectorSubCollection, ForAll( Office365Users.DirectReportsV2(Self.Selected.Email).value, { Claims: "i:0#.f|membership|" & mail, Department: "", DisplayName: displayName, Email: mail, JobTitle: jobTitle, Picture: "" } ) );

But I cannot figure out how my own how to now figure out which of the people in this collection have Direct reports.

I understand in theory that I need to loop through the CurrentDirectorSubCollection to see if each row results in a non-zero return when Office365Users.DirectReportsV2 is passed the CurrentDirectorSubCollection.Email but can't write the syntax.
DCHammer 714 Super User 2025 Season 1 on at

Like (0)

Report

Office365Users.DirectReportsV2 Filtering

Thanks @FLMike. I wish there was a way to reply directly. I realized on the way to the office that just what you said is the likely solution. But even after I built the collection, I didn't know what to filter on. But CountRows make good sense. Now to figure out the syntax.

Thanks
Verified answer

Michael E. Gernaey 40,375 Super User 2025 Season 1 on at

Like (0)

Report

Office365Users.DirectReportsV2 Filtering

Hi,

Essentially you need to get a count of the .value.mail (at the end of yours) and if that has 0 do not include them.

So you would probably need to do a ClearCollect first, to populate a collection with their directs.

If the countrows = 0 do nothing, if it does

then change your code above to reference the Collection and not the query, that way you aren't querying twice.