RegEx formula in Action tool to prepare list for IN condition

[ehlerd]

Users will enter list of Skus as such

123545

234564

324568

I need the action tool to format the list as 

'123545',

'234564',

'324568'

The below formula in the Action tool

if !isempty([#1])
then replace([Destination],'9999',regex_replace([#1],'\n "','","',''))
else replace([destination],"[Sku #] IN ('9999')",'1=1')
endif

produces in the Debug formula tool

[Sku #] in ('123545

234564

324568')

What am I missing in my formula to make this work?

[flying008 · Accepted solution]

Hi, @ehlerd 

Maybe you looking for this :   then replace([Destination],'9999',regex_replace([#1],'\n "','","',''))

"'" + regex_replace([#1],'\n',"',\n'") + "'"

[Yoshiro_Fujimori]

@ehlerd ,

If you want to convert each row of the list, how about once put it to Input Data tool and then use Formula tool?

The formula can be as simple as below:

"'" + [SKU] + "'"

If you have some other reason to use RegEx, please ignore my comment.

[ehlerd]

Thank you for the response.

I have updated the formula as suggested

if !isempty([#1])
then replace([Destination],'9999',"'" + regex_replace([#1],'\n',"',\n'") + "'")
else replace([destination],"[Sku #] IN ('9999')",'1=1')
endif

Almost there but it is producing this with one extra single quote beginning and end

[Sku #] in (''349182',
'020849',
'015013',
'812760',
'018107'')

[ehlerd]

The extra quotes is coming from the Filter Tool.  If I remove the quotes from the formula, it will fail

[flying008]

Hi,@ehlerd 

Maybe you can try this:

Old formula part: then replace([Destination],'9999',"'" + regex_replace([#1],'\n',"',\n'") + "'")  

New formula part: then replace([Destination],"'9999'","'" + regex_replace([#1],'\n',"',\n'") + "'")

In fact, you need change the all  in IN(), so you don't care newline char.