function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

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

Trying to create an IF(Contains( formula. With Zipcodes

Hi I am trying to create a formula that would have several Zip Codes, and if the zipcode is in the formula it would be indicated in the field. 

 

IE: If Zip Code Field contains 01234,12345,34561,08043,66236,52432 display Extended area, or if zip contains 55555,78948,66589,88896 Display Local Area.

 

The End Result would be for the field to display "Extended Area" or "Local Area" depending on the zip code.  If the zipcode doesn't match either return "null".

 

Any thoughts?

 

Thanks

goabhigogoabhigo

You can either make use of IF(CONTAINS( TEXT(ZipCode__c), '01234') || CONTAINS( TEXT(ZipCode__c), '12345')....

OR

You can use CASE.

 

But I am afraid that you might hit the character limit. Let me know if you need more help.
Jeff MayJeff May

Since ZIPs are numbered geographically, you might be able to use LEFT(ZIP, 2) to make the formula smaller, and handle more zip codes.  CASE(LEFT(ZIP, 2), '01', 'Local', '02', Local, 'Extended').

 

or even shorter:

 

IF(VALUE(LEFT(ZIP, 2)) < 10, 'Local', 'Extended')
BdoggBdogg
Thanks JeffM, unfortunately I need to use the whole zipcode, the first characters don't help in my situation. The goal is to indicate where my shippers charge additional shipping, and that is based off the distance from their hubs so 92008 may not be extended area, but 20009 would be.
BdoggBdogg

Thanks for the suggestions, I think the If Contains won't work... there are about 10,000 zip codes so that would be an incredible long formula.  I am not farmiliar with the CASE you mentioned, would you be able to provide an example?  If I run into a issue with the character limit, I may break it down and create several fields covering different ranges of zipcodes, but that would not be my preferred route. 
Jeff MayJeff May

The formula will be too long -- and you would have to break it into several pieces, but then you can't combine those pieces into a single formula field because SFDC will combine the formulas that contribute and you will be over the limit.  With 10,000 zip codes, I think you'll need to look at a programmatic option.  Triggers can do it, based off of a 'Zip Code' table of some sort.
BdoggBdogg
I was afraid of that, as I am not familiar with Triggers..
Shannon HaleShannon Hale

Using CASE() would look something like this:

 

CASE(
  LEFT( BillingPostalCode, 5 ),
  "01234", "Extended Area",
  "12345", "Extended Area",
  ...
  "88896", "Local Area",
  null
)

 

However, if you have 10,000 zip codes you're probably not going to be able to do this with a formula. It's going to exceed the compile size limit (the size of the query that gets generated in the database, not the length of the formula you typed). Both CASE() and CONTAINS() compile to a similar size.

 

You could create a custom object for the Zip Code that has the Extended/Local Area status as a field on that object, and then include that value as a simple cross-object reference. But then you wouldn't be able to use the zip code included in the standard address field.

 

I think you might need to look at Apex for this.
BdoggBdogg
Can anyone point me in a the direction of an idiots guide to Triggers?
Jeff MayJeff May

Here's at least 1 resource:  http://www.crmverse.com/write-your-first-salesforce-com-apex-trigger-overview/