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 Saved Searches.
Add Comment
1 Answer(s)

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.

Beginner Answered on March 16, 2020.
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.
  • This site made possible by our sponsors:   Tipalti   Celigo   Limebox   Become a Sponsor   Become a Sponsor