Filter with Contains rather than Equals

[Sam87]

The use case is that I have a form where a user will choose red, blue, or green in the “COLOR SELECTION FIELD”.

I have a field (as shown below) that will return all columns from another table when that selection above is found in the “COLORS” column for that other table.

FILTER(“TABLE”, ([COLOR SELECTION] = [COLORS] ))
What I want is FILTER ROWS WHERE COLOR SELECTION IS CONTAINED WITHIN COLORS and also to choose the columns if possible.

The COLORS field is not an exact match to COLOR SELECTION…more like tags where it can have yellow, green, blue (comma separated). I want all COLORS column values where COLOR SELECTION = “Blue” for example, whether exact match or if “Blue” is simply found in the COLORS value ( yellow, green, blue for example).

[Aurelien · Accepted solution]

Hi @Sam87

What about:

FILTER(“TABLE”,
  CONTAINS([COLORS], [_THISROW].[COLOR SELECTION])
)

For reference:

help.appsheet.com

CONTAINS()

Does text contain fragment?

help.appsheet.com

FILTER()

Select rows of table or slice

[Sam87]

Thanks,

I used this and it works great!

FILTER(“TABLE”,
CONTAINS([COLORS], [_THISROW].[COLOR SELECTION])
)

I decided to just return one column from the lookup table based on the contains.

I used SELECT(COLORSTABLE[RETURN COLUMN], CONTAINS([COLORS], [_THISROW].[COLOR SELECTION])
)

For some reason the whole table is being returned (with the proper contains result) rather than just the return column. Any thoughts?

[Aurelien]

Sam87:

For some reason the whole table is being returned (with the proper contains result) rather than just the return column. Any thoughts?

You may want to share a screenshot in order to help us better understand what you describe, as well as a screenshot of your table structure.

[Steve]

help.appsheet.com

IN()

Is item in list?

[Aurelien]

I neglected about the IN(), don’t know why…

@Sam87 Did you see @Steve’s suggestion ?

Edit : I remember : because in my mind, IN() is about looking for an item in a list.
Here, @Sam87 is looking for an item in “something I understood as being of type text”.
@Steve , your opinion ?

[Steve]

If the data being searched is a simple textual value, CONTAINS() is the correct choice.

[Sam87]

I am good with this, works. It works great now with contains…but my alternate formula to just pull one column from the table on the match isn’t working. It returns all columns:

SELECT(COLORSTABLE[RETURN COLUMN], CONTAINS([COLORS], [_THISROW].[COLOR SELECTION])
)

[Aurelien]

The good row but all the columns ? ==> that makes me think:

• your column [RETURN COLUMN ] is of type Ref
• this is the key_column of your table that it refers to

Can you provide screenshot in order to confirm/deny this ?

[Sam87]

That is my return column. It is text.

[Sam87]

This returns all columns rather than just the ‘Reason for PR Failure’ column

SELECT(SystemFix[Reason for Failure], CONTAINS([Tags],[Field of concern]))
Note: This expression could impact performance.

The list of values of column ‘Reason for PR Failure’
…from rows of table ‘SystemFix’
…where this condition is true: ((The value of column ‘Tags’) contains the text value (The value of column ‘Field of concern’))

[Aurelien]

I was interested in a screenshot of the output too, sorry for not having been clear.

I think you don’t display every necessary information, as the SELECT expression in your last post is not visible in the screenshot provided.
Also, a SELECT returns a list. a Text type is not appropriate, you might want to change it to a List type.