Filter gallery by comparing Choice column options from a List, against the items in a collection

[Syndicate_Admin]

Hi there,

I have a collection with one column. The values in the column are chosen by the user at a previous point in the app.

There is a SharePoint List Choice column that contain the same values that would appear in the collection.

I want to filter a gallery based on the items that are matched from the collection to the Choice column.

I have this, in which the syntax is correct, but it doesn't seem to filter the gallery items at all:

Filter('Inhaler data for app', 'Overarching Drug Class'.Value <> "SABA" &&
Concat(ActivityCollection2,Value&",") in Concat('Drug Class',Value&","))  <----Adding this doesn't do anything

I have many other conditions in the filter, which seem to work ok, but this one doesn't. Full code:

Hope you can help 🙂

Thanks in advance! Joel

[Syndicate_Admin]

Hi Paul,

Thanks for your reply. I'm sorry I don't think I made myself very clear.. The choice column does not allow multiple values. So I just want to match items from a collection to the choice column. So for example:

If Collection contains: "Apple", "Pear", "Orange". The Gallery is filtered on the choice column that has those choices set for each record.

This certainly sounds like a common requirement, I would have thought.

Thanks again,

Joel

[Syndicate_Admin]

I would advise against using multi-choice columns as they are not a valid concept in relational databases (a multi-choice field is not an atomic value and so violates first normal form) and so cause lots of problems for querying, as your question demonstrates.

Others here may be able to suggest some tricks/work-arounds, but I would suggest modifying your data structure to avoid the use of multi-choice fields.

Note that SharePoint is very limited as a datasource in terms of relational querying ability (probably why it implemented choice fields in the first place!), so consider how much data your App will need to hold/manipulate and maybe consider a different datasource (though this will have licensing implications, so you may need to also consider a platform other than Power Apps as well).