Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Chris ValkoChris Valko 

Formula Field Checkbox - How to add multiple values to filter

I am attempting to get a checkbox formula field to be TRUE when another field, "Shipping State", does not contain certain states.  I tried the following but it is not seeing each state as a different value.  What could I be missing?  Thanks!
  
NOT(
AND(
Shipping_State__c = ("AL,AR,AZ,CA,CO,CT,DC,DE,FL,GA,IA,ID,IL,IN,KS,KY,LA,MA,MD,ME,MI,MN,MO,MS,MT,NC,ND,NE,NH,NJ,NM,NV,NY,OH,OK,OR,PA,RI,SC,SD,TN,TX,UT,VA,VT,WA,WI,WV,WY"),
Shipping_State__c = ("?,NA,1,??,0,.")
)
)

Thanks!
Best Answer chosen by Chris Valko
Peter FribergPeter Friberg
Could you try something like this? 
NOT(
  CONTAINS("AL,AR,AZ,CA put needed states here comma separated", Shipping_State__c)
)
If the state in Shipping_State__c is not found in the CONTAINS string above, the check box will be checked = true.

All Answers

Peter FribergPeter Friberg
Could you try something like this? 
NOT(
  CONTAINS("AL,AR,AZ,CA put needed states here comma separated", Shipping_State__c)
)
If the state in Shipping_State__c is not found in the CONTAINS string above, the check box will be checked = true.
This was selected as the best answer
Akhil AnilAkhil Anil
Hi Chris,

I think CASE function is the best approach for this. Your formula would would be like this
  
CASE(Shipping_State__c,
"AL",1,
"AR",1,
"AZ",1,
"CA",1,
"CO",1,
"CT",1,
"DC",1,
"DE",1,
"FL",1,
"GA",1,
"IA",1,
"ID",1,
"IL",1,
"IN",1,
"KS",1,
"KY",1,
"LA",1,
"MA",1,
"MD",1,
"ME",1,
"MI",1,
"MN",1,
"MO",1,
"MS",1,
"MT",1,
"NC",1,
"ND",1,
"NE",1,
"NH",1,
"NJ",1,
"NM",1,
"NV",1,
"NY",1,
"OH",1,
"OK",1,
"OR",1,
"PA",1,
"RI",1,
"SC",1,
"SD",1,
"TN",1,
"TX",1,
"UT",1,
"VA",1,
"VT",1,
"WA",1,
"WI",1,
"WV",1,
"WY",1,
0) = 0

If the State doesn't contain any of the above mentioned states, it would return TRUE else FALSE.

Kindly mark it as an answer if that works.
Chris ValkoChris Valko
Thanks Peter & Akhil,

Both worked, however I wanted to do best practice and go with the smallest compiled code.

I wrote the following after asking my question but it was over 2,000 characters after being compiled. 
AND( 
Shipping_State__c != "AL", 
Shipping_State__c != "AR", 
Shipping_State__c != "AZ", 
Shipping_State__c != "CA", 

<ALL STATES LISTED, SHORTENED FOR BREVITY>

NOT(CONTAINS(Shipping_State__c, "?")), 
NOT(CONTAINS(Shipping_State__c, "NA")), 
NOT(CONTAINS(Shipping_State__c, "1")), 
NOT(CONTAINS(Shipping_State__c, "??")), 
NOT(CONTAINS(Shipping_State__c, "0")), 
NOT(CONTAINS(Shipping_State__c, ".")) 
)

Peters answer is the best as after being compiled is is only around 200 characters and I used the following 
NOT(
CONTAINS("AL,AR,AZ,CA,CO,CT,DC,DE,FL,GA,IA,ID,IL,IN,KS,KY,LA,MA,MD,ME,MI,MN,MO,MS,MT,NC,ND,NE,NH,NJ,NM,NV,NY,OH,OK,OR,PA,RI,SC,SD,TN,TX,UT,VA,VT,WA,WI,WV,WY,?,NA,1,??,0,.,..", Shipping_State__c)
)

Thanks again.
Peter FribergPeter Friberg
The logic is also probably the fastest because it only performs a single string scan and then just inverts the result.