Needing a formula in a saved search to return a value if a multi-select field contains the value of a different field.

I’m working on a formula (text) field on a saved search where if {fieldA} = {fieldB} then return a certain value.
I am able to do this with the following formula:
CASE WHEN {fieldA} = {fieldB} THEN {name} ELSE Null END
However, the issue is that {fieldA} is a multi-select field. So, this only works if only one thing is selected in that field. Is there some sort of “contains” function I could add to this to say if {fieldA} contains whatever is selected in {fieldB}, then the correct value would be returned?

Rookie Asked on January 31, 2020 in Saved Searches.
Add Comment
1 Answer(s)
Best answer

Use INSTR to determine the index where fieldB occurs in fieldA (https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions068.htm).

Alternativly use the like condition within your case (https://docs.oracle.com/cd/B19306_01/server.102/b14200/conditions007.htm)

Both options may require tweaking if a select option contains the value of another select option within it since both are based on strings.

Intermediate Answered on January 31, 2020.

I greatly appreciate you input. This got me a little closer, I think, but it still isn’t returning the correct results when I add another value to the multi-select field. However, I think it may be due to my lack of knowledge about how INSTR should work.

I used the formula below and it gave the results I was looking for even more accurately than using the formula I noted originally, but like I mentioned, it still didn’t return the right results if I added a value to the multi-select.

CASE WHEN INSTR({fieldA}, {fieldB}) IS NOT NULL THEN {name} ELSE NULL END

I am doing a formula (text) field with a summary type as ‘count’. The purpose is to count how many records there are that share a value in fieldA and fieldB. I also have the saved search grouped by the ‘name’ field so it breaks those counts up properly.

on January 31, 2020.

INSTR returns the index number where one string contains another string.

As an example, if fieldA was “Andorra,United Arab Emirates,United Kingdom,United States” (4 select options) and fieldB was “United Kingdom” then INSTR({fieldA}, {fieldB}) would be 30 because “United Kingdom” appears in the 30th position.

I would not want to do this if fieldB is also a multiselect. If field B was instead “Andorra, United States”, then the formula would return 0 since that exact string does not occur in fieldA.

on February 1, 2020.

Thank you so much for taking the time to help me with this. INSTR ended up being what I needed. What was keeping it from showing the correct results after I did the INSTR formula was what I was grouping my search on. I also applied an INSTR formula to what I was grouping the search by and it got me where I needed to be.

Again, thank you!

on February 3, 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