Action setting to update a Contain formula

[ehaeri]

Hi all,

I am trying to setup an action which dynamically updates a filter based on user selection(s) from a List Box:

List Box options are:

7Z369
7B546
7A508
7540
7C601
7C602
7C603
7A508

User may choose 1+ item(s) from List Box and the formula should be flexible enough to capture all selected values as: Contains([Reported BPNs], "7Z369") if only "7Z369" is selected 

OR 

Contains([Reported BPNs], "7Z369") or Contains([Reported BPNs], "7B546") if 7Z369 and 7B546 or any other possible combinations are selected.

Could anyone help me with that please?

Thanks in advance.

[DavidP]

I think this would work

[ehaeri]

Thanks David,

My reported BPNs column looks like this:

Function "IN" works perfectly only when I have a single value in each row, however, with the multiple items separated by comma in some of the lines, my only option is to use multiple "CONTAINS" formulas linked with an OR.

Or alternatively, break it down to several rows with single value and use function "IN" instead.

[danilang · Accepted solution]

Hi @ehaeri 

In this case you can use Regex_Match as opposed Contains to perform the same operation.  The initial formula is 

REGEX_Match([Reported BPNs], ".*?(7Z369).*")

The action tool is configured to output the list with a pipe "|" as the joining character and replace "7Z369" in the formula tool with the result, i.e .*?(7Z369|7B546).* which matches any string with 7Z369 or 7B546 in it

Dan

[ehaeri]

Hey @danilang

It worked perfectly!

Thank you very much.