Filter gallery if string is contained within column

[MarkH1]

Hi Folks,

Column structure -> [Category1, Category2, Category3]

I need to be able to filter my gallery to pull back all items that has 'Category2' contained within it. Is this possible?

Thanks

[LRVinNC]

Yes, it is possible.  How is your category column defined?  Is it a choice column or a text column?  Do you always want to filter for 'Category2' or will the user need to choose which category to filter for?

[MarkH1]

It is a multi-line text column in SharePoint. One one screen I may want to pull back call Category2 items and then on the next screen, I may want to pull back all Categoriy1 items. Each Category is separated by a comma.

[poweractivate]

@MarkH1 

Glad you posted some clarification, however, there is still some ambiguity in this question right now.

1) Do you mean one single multi line column with each actual "column" you were really talking about, separated by a comma within that same column, or did you actually mean three separate multi line text columns?

For the purposes of the following questions, we'll presume for now it is not the latter but the former (one multi line text column only with three comma separated 'columns' inside of it).

2) Presuming that it is one multi line text column only with three comma separated 'columns' inside of it -> is this a required way to format it? Is it safe to assume it is impossible to separate into 3 separate columns instead in SharePoint?

Reason we ask is, that we already can tell that one way is probably far easier than the other - and we are unsure the "other" is even possible at all in Power Apps without checking it quite a bit further in detail.

3) Please check the below screenshot of four example records in a SharePoint List with Multi Line Column Text Field.

When is it possible for what you call 'Category2' in the example above not to be defined?
Is it literally the words 'Category2'? We are not sure if that is what you meant.

Something else can be in there other than the words 'Category2' but it would still be defined because there is an item (Item 1), a comma (a separator in your schema), then another item (Item 2) then another comma (a separator), then Item 3, as you can see from above example. This is another way your question can be interpreted, and we are unsure if you meant the words 'Category2', the 'Category2' derived from separation we are implying here or maybe something else entirely.

4) If the schema we presented in #3 above related to [Something,SomethingElse,SomethingElseElse] (where SomethingElse would be a definition for that record of Column2 in the aforementioned example) - if this was what you meant we would be unsure about the following then:

When would 'Column2' ever be undefined?

For example, would saying [Something01,,SomethingElseElse03] be example of Column2 being undefined because of there being two commas there?

Or is there a key word like null, so like [Something01,null,SomethingElseElse03] - if Column2 is the word 'null' - is that when it is undefined in Column2?

In which specific cases would 'Column2' be undefined in your specific setup?

[MarkH1]

Hi @poweractivate , @LRVinNC 

Sorry for the delayed response and thank you for the reply. Here is the actual column

It's not possible to split them into multiple columns unfortunately. Here is an example of what I would like to do:

Screen1 - Gallery contains all items that contains 'Global Investigations' in this column.
Screen2 - Gallery contains all items that contains 'Local Policy Teams' in this column.
Screen 3 - Gallery contains all items that contains 'Transaction Monitoring' in this column

Each entry is comma separated

Does this make sense?

[poweractivate]

@MarkH1 

We were able to come up with solution for you with SharePoint List Data Source with single multi line text column with comma separated values, and without any delegation warnings as well.

Presuming a SharePoint List like this with the following test data:

Here is a possible complete solution for all your three Screens and the use cases:

-----------

Explanation:

1) Screen1 - Gallery contains all items that contains 'Global Investigations' in this column.

A) On the OnVisible Property of Screen1, use this formula:

Set(_V1_01,"Global Investigations");ClearCollect(_C1_01,MultiLineColumn);ClearCollect(_C1_02,Filter(_C1_01, StartsWith(MultiLineTextColumnTitle,_V1_01)))

B) On the items Property of Gallery1 use this formula:

_C1_02

2) Screen2 - Gallery contains all items that contains 'Local Policy Teams' in this column.

A) On the OnVisible Property of Screen2, use this formula:

Set(_V2_01,"Local Policy Teams");ClearCollect(_C2_01,MultiLineColumn);ClearCollect(_C2_02,Filter(_C2_01, _V2_01 in Last(FirstN(Split(MultiLineTextColumnTitle,","),2)).Result))

B) On the Items Property of Gallery2 use this formula:

_C2_02

 

3) Screen 3 - Gallery contains all items that contains 'Transaction Monitoring' in this column

A) On the OnVisible Property of Screen3, use this formula:

Set(_V3_01,"Transaction Monitoring");ClearCollect(_C3_01,MultiLineColumn);ClearCollect(_C3_02,Filter(_C3_01, _V3_01 in Last(FirstN(Split(MultiLineTextColumnTitle,","),3)).Result))

B) On the Items Property of Gallery3 use this formula:

_C3_02

-----------

Just in case you want it, we can also send you the app itself as well along with instructions how to import the app into your environment, so you can check it directly in the specific sample app if you prefer it.