Multiple CONTAINS filter in Analytic App using Text Box

Question

Hi All,

I would like to let users enter keyword(s) into a text box and filter a column using the CONTAINS() function. I have the text box setup already using a multiline option checked and would want to understand how to configure the Formula in the Action Tool. Here is what I have that solves for a IN function but I'd like to do the same for the CONTAINS() function and separate each CONTAINS() function w/ an OR --- but not end w/ the OR (so the formula works).

Here is configuration for the IN function in the Filter Tool:

[URL] in ('keyword')

Here is the configuration in the Action Tool:

IF !ISEMPTY([#1])
THEN Replace([Destination],'keyword',REGEX_REPLACE([#1],'
',','))
ELSE Replace([Destination],'[URL] in ('keyword')','1=1')
ENDIF

Please let me know if there's any questions!

Best,

Henry

CharlieS · Accepted Answer

Hi @hctlee0812ss. This is a bit odd, but it works. Maybe someone will chime in with a more elegant solution.

So the Idea is that we want to concatenate the user input with the follow Start, Separator, and End text strings (where [Field1] is the field we're filtering on):

Contains([Field1],"

") OR Contains([Field1],"

")

The ideal state is that we could just replace the newline character with the separator string whenever it appears. The problem I ran into was the character rules in RegEx while writing this expression. So I did this with two replacements.

1. Use RegEx_Replace( to replace the newline characters (
) with a placeholder (#)

2. Use the standard string Replace( function to replace the "#" with the separator string we actually want.

The Action expression looks like this:

IF isempty([#1]) THEN '1' ELSE
Replace(
  'Contains([Field1],"'+RegEx_Replace([#1],'
','#')+'")'
,'#','") OR Contains([Field1],"')
ENDIF

I also added this into an IF statement to pass all records if nothing is entered like you had.

Check out the attached workflow for an example of this in action and let me know if you have any questions.