Ask Search:
Gary YantsosGary Yantsos 

(Checkbox) Formula field with multiple CONTAINS

Hello. I'm having a problem with a formula field, mostly because its a checkbox. Below is my existing formula, but I'd like to refine it by checking for a string instead of a literal value. I've ready that using the ":" symbol to separate values converts to "equals".  Basically, what I'm trying to do is to check to see if the "Lead_Product__c" field contains the string "OCT" or "TRC" or ...  If the string exists, make the value 1, if not, 0. Really having trouble with this syntax. Any help you could provide would be great!. Thanks

if(
CASE(Lead_Product__c ,
"11MP Color/BW Myd Imaging System w/FAF/ICG", 1,
"3D OCT-2000", 1,
"5MP Color/BW Myd Imaging System w/FAF/ICG",1,
"5MP Color/BW Mydriatic Imaging System:CV-5000S",1,
"CV-Tablet:Endpoint Management",1,
"EXAM-5000",1,
"EZ Capture",1,
"IMAGEnet",1,
"KR-1W:MC-3",1,
"Monochromatic BW Myd System for FA/FAF/ICG",1,
"Nikon Digital Attachment for the TRC-50DX:OMS-710",1,
"OMS-800",1,
"OMS-90",1,
"Pascal Streamline",1,
"Pascal Streamline 577",1,
"PASCAL Synthesis",1,
"Spaide Autofluorescence Filters:Synergy",1,
"TRC-50DX",1,
"TRC-NW300",1,
"TRC-NW300 8MP Non-Myd System Telemed Capable",1,
"TRC-NW7SF",1,
"TRC-NW7SF Myd/Non-Myd Imaging System",1,
"TRC-NW8",1,
"TRC-NW8 High Resolution Non-Myd System",1,
"TRC-NW8F",1,
"TRC-NW8F Myd/Non-Myd Imaging System",1,
"TRC-NW8F Plus",1,
"SP-1P",1,
"Cataract Workstation",1,
"DRI OCT-1 Atlantis",1,
0)=1,TRUE,FALSE)

Best Answer chosen by Gary Yantsos
Steve MolisSteve Molis
Also if Lead_Product__c is a Text Field you could use
OR(
CONTAINS(Lead_Product__c ,"TRC-"),
CONTAINS(Lead_Product__c ,"OMS-"),
CONTAINS(Lead_Product__c ,"OTC-"),
CONTAINS(Lead_Product__c ,"ABC-"))
or if it's a Picklist you could use
 
OR(
CONTAINS(TEXT(Lead_Product__c) ,"TRC-"),
CONTAINS(TEXT(Lead_Product__c) ,"OMS-"),
CONTAINS(TEXT(Lead_Product__c) ,"OTC-"),
CONTAINS(TEXT(Lead_Product__c) ,"ABC-"))


 

All Answers

Deepak AnandDeepak Anand
I am thinking even writing this with a CONTAINS would still grow up to huge list. Again with the CONTAINS function along with the multiple operator(:) has a couple of caveats that you should keep in mind or consider before migrating away from this CASE.

Especially with your Picklist that contains spaced lengthy strings in them, using the CONTAINS with (:) can be tricky ?!?

Ref: How do I use the CONTAINS function?​
Gary YantsosGary Yantsos
I'm hoping to at least consolidate some of the list short-term. For example, I can conaolidate all of the items that contain "TRC" straight away. Something about the syntax that I'm not getting right. I need to stay away from (:) due to the statement transforming into AND instead of OR. I'll get this right sooner or later...
Steve MolisSteve Molis
With Formula Checkbox fields you don't need an IF Statement or an = TRUE/FALSE you can just say:
 
CASE(Lead_Product__c ,
"11MP Color/BW Myd Imaging System w/FAF/ICG", 1,
"3D OCT-2000", 1,
"5MP Color/BW Myd Imaging System w/FAF/ICG",1,
"5MP Color/BW Mydriatic Imaging System:CV-5000S",1,
"CV-Tablet:Endpoint Management",1,
"EXAM-5000",1,
"EZ Capture",1,
"IMAGEnet",1,
"KR-1W:MC-3",1,
"Monochromatic BW Myd System for FA/FAF/ICG",1,
"Nikon Digital Attachment for the TRC-50DX:OMS-710",1,
"OMS-800",1,
"OMS-90",1,
"Pascal Streamline",1,
"Pascal Streamline 577",1,
"PASCAL Synthesis",1,
"Spaide Autofluorescence Filters:Synergy",1,
"TRC-50DX",1,
"TRC-NW300",1,
"TRC-NW300 8MP Non-Myd System Telemed Capable",1,
"TRC-NW7SF",1,
"TRC-NW7SF Myd/Non-Myd Imaging System",1,
"TRC-NW8",1,
"TRC-NW8 High Resolution Non-Myd System",1,
"TRC-NW8F",1,
"TRC-NW8F Myd/Non-Myd Imaging System",1,
"TRC-NW8F Plus",1,
"SP-1P",1,
"Cataract Workstation",1,
"DRI OCT-1 Atlantis",1,
0) = 1

 
Steve MolisSteve Molis
Also if Lead_Product__c is a Text Field you could use
OR(
CONTAINS(Lead_Product__c ,"TRC-"),
CONTAINS(Lead_Product__c ,"OMS-"),
CONTAINS(Lead_Product__c ,"OTC-"),
CONTAINS(Lead_Product__c ,"ABC-"))
or if it's a Picklist you could use
 
OR(
CONTAINS(TEXT(Lead_Product__c) ,"TRC-"),
CONTAINS(TEXT(Lead_Product__c) ,"OMS-"),
CONTAINS(TEXT(Lead_Product__c) ,"OTC-"),
CONTAINS(TEXT(Lead_Product__c) ,"ABC-"))


 
This was selected as the best answer
Gary YantsosGary Yantsos
Beautiful. Thanks again Steve. I owe you another beer. See you at #DF15