Filter direct reports on search combobox

[Timm1]

Hello!

I have an org chart that I have built and have a dropdown to search for Office365 users. I only want users that have direct reports to show up. As of now with the wonderful answers from Rusk, I use the formula

Filter(Office365Users.SearchUser({searchTerm:cmbName.SearchText,top:600}),Len(Department) >0 && Not(StartsWith(DisplayName, "_")), Not(StartsWith(DisplayName, "811")), Not(StartsWith(DisplayName, "on-premises")), Not(StartsWith(Department, "convention center")), Not(StartsWith(Department, "Museum")),Not(StartsWith(DisplayName, "Apple")),Not(StartsWith(DisplayName, "beyondtrust")),Not(StartsWith(DisplayName, "ccpa")),Not(StartsWith(DisplayName, "Collect")))

I have the Not(StartsWith(…..etc  to remove unwanted users but I have over 40 display or departments and don’t want to use the NotStartWith(.. for each one.  I can eliminate most of them if I can filter searching on users that have direct reports.  I have tried the !IsBlank(Office365Users.DirectReports(…. But with no success.  Rusk has been very helpful with trying to help me out with this.  If I can’t filter with directreports, is there a way to have the Not(Startswith( formula and type in a list of each name and not have the Not(StartsWith( in front of all 40 entries.

Hope this all makes sense.

Thank you for your time.

Thanks,

Tim

[Drrickryp]

@Timm1 

How many options are there that it can start with?  You have specified 40 that it doesn't start with.

[Timm1]

Sorry - maybe I didn't explain my question correctly.  My formula contains Not(StartsWith(DisplayName, "811")), Not(StartsWith(DisplayName, "on-premises")), Not(StartsWith(Department, "convention center")).... etc.  I will have over 40 of these to put in to not show in the results.  If I can have a Not(StartsWith(DisplayName, "811", or on-premisis, or convention center.. or....etc.  Not have the Not(StartsWith(DisplaName in front of each item.  Or, if I can have the formula added to filter on only users who have Direct Reports, it would eliminate most of the entries I would need to add.  My current formula to the search combo box is Filter(Office365Users.SearchUser({searchTerm:cmbName.SearchText,top:600}),Len(Department) >0

Hope this helps. 

Thank you!

[JD382]

If it's feasible to make a collection, you can ClearCollect a table of results using your existing direct reports > 0 filter.

ClearCollect(

    collFilteredUsers,

    Filter(Office365Users.SearchUser({searchTerm:cmbName.SearchText,top:600}),Len(Department) >0

)

You then need a table of terms you want to use to remove names. One means of doing this:

Set(

    tableRemoveNames,

    ["Name1", "Name2", .....]

)

Then iterate over all of your removal criteria and perform removeif on the user collection.

ForAll(

    tableRemoveNames,

    RemoveIf(

        collFilteredUsers,

        StartsWith(

            Result,

            Value

        )

    )

)

This could be placed in your dropdown's OnSelect property.

[Timm1]

Ok- I think I'm making this more complicated than it is.  The error that it is giving talks about my Direct Reports gallery.  So, all I need is the formula to show only users with Managers.  I have already filtered for departments only but now, need to filter with user's with managers. The formula I currently have is 

Filter(Office365Users.SearchUser({searchTerm:cmbName.SearchText,top:600}),Len(Department) >0)

I need to add a filter to show only users with managers.

Sorry for the confusion and thanks again!

Tim