Filtering ComboBox

[StephenGW]

All,

I have a combo box that uses Office365Users to fill. I want to limit this to only people in my City. I can do this like this, Filter(Office365Users.SearchUser({searchTerm:cbName.SearchText,top:15}),City="Atlanta") but then the drop down part is not populated. I thought maybe I could use the App=OnStart with ClearCollect(MccNames,Filter(Office365Users.SearchUserV2({searchTerm:""}).value,City="Atlanta")) which does create a collection but when I put that collection for the Items in the Combo box it only shows the 5 like in the collection preview.

Anybody have any ideas how I can make this work?

Thanks

[RandyHayes · Accepted solution]

@StephenGW 

Your issue is coming in that you are limiting the search results to the top 15.  That is the inner table of results that is then used on your filter.  So, if none of the 15 returned have Atlanta for the City, then your results will be nothing.

Change your Items property formula to:

    Filter(Office365Users.SearchUserV2({searchTerm: cbName.SearchText}).value,City="Atlanta")

You don't need any collections for this as you're not implementing an in-memory database. - you're just filtering results.

I hope this is helpful for you.

[vish3027]

Hello @StephenGW ,

Yu can use below formula as items property of Combox:

Filter(Office365.SearchUser({searchTerm:ComboBox1.SearchText}),("Chicago" in City)).DisplayName

Thanks

Please accept as solution if it works for you.

[StephenGW]

@RandyHayes 

This seems to work but the drop down only show 5 results now. Is there a way to increase this to show all? There should be around 150 after the filter.

[StephenGW]

@vish3027 

This did not work. IT gave me two errors, Invocation if unknown or unsupported function, and The function 'Filter' has some invalid arguments.

I did get Randy's solution to work but it only displays 5 results in the drop down. If you know of any way to expand that I would be grateful.

[RandyHayes]

@StephenGW 

Your formula will be returning all of the filtered records.  There is no limit on the combobox that would only show 5.

So, let's look at your formula...in the Formula Editor, put your cursor at the end of the formula or highlight the entire formula and see what the editor is showing.

In my case, it shows no data because we have no one in Atlanta. But yours should show all of the records that the formula is returning...do you see only 5 there or are there more?

[StephenGW]

@RandyHayes 

It only shows 6 results (I counted wrong) but there should be many more. If I search for other names I know are in the same city their names come up but they don't show in he drop down when it is initially clicked only 6 names and they are not even the first 6. It is odd behavior.

[RandyHayes]

@StephenGW 

How many users do you have in your Office365 system?  If it is more than 1000, then the default "Top" value for the SearchV2 action (we're not specifying it, so it will default) is 1000.

[StephenGW]

@RandyHayes 

I don't know but it would for sure be way over 1000. So that might be the problem then. It is only displaying the top 1000 then filtering out by city and only leaving 6, but then the search will still find the rest? So is it possible to sort my city to the top then filter?

[RandyHayes]

@StephenGW 

Yes that would be the issue if there are more than 1000.  The search is only returning the top 1000 and then filtering on those results.

Unfortunately you can't search on the city in the SearchUserV2 action, nor sort by it - you are at the mercy of the SearchUserV2 action limits.  And I believe the maximum results are 1000 - I may be wrong on that as it has changed over the years and it was 1000 before.  

So, you're not left with many options.  You could try to create a table of all of the users in your app, but that would be extremely intensive and a performance hog and memory hog.  Or, you could somehow devise a separate list that is managed by a flow which would perform the intensive actions and then update a list with all the users of a particular city (or cities) and then use that as the source of the search (i.e. no longer user the SearchUserV2 action, but instead search a list).