+ Start a Discussion
BDeigsBDeigs 

Help with an IF(Begins statement with multiple values

I can't get this to work and was hoping someone can help...  Any thoughts?

 

IF(BEGINS( Phone , "(804)"), "Richmond",
IF(BEGINS(Phone , "(757)"),"Virginia Beach - Norfolk",
IF(BEGINS(Phone , "(704):(980)"),"Raleigh",
IF(BEGINS(Phone , "(919)"),"Charlotte",
IF(BEGINS(Phone , "(813):(727):(941)"),"Tampa",
IF(BEGINS(Phone , "(321):(407)"),"Orlando",
IF(BEGINS(Phone , "(904)"),"Jacksonville",
IF(BEGINS(Phone , "(770):(678):(404)"),"Atlanta",
IF(BEGINS(Phone , "(914):(631):(516):(347):(718):(917):(212):(646):(862):(973):(201):(551)"),"NYC - Newark",
IF(BEGINS(Phone , "(484):(610):(835):(215):(267):(856):(609):(302)"),"Philadelphia",
IF(BEGINS( Phone , "(781):(857):(339):(617)"),"Boston",
IF(BEGINS(Phone , "(716):(858)"),"Buffalo",
IF(BEGINS(Phone , "(860)"),"Hartford",
IF(BEGINS(Phone , "(401)"),"Providence",
IF(BEGINS(Phone , "(305):(786):(954):(754)"),"Miami",
IF(BEGINS(Phone , "(412):(878)"),"Pittsburgh",
IF(BEGINS(Phone , "(513)"),"Cincinnati",
IF(BEGINS(Phone , "(216):(440)"),"Cleveland",
IF(BEGINS(Phone , "(614)"),"Columbus",
IF(BEGINS(Phone , "(571):(703):(202):(240):(301)"),"DC",
IF(BEGINS(Phone , "(410):(443)"),"Baltimore",
"?-Non-metro")))))))))))))))))))))

Best Answer chosen by Admin (Salesforce Developers) 
jhurstjhurst

Not quite sure what the issue you are seeing is, but my guess is that you see a "?-Non-Metro" for any of the cities you are using the OR for.  If that is the case, it is because you are using an invalid OR designation.  By including the : in the quotes you are using them as a matching string.  So to get "Raleigh" the number would have to start with the string "(704):(980)".

 

I was able to get what I think you are looking for with the formula:

 

IF(BEGINS( Phone , "(804)"), "Richmond",
IF(BEGINS(Phone , "(757)"),"Virginia Beach - Norfolk",
IF(BEGINS(Phone , "(704)") || BEGINS(Phone, "(980)"),"Raleigh",
IF(BEGINS(Phone , "(919)"),"Charlotte",
IF(BEGINS(Phone , "(813)") || BEGINS(Phone , "(727)") || BEGINS(Phone , "(941)"),"Tampa",
IF(BEGINS(Phone , "(321)") || BEGINS(Phone , "(407)"),"Orlando",
IF(BEGINS(Phone , "(904)"),"Jacksonville",
IF(BEGINS(Phone , "(770)") || BEGINS(Phone , "(678)") || BEGINS(Phone , "(404)"),"Atlanta",
IF(BEGINS(Phone , "(914)") || BEGINS(Phone , "(631)") || BEGINS(Phone , "(516)") || BEGINS(Phone , "(347)") || BEGINS(Phone , "(718)") || BEGINS(Phone , "(917)") ||
     BEGINS(Phone , "(212)") || BEGINS(Phone , "(646)") || BEGINS(Phone , "(862)") || BEGINS(Phone , "(973)") || BEGINS(Phone , "(201)") || BEGINS(Phone , "(551)"),"NYC - Newark",
IF(BEGINS(Phone , "(484)") || BEGINS(Phone , "(610)") || BEGINS(Phone , "(835)") || BEGINS(Phone , "(215)") || BEGINS(Phone , "(267)") || BEGINS(Phone , "(856)") ||
     BEGINS(Phone , "(609)") || BEGINS(Phone , "(302)"),"Philadelphia",
IF(BEGINS(Phone , "(781)") || BEGINS(Phone , "(857)") || BEGINS(Phone , "(339)") || BEGINS(Phone , "(617)"),"Boston",
IF(BEGINS(Phone , "(716)") || BEGINS(Phone , "(858)"),"Buffalo",
IF(BEGINS(Phone , "(860)"),"Hartford",
IF(BEGINS(Phone , "(401)"),"Providence",
IF(BEGINS(Phone , "(305)") || BEGINS(Phone , "(786)") || BEGINS(Phone , "(954)") || BEGINS(Phone , "(754)"),"Miami",
IF(BEGINS(Phone , "(412)") || BEGINS(Phone , "(878)"),"Pittsburgh",
IF(BEGINS(Phone , "(513)"),"Cincinnati",
IF(BEGINS(Phone , "(216)") || BEGINS(Phone , "(440)"),"Cleveland",
IF(BEGINS(Phone , "(614)"),"Columbus",
IF(BEGINS(Phone , "(571)") || BEGINS(Phone , "(703)") || BEGINS(Phone , "(202)") || BEGINS(Phone , "(240)") || BEGINS(Phone , "(301)"),"DC",
IF(BEGINS(Phone , "(410)") || BEGINS(Phone , "(443)"),"Baltimore",
"?-Non-metro")))))))))))))))))))))

 Hope this helps.

Jay

All Answers

jhurstjhurst

Not quite sure what the issue you are seeing is, but my guess is that you see a "?-Non-Metro" for any of the cities you are using the OR for.  If that is the case, it is because you are using an invalid OR designation.  By including the : in the quotes you are using them as a matching string.  So to get "Raleigh" the number would have to start with the string "(704):(980)".

 

I was able to get what I think you are looking for with the formula:

 

IF(BEGINS( Phone , "(804)"), "Richmond",
IF(BEGINS(Phone , "(757)"),"Virginia Beach - Norfolk",
IF(BEGINS(Phone , "(704)") || BEGINS(Phone, "(980)"),"Raleigh",
IF(BEGINS(Phone , "(919)"),"Charlotte",
IF(BEGINS(Phone , "(813)") || BEGINS(Phone , "(727)") || BEGINS(Phone , "(941)"),"Tampa",
IF(BEGINS(Phone , "(321)") || BEGINS(Phone , "(407)"),"Orlando",
IF(BEGINS(Phone , "(904)"),"Jacksonville",
IF(BEGINS(Phone , "(770)") || BEGINS(Phone , "(678)") || BEGINS(Phone , "(404)"),"Atlanta",
IF(BEGINS(Phone , "(914)") || BEGINS(Phone , "(631)") || BEGINS(Phone , "(516)") || BEGINS(Phone , "(347)") || BEGINS(Phone , "(718)") || BEGINS(Phone , "(917)") ||
     BEGINS(Phone , "(212)") || BEGINS(Phone , "(646)") || BEGINS(Phone , "(862)") || BEGINS(Phone , "(973)") || BEGINS(Phone , "(201)") || BEGINS(Phone , "(551)"),"NYC - Newark",
IF(BEGINS(Phone , "(484)") || BEGINS(Phone , "(610)") || BEGINS(Phone , "(835)") || BEGINS(Phone , "(215)") || BEGINS(Phone , "(267)") || BEGINS(Phone , "(856)") ||
     BEGINS(Phone , "(609)") || BEGINS(Phone , "(302)"),"Philadelphia",
IF(BEGINS(Phone , "(781)") || BEGINS(Phone , "(857)") || BEGINS(Phone , "(339)") || BEGINS(Phone , "(617)"),"Boston",
IF(BEGINS(Phone , "(716)") || BEGINS(Phone , "(858)"),"Buffalo",
IF(BEGINS(Phone , "(860)"),"Hartford",
IF(BEGINS(Phone , "(401)"),"Providence",
IF(BEGINS(Phone , "(305)") || BEGINS(Phone , "(786)") || BEGINS(Phone , "(954)") || BEGINS(Phone , "(754)"),"Miami",
IF(BEGINS(Phone , "(412)") || BEGINS(Phone , "(878)"),"Pittsburgh",
IF(BEGINS(Phone , "(513)"),"Cincinnati",
IF(BEGINS(Phone , "(216)") || BEGINS(Phone , "(440)"),"Cleveland",
IF(BEGINS(Phone , "(614)"),"Columbus",
IF(BEGINS(Phone , "(571)") || BEGINS(Phone , "(703)") || BEGINS(Phone , "(202)") || BEGINS(Phone , "(240)") || BEGINS(Phone , "(301)"),"DC",
IF(BEGINS(Phone , "(410)") || BEGINS(Phone , "(443)"),"Baltimore",
"?-Non-metro")))))))))))))))))))))

 Hope this helps.

Jay

This was selected as the best answer
BDeigsBDeigs

Thank you so much Jay, that worked.  So || is an OR funciton, great to know!

 

Thanks again

jhurstjhurst

Yes.  You can also use the OR( test1, test2, test3, ...) format also

BDeigsBDeigs

Would this be the correct syntax to utilize OR?  Trying to limit the key strokes a bit here...

 

IF(BEGINS(Phone , OR ("(704)"),"(980)")),"Raleigh"
jhurstjhurst
Close...it would be:

IF(BEGINS(Phone , OR ("(704)","(980)")),"Raleigh", <else_condition>)
BDeigsBDeigs

For some reason I can't get this to work... I keep getting this error:  Expected Boolean, received text.

 

I am trying to add a few more IF statements but I am running into the 5000 character limit so figuring out how to limit the characters in these IF statements is key.  Thanks for any help / guidance.

jhurstjhurst

The error means that for a True/False conditin (such as an If Statenemt) instead of a true/false, you are trying to pass in a text value.  I would need to see the exact formula to determine what the issue is.

 

the character limit is something you will have to deal with.  Generally, if you are getting to a point where there is no way to reduce the characters, i recommend going with an Apex Trigger.  The idea would be to do an update in the trigger to set the field instead of having a formula field.  You will have a little more leeway with the format that way.

 

Hope this helps.

Jay

sa asawsa asaw
Sound good. I want to use this complete scirpt for my info program of music website (https://themusicinsight.com/14-best-acoustic-guitars-under-100/).