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
iSfdciSfdc 

Limitation of Formula fields

Hi all,
We need to create a Formula field based on the Postal Code which the User Enters.
Eg.If a User enters pincode=32700 ,the formula field must automatically populate the Region of the pincode.
 
This works only for limited Regions because we are not able to write the code for all the Pincodes.(Salesforce supports 5000 characters)
 
Code:
IF(
OR(AND(VALUE(Postal_Code__c)>=31000,VALUE(Postal_Code__c)<=31199),
AND(VALUE(Postal_Code__c)>=32700,VALUE(Postal_Code__c)<=32839),
AND(VALUE(Postal_Code__c)>=33000,VALUE(Postal_Code__c)<=33189),
AND(VALUE(Postal_Code__c)>=37000,VALUE(Postal_Code__c)<=37199),
AND(VALUE(Postal_Code__c)>=37400,VALUE(Postal_Code__c)<=37449),
AND(VALUE(Postal_Code__c)>=37500,VALUE(Postal_Code__c)<=37699),
AND(VALUE(Postal_Code__c)>=38000,VALUE(Postal_Code__c)<=38179),
AND(VALUE(Postal_Code__c)>=38200,VALUE(Postal_Code__c)<=38388),
AND(VALUE(Postal_Code__c)>=38400,VALUE(Postal_Code__c)<=38479),
AND(VALUE(Postal_Code__c)>=38600,VALUE(Postal_Code__c)<=38729),
AND(VALUE(Postal_Code__c)>=38840,VALUE(Postal_Code__c)<=38879)
),"Central Region","Not available"
)
 
Similarly we need to do it for Other Regions also
 
Any alternatives to implement this would be useful to us
 
Regards
Gani
Jeff TalbotJeff Talbot

Because of the formula size limitations, this is usually either very challenging or impossible to accomplish with a Salesforce formula. If it can work for your business process, try to break your regions down by State where you can, and only break it down by Zip where it's absolutely necessary. Additionally, consider narrowing the evaluation to the first three digits of the Zip, as this can cut down the size and number of evaluations considerably.

Here's a small scale example of what I'm referring to. This formula categorizes California into "North West" and "South West" regions, based on the first three digits of the zip code.

IF(CONTAINS("900:901:902:903:904:905:906:907:908:910:911:912:913:914:915:916:917:918:919:920:921:922:
923:924:925:926:927:928:930:931:932:933:934:935",LEFT(PostalCode,3)),"South West",
IF(CONTAINS("936:937:938:939:940:941:942:943:944:945:946:947:948:949:950:951:952:953:954:955:956:957:
958:959:960:961:962",LEFT(PostalCode,3)),"North West",
"Needs Zip to calculate Region"))
werewolfwerewolf
One alternative would be to make a custom object that maps zips to regions, and then make a before insert/before update Apex trigger that looks up the zip in that object and applies it to a field on the object you're working with.  That would probably also be much easier to maintain than formula fields.

In other news, note that the length of formula fields is now up to 4000 characters.
RickyGRickyG
It does sound like a scenario more appropriate for related objects.  Although it would not take place before you updated the object, you could use a workflow to set the value for the region code from the related object.

Hope this helps.