Compare value to multi select field in saved search formula

I have a saved search that has a filter critera on a multi select field. I need to compare the value in the multi select with another field value (non multi select).

I am using this formula:

case
when SUBSTR({field1.id}, INSTR({field1.id}, {field2.id}), LENGTH({field2.id})) = {field2.id} then 1
else 0
End

The problem is, that the values in the multi select field (field1) could be ‘68,69,70,72’ and when I am using this formula to compare it to for example  the value 7 (field2), the condition will be true. Which it should not.

I have tried solving this with a formula like the one below, but I am then limited by number of characters in the formula so I can only do it for 20 values. And a user may have over 30 selected values.

case
when substr({Field1.id},1,instr({Field.id},’,’)-1) = {Field2.id} then 1
when substr({Field1.id},REGEXP_INSTR({Field1.id}, ‘,’,1,1,1),instr({Field1.id},’,’)-1) = {Field2.id} then 1
when substr({Field1.id},REGEXP_INSTR({Field1.id}, ‘,’,1,2,1),instr({Field1.id},’,’)-1) = {Field2.id} then 1
when substr({Field1.id},REGEXP_INSTR({Field1.id}, ‘,’,1,3,1),instr({Field1.id},’,’)-1) = {Field2.id} then 1
else 0
end

I believe it could be solved easily if there were any split possibilities in saved search formulas, but I cannot find that there is..

Does anyone have a solution on how to do this with a formula? Is it possible?

Rookie Asked on March 2, 2020 in