Contains() with partial text search

Sam87 · 10-29-2021 04:37 PM

FILTER(“TABLE”,(CONTAINS([FREE TEXT FIELD],[TAGS]))) of List Type Ref

Hi All, trying to do the following:

I have a FREE TEXT FIELD where the user can type something such “I want to know more about cars.” (for example)

The lookup TAGS field contains “cars” and the rows tagged as such are returned. The string is found contained in the FREE TEXT FIELD (as expected)

When the Tags field contains “boats, bikes, cars” then it is not returned. I know this is due to the full string being referenced in the contains lookup. I would like this to be returned when a partial match on the contains. Is there a means to achieve this?

Suvrutt_Gurjar

You could try a combination of SPLIT() and INTERSECT() functions.

Something like

ISNOTBLANK(INTERSECT(SPLIT([FREE TEXT FIELD], " ")`, SPLIT([TAGS], ", ") ))

Assumes both ([FREE TEXT FIELD] and [TAGS] are text fields.

If [TAGS] is a list with base as text, then please try

ISNOTBLANK(INTERSECT(SPLIT([FREE TEXT FIELD], " ")`, SPLIT([TAGS] , “,”) ))

Sam87

Thanks @Suvrutt_Gurjar

Sam87

The above field is a list type so i am not able to get the function to work correctly. “The expression is valid but its result type ‘Yes/No’ is not one of the expected types: List”

Sam87

@Suvrutt_Gurjar can you help take a look at this issue above?

Suvrutt_Gurjar

Sorry for responding late as it was night time past 10:30 PM in my geographic zone when you responded.

Need to try that suggested expression condition in place of CONTAINS() condition. Please try

FILTER(“TABLE”,ISNOTBLANK(INTERSECT(SPLIT([FREE TEXT FIELD], " ")`, SPLIT([TAGS], ", ") )))

Or the below

FILTER(“TABLE”, ISNOTBLANK(INTERSECT(SPLIT([FREE TEXT FIELD], " ")`, SPLIT([TAGS] , “,”) )))