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?
This is what I can come up with: REGEXP_INSTR(REGEXP_REPLACE(‘68,69,70,72’, ‘[^0-9,]+’, ”),'(^68,|,68,|,68$)’)
First, replace all characters that are not digits or a comma with an empty character. Then, look for the number (68 is the example) either at the beginning followed by a comma, or beginning and ending with a comma, or at the end ending with a comma. The result of this formula will be an integer. If the value doesn’t exist in the array then it’s 0, if it does exist, then it’s larger than 0.