Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Atomic1Atomic1 

Syntex Error

I'm trying to create a formula field that looks at the billing state and country fields and the industry (K-12) and then populates the field with either East, West or Other.  I keep getting "Syntex Error".  Here's the formula.  Any ideas?
 

IF (LEN (BillingState)=0, "None",

IF(AND(CONTAINS("BC:AB:SK:YT:NT:NU:HI:AK:WA:OR:CA:ID:NV:MT:UT:AZ:WY:CO:NM", BillingState),CONTAINS("USA:Canada",BillingCountry),CONTAINS("K12", Industry)),"West",

IF(AND(CONTAINS("TX:MN:IA:MO:ND:SD:WI:NE:IL:KS:OK",BillingState),CONTAINS(“USA:Canada",BillingCountry)CONTAINS("K12", Industry)),"West",

IF(AND(

CONTAINS("MI:IN:OH:PA:NY:MD:DE:NJ:CT:RI:MA:VT:NH:ME:DC:ON:QC:NB:NS:PE:NL:MB", BillingState),CONTAINS("USA:Canada",BillingCountry)CONTAINS("K12", Industry)),"East",

IF(AND(

CONTAINS("KY:WV:VA:TN:NC:AR:LA:MS:AL:GA:SC:FL", BillingState),CONTAINS("USA:Canada",BillingCountry)CONTAINS("K12", Industry)),"East", "International")))))

SteveBowerSteveBower

Fast visual scan, looks like you're missing some commas.  Before the "Contains" for the K12's.   -Steve.
Atomic1Atomic1

IF (LEN (BillingState)=0, "None",

IF(AND(CONTAINS("BC:AB:SK:YT:NT:NU:HI:AK:WA:OR:CA:ID:NV:MT:UT:AZ:WY:CO:NM", BillingState),CONTAINS("USA:Canada",BillingCountry),CONTAINS("K12", Industry)),"West",

IF(AND(CONTAINS("TX:MN:IA:MO:ND:SD:WI:NE:IL:KS:OK",BillingState),CONTAINS(“USA:Canada",BillingCountry),CONTAINS("K12", Industry)),"West",

IF(AND(

CONTAINS("MI:IN:OH:PA:NY:MD:DE:NJ:CT:RI:MA:VT:NH:ME:DC:ON:QC:NB:NS:PE:NL:MB", BillingState),CONTAINS("USA:Canada",BillingCountry,CONTAINS("K12", Industry)),"East",

IF(AND(

CONTAINS("KY:WV:VA:TN:NC:AR:LA:MS:AL:GA:SC:FL", BillingState),CONTAINS("USA:Canada",BillingCountry),CONTAINS("K12", Industry)),"East", "International")))))

 

Sorry, I had fixed them in my formula but not in the one I copied to Word when I cancelled out of the formula.  Still get the error. 

 

BTW - how do I get this to post without the happy faces?
Atomic1Atomic1
First of all, thanks to Chris from SF for getting me to this formula:
 
IF(LEN (BillingState)=0, "N/A",
IF(AND(CONTAINS("BC:AB:SK:YT:NT:NU:HI:AK:WA:OR:CA:ID:NV:MT:UT:AZ:WY:CO:NM",BillingState),
CONTAINS("USA:Canada",BillingCountry),ISPICKVAL(Industry,"K-12")
), "West",
IF(AND(CONTAINS("TX:MN:IA:MO:ND:SD:WI:NE:IL:KS:OK", BillingState), CONTAINS("USA:Canada",BillingCountry),
ISPICKVAL(Industry,"K-12")), "West",
IF(AND(CONTAINS("MI:IN:OH:PA:NY:MD:DE:NJ:CT:RI:MA:VT:NH:ME:DC:ON:QC:NB:NS:PE:NL:MB:AE:AP", BillingState), CONTAINS("USA:Canada",BillingCountry),
ISPICKVAL(Industry,"K-12")),"East",
IF(AND(CONTAINS("KY:WV:VA:TN:NC:AR:LA:MS:AL:GA:SC:FL", BillingState), CONTAINS("USA:Canada",BillingCountry),
ISPICKVAL(Industry,"K-12")),"East","Other")))))
 
I need to use ISPICKVAL.  Now, I've tried several ways to put in multiple pick values and I don't get errors, what I get is everything moving to "Other".  I've done ISPICKVAL(Industry, "K-12:K-12 Consortium") which returns all states to Other and ISPICKVAL(Industry, "K-12, K-12 Consortium") which also returns all to "Other".  Since the formula doesn't error out, what is missing that is causing it to go to "Other"?