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
Chris EChris E 

Need to shorten CASE formula that usesmultiple options with the same result

I'm trying to use a CASE field to look up the first two characters of a postcode and output some broad regions that we use to report on data. So far i have this:
CASE( UPPER(LEFT(BillingPostalCode,2)),
'PL', "South West England",
'PO', "South West England"

/*TONNES OF OTHERS */
        "Unfound"
 )

Obviously once i get all the UK postcodes in there, it exceeds the character limit, plus it just looks horribly clumsy. I've tried things like
'PL' : 'PO', "South West England"  
to shorten the formula, but i can't seem to make any of them work. Is there any more elegant way of pointing those multiple options at a single result? 

Apologies if this has been tackled elsewhere, i had a good look round the forums and couldn't find anything that seemed to solve it.
John PipkinJohn Pipkin
Chris, 

We have encountered this issue before. Whenever there are a ton of postal codes, formula fields just don't cut it. It's also not very scalable. I would suggest creating a Custom Setting and having a trigger fill out the region field. It could look a little like this:

Custom Setting Record:
Name = 'PL'
Region__c = 'South West England'

Trigger: 
trigger AccountBefore on Case (before insert, before update){

    //this stores all cust setting records in a map
	Map<String,MyCustomSetting__c> custSet = MyCustomSetting__c.getAll();
	
	for(Account a :Trigger.new){
		Account oldA = null;
		Boolean findCS = false;
		String pc = '';

		if(Trigger.isUpdate)
			oldA = Trigger.oldMap.get(a.Id);

		if(Trigger.isInsert && a.BillingPostalCode != null)
			findCS = true;
		
		if(Trigger.isUpdate){
			if(a.BillingPostalCode != oldA.BillingPostalCode && a.BillingPostalCode != null)
				findCS = true;
		}

		if(findCS){
			pc = a.BillingPostalCode.substring(0,2).toUpperCase();
			MyCustomSetting__c cs = custSet.get(pc); //find custom setting record

			if(cs != null)// only run logic if a record is found
				a.Region__c = cs.Region__c;//update your region field based on custom setting
		}
	}
}

This way, if you add regions, or change the name of the regions, you won't have to edit the trigger or any formula field. 

Hope that helps. Good luck
Chris EChris E
Cheers John. I've never written a trigger (hence the enormous formula) but I can give it a shot. Thank god for Sandbox!
John PipkinJohn Pipkin
Give it a try. Let me know if you run into any problems you can't figure out. Good luck!
David BermanDavid Berman
If this is to be used ultimately for reports, have you considered tackling this via a report by creating a formula field to get the first two characters of the postal code and then using bucketing on those 2-letter codes?