Filter Gallery Populated by Sharepoint List with Dropdown

[CodyS]

There are SO many articles on this topic and I feel like I've tried all of them to no avail.  Hoping someone can help point me in the right direction.

I have a Sharepoint List ("Gamer Directory") with columns for: 'Gamertag / Handle' (Title), 'Name' (Person), 'System / Platform' (Choice), and 'Game(s) You Play' (Choice).

PowerApps auto-created the basic shell of what I want - a searchable directory from this list; but I want to add a dropdown to filter by 'System / Platform' (FilterPlatform) and another to filter by 'Game(s) You Play' (FilterGames) - along with the default search Name/Title.  I've tried EVERYTHING...to no avail. My latest attempt was following the instructions in this thread; just trying to get ONE dropdown to work before tackling the second.

So I've got my App OnStart :

ClearCollect(AllChoices,{Value:"All"},Choices('Gamer Directory'.'Systems / Platforms'))

Which...to my understanding is taking all the choices from 'Systems / Platforms' in my list and placing them into a collection inapp.

The dropdown (FilterPlatform)'s Items is set to AllChoices.   All this works - my dropdown is populated with a set of choices from my list (preceeded by 'All').  

This is where things stop working - when my gallery's Items is set to:

If(
FilterPlatform.Selected.Value<>"All", 
Filter([@'Gamer Directory'], StartsWith(Title, TextSearchBox.Text), 'Systems / Platforms'.Value = FilterPlatform.Selected.Value),
Filter([@'Gamer Directory'], StartsWith(Title, TextSearchBox.Text))
)

My gallery goes blank. In fact - PowerApps says this is wrong - citing specifically that the "=" in the 3rd line is an 'invalid argument'.

When I modify the the line to:

Filter([@'Gamer Directory'], StartsWith(Title, TextSearchBox.Text), "Systems / Platforms" = FilterPlatform.Selected.Value),

removing the .value from my column and putting the column title in double-quotes instead of single quotes - it validates AND works with 'All', but the moment you change it to any of the selections it returns no results.

My initial stabs at this had it pulling directly from the sharepoint list (no clear collect), but the issue seems to be a combination of my choice column, the populating of the dropdown list, and this one line triggering an 'invalid operator' warning because of the "=". 

I'm stuck.  Any advice? Seems like this should be simple....what am I missing?

[RandyHayes]

@CodyS 

So, first, let's clean up the formula a bit to this:

Filter([@'Gamer Directory'], 
    StartsWith(Title, TextSearchBox.Text) &&
    (FilterPlatform.Selected.Value = "All" || 'Systems / Platforms'.Value = FilterPlatform.Selected.Value)
)

 You definitely want the single quotes on the field.  Putting double turns it into a string/text.

Let's start there and see what you have.  

The rest seems good, but if this doesn't work, we'll need to explore the choice column more.

I hope this is helpful for you.

[CodyS]

Thanks for your help! That's what I thought with the single quotes...but I was grasping at straws for any form of progress. Haha.

I replaced the formula with the on you provided and I'm back to it having all kinds of problems - breaking the Title, Platforms, and Games values in the gallery.  I don't think I quite understand why - it seems like the elements are independent of each other...but here we are.

Oddly enough it doesn't call out a specific part of the formula as an error - but when I first pasted it in it again had a problem with the "=" after the 'OR'.   App checker lists 16 errors - but I think they're all derived from 'Invalid Argument type' and 'Incompatible Type' in the gallery filter equation.

[RandyHayes]

@CodyS 

Yeah, don't worry about the errors, they are all byproducts of the formula error you have...let's get that corrected.

What are your settings for the column in SharePoint?  Choice column with Drop-Down Menu (not Checkboxes) Not allowing fill-in choices?

[CodyS]

Both Systems / Platforms and Game(s) You Play are choice columns (dropdown), with multi-select and "can add values manually".  Functionally the latter (especially) was needed to allow folks to input their own games should they not already be listed.

[RandyHayes · Accepted solution]

@CodyS 

Ah ha!  There is the issue.  Choice columns that are multi-select are troublesome in PowerApps.

You will need to change your formula to the following:

Filter([@'Gamer Directory'], 
    StartsWith(Title, TextSearchBox.Text) &&
    (FilterPlatform.Selected.Value = "All" || FilterPlatform.Selected.Value in 'Systems / Platforms'.Value)
)

You will get a delegation warning on the "in" operator as in is not delegable to the data source.  If you don't expect more than your data row limit setting in your app, then you can ignore the warning.

[CodyS]

All this time and the problem was solved by changing "=" to "in"...man. The best and worst feeling of coding. Haha.

This worked. Thanks so much for your help. I hadn't even considered the options of the choice column would effect the formulas...but it makes so much sense in hindsight. 

A few super quick follow-up questions so I can dig more / research some more topics to make sure this a more sustainable solution for larger sets of data, and layer on additional functionality :

• Adding a second dropdown to the filter seems pretty straightforward with just another OR - but is it correct syntax to create another collection .OnStart to manage the values for that dropdown? Does it make sense to do this:

ClearCollect(PlatformChoices,{Value:"All"},Choices('Gamer Directory'.'Systems / Platforms'));
ClearCollect(GameChoices,{Value:"All"},Choices('Gamer Directory'.'Game(s) You Play'))​

• If I thought my data sets would balloon a bit larger than the delegation warning suggests - I suspect this is where I'd go down the rabbit hole of collecting my data onload into a collection and running it off that? I guess I could read up a bit more on delegation as it's not an area I've hit yet.

Appreciate your time and help! You're a rockstar. 🙂

[RandyHayes]

@CodyS 

Yes, when you have multi-select on Choice columns, they then have a Table of values rather than a Record of value.  So, you have to at least take this approach of looking "IN" the table of values.

Some better choices (no pun intended) would be to separate your Game and Platform choices out to another list.  This way it is entirely flexible and does not suffer from a complex column type (which Choice is considered).  What you do (for a one to one) is simply create a numeric column in the Gamer Directory list that will store the ID of the record in the Game and Platform list that is related.  If one to many (as you would need to implement a multi select) you could do one of a couple of things - one is to have yet another list that would have two columns - the Gamer Directory ID and the Platform or Game ID.  You can then Filter the Platform list by the Gamer Directory ID and get a list of all the records which would be in the Platform list (for example).  This can also be done with concatenated strings and a few other ways, but that is one we use often.

As for collecting your choices...this is really the only option if you are adding a row (which you are with the "All" record).  However, unless you are going to have more than 2000 Platforms or Games then your collections would be fine.

Your real challenge with delegation comes from the main data and where you envision it to be in the future.  There are a variety of ways to get around it pretty easily, but it is always good to plan these things from the start.  But in general, you want to stick with delegable columns as much as possible and formulas that take advantage of them.  There is plenty of info on ways around that on this forum and, of course, you can always feel free to post any questions you have.

I am glad this was helpful for you.

[CodyS]

Sweet - incredibly helpful. Lots to chew on - even more new wrinkles in my brain.

Thanks Randy!