Convert string list from tree tool to usable list in filtering

[spencerswartz]

I am creating an analytic app that is connecting to our database to create a tree tool representing our product hierarchy. The tree tool is currently selecting the correct data although I am having trouble parsing the output into a usable format in the in-database filter tool. A sample output of the tree tool is below along with the intended change to the filter tool:

Tree Tool output:

'100021358
100021854
100021954
100021
1000'

I am looking for this corresponding In-Database filter (last three digits of all 9digit nums):

"ARTICLE_CAT_CODE" IN (358,854,954)

Currently, within an Action tool, I have the following formula which gets me close to halfway there:

'"ARTICLE_CAT_CODE" IN ("' + REGEX_Replace([#1], '\n', '","') + '")'

this produces this output to the In-Database filter:

"ARTICVLE_CAT_CODE" IN (100021358,100021854,100021954,100021,1000)

Any help in getting towards the final result would be greatly appreciated!

Thank you!

[jdunkerley79 · Accepted solution]

I suggest something like

'"ARTICLE_CAT_CODE" IN (' + REPLACE(REGEX_Replace(TRIM([#1],"'"), '(\d+(\d{3}))', '$2'), "
",",") + ')'

First remove the ' with TRIM

Then replace numbers with just the last 3 digits 

REGEX_Replace(..., '(\d+(\d{3}))', '$2')

If you need quotes around the threes digits you can amend the '$2' here to add them.

Finally convert new lines to , with a REPLACE

Hope it helps