+ Start a Discussion
Jenna HildebrandJenna Hildebrand 

Apex Code to Function Like a VLookup

Hello,

I'm working to build a simple, custom territory management solution for my org; other territory management tools don't quite do the trick. Basically, I need to assign accounts to territories based on the zip code value in the account's billing address.

First, some object details: I have a custom object for Territories and a custom object for Zip Codes. Each Zip Code record has a lookup field for a Territory record, which basically signifies that the zip code "belongs" to the related territory. I can go to each Territory record and see a related list of the associated Zip Code records. I also have a Territory lookup field on the account record. (I was told that I may need a Zip Code lookup on the account as well to serve as a junction, but I'm not sure about that one yet...)

Now, what I want to accomplish: I need to create an apex trigger that says, "if the value in the account's billing zip code matches the name of any existing Zip Code record, then 'Territory' (on the account) equals the Territory related to that matched Zip Code record."

I was hoping to accomplish this via process builder, but Salesforce Support informed me that this won't be possible... which brings me here. Can anyone help me get started on coding an apex trigger that can accomplish what I stated above? I'd appreciate any guidance you can provide.

Thank you!
bainesybainesy
This is very basic and I don't know the names of your objects but this should get you going in the right direction:
trigger AssociateAccountToTerritory on Account (before insert, before update) {

	Set<string> zipCodes = new Set<string>();

	for (Account account : Trigger.new) {
		string zipCode = account.BillingPostalCode;

		if (!zipCodes.contains(zipCode)) zipCodes.add(zipCode);
	}

	List<ZipCode__c> customZipCodes = new List<ZipCode__c>();

	if (zipCode.size() > 0) {
		customZipCodes = [Select Id, Territory_Id__c from ZipCode__c where Name = :zipCodes];
	}

	Map<String,Id> zipCodeTerritoryIdMap = new Map<String,Id>();
	for (ZipCode__c customZipCode : customZipCodes) {
		zipCodeTerritoryIdMap.put(customZipCode.Name, customZipCode.Territory_Id__c);
	}

	for (Account account : Trigger.New) {
		account.Territory_Id__c = zipCodeTerritoryIdMap.get(account.BillingPostalCode);
	}

}

 
Amol Salve 14Amol Salve 14
Helyli Jenna,

Used following code to fullfill your requirent,

trigger AssociateAccountToTerritory on Account (before insert, before update) {

    Set<string> zipCodeSet = new Set<string>();

    for (Account account : Trigger.new) {
        
        if(account.BillingPostalCode != null){
            
            zipCodeSet.add(account.BillingPostalCode);
        }        
    }
    List<ZipCode__c> customZipCodeList = new List<ZipCode__c>();
    map<string, Id> zipVsTerritoryMap = new map<string, Id>();
    if (zipCodeSet.size() > 0) {
        
        customZipCodeList = [Select Id, Name, TerritoryId__c from ZipCode__c where Name IN :zipCodeSet];
        if(customZipCodeList.size()>0){
            
            for(ZipCode__c zipObj : zipVsTerritoryMap){
                
                zipVsTerritoryMap.put(zipObj.Name, zipObj.TerritoryId__c);
            }            
        }
        for (Account accountObj : Trigger.New) {
            
            if(zipVsTerritoryMap.containsKey(account.BillingPostalCode)){
                
                accountObj.Territory_Id__c = zipVsTerritoryMap.get(accountObj.BillingPostalCode);
            }        
        }
    }
}

Thank you,
Amol Salve
Salesforce Develpoer
Jenna HildebrandJenna Hildebrand
@bainesy and @Amol Salve 14 - Thank you so much for the help!

I used both of your suggestions to help better my understanding of apex. I ended up creating a class to house the logic and a trigger to call the method. Everything seems to be working when I test manually in Sandbox, but I'm having some trouble with the test class I created (I wanted it to work here, too, for peace of mind). Could anyone check it out and see how I need to handle the System.assertEquals logic? That is what's throwing the error.

Below is my TerritoryManagement class:
public class TerritoryManagement {

    public static void assignTerritoryToAccount(Account[] account) {
    
        // defines new set (unordered collection of elements) to place zip codes
        Set<string> zipCodeSet = new Set<string>();
        
        // for Trigger.new accounts, add account zip code to set if not blank
        for (Account a :account) {
        
            if(a.BillingPostalCode != null){

                zipCodeSet.add(a.BillingPostalCode);
            }
        
        }

        // defines list of zip codes from Zip Code object
        List<Zip_Code__c> customZipCodeList = new List<Zip_Code__c>();

        // defines zip code map to populate later (name, id)
        map<string, Id> zipVsTerritoryMap = new map<string, Id>();

        // if there are values in the zip code set...
        if (zipCodeSet.size() > 0) {

            // grabs zip code id, zip code name, and territory from Zip Code object if name matches zipCodeSet (Trigger.new account zips)
            customZipCodeList = [SELECT Id, Name, Territory__c FROM Zip_Code__c WHERE Name IN :zipCodeSet];

            // if matches are found in Zip Code object...
            if(customZipCodeList.size() > 0) {

                // for each match, create key-value pair of zip code name/territory id
                for(Zip_Code__c zipObj :customZipCodeList) {
                
                    zipVsTerritoryMap.put(zipObj.Name, zipObj.Territory__c);

                }
            }

            // for the new accounts being created...
            for (Account a :account) {

                // if the zip code map contains the account zip code...
                if(zipVsTerritoryMap.containsKey(a.BillingPostalCode)){
                
                    // then assign the appropriate territory id 
                    a.Territory_New__c = zipVsTerritoryMap.get(a.BillingPostalCode);
                
                }
            }
        }
    }
}
Below is my TerritoryManagementTrigger:
trigger TerritoryManagementTrigger on Account (before insert, before update) {

    Account[] account = Trigger.new;
    
    TerritoryManagement.assignTerritoryToAccount(account);

}
Below is my TerritoryManagementTest:
@isTest
private class TerritoryManagementTest {

    static testMethod void validateTerritoryManagement() {
    
    Account a = new Account(Name='TM Test', BillingState='LA', BillingPostalCode='12345');
    System.debug('Territory = ' + a.Territory_New__c);
    
    insert a;
    
    a = [SELECT Territory_New__c FROM Account WHERE Id =:a.Id];
    System.debug('Territory after trigger fired: ' + a.Territory_New__c);
        
    System.assertEquals('Test Territory 1', a.Territory_New__c);
    
    }

}
The specific error I'm getting is System.AssertException: Assertion Failed: Expected: Test Territory 1, Actual: null

I don't think 'Test Territory 1'  is at all correct. What value should go here to match the expected value? Territory_New__c is a lookup, so should it refer to the ID somehow?

Thanks again!
Jenna HildebrandJenna Hildebrand
Also, I don't quite understand why Actual: null is the result of my test class when I've confirmed that the trigger works when I test records manually.
bainesybainesy
a.Territory_New__c will = null on line 07 unless you requery the database to get the value. You can avoid doing that buy changing line 06 to:
Account a = new Account(Name='TM Test', BillingState='LA', BillingPostalCode='12345', Territory_New__c = null);

You will then have a refernce to Territory_New__c so you can then check the value. Ideally what you are trying to do is validate the name of the Account territory matches what was automatically inserted via your trigger. You know the name of the Territory should be '12345' so use this as a different approach to check the value:
 
@isTest
private class TerritoryManagementTest {

    static testMethod void validateTerritoryManagement() {
    
    Account a = new Account(Name='TM Test', BillingState='LA', BillingPostalCode='12345', Territory_New__c = null);
    System.debug('Territory = ' + a.Territory_New__c);
    
    insert a;

    zipCodeId = a.Territory_New__c; //this should have a value now. If it doesn't, re-query the database for it as you did below
   //a = [SELECT Territory_New__c FROM Account WHERE Id =:a.Id];

    Zip_Code__c zipCode = [Select Id, Name from Zip_Code__c where Id = :zipCodeId];
    
    
    System.debug('Territory after trigger fired: ' + a.Territory_New__c);
        
    System.assertEquals(a.BillingPostalCode, zipCode.Name);
    
    }

}

 
Jenna HildebrandJenna Hildebrand
I'm receiving a compile error saying that the variable zipCodeId doesn't exist. But, I did want to clarify that the name of the territory being assigned to the account should actually be the lookup value in the Territory__c field on the Zip Code object, not the zip code name. The zip code is simply used to find a match between the account BillingPostalCode and Zip Code records, then the territory looked up by that zip code record is assigned to the account.

I feel like the below is getting close to what I'm looking for, but I'm receiving the following error when running the test: System.QueryException: List has no rows for assignment to SObject at line 13, column 1. Is this because the Zip_Code__c query isn't returning anything?
@isTest
private class TerritoryManagementTest {

    static testMethod void validateTerritoryManagement() {
    
    Account a = new Account(Name='TM Test', BillingState='LA', BillingPostalCode='12345', Territory_New__c = null);
    System.debug('Territory = ' + a.Territory_New__c);
    
    insert a;
    
    a = [SELECT Territory_New__c, BillingPostalCode FROM Account WHERE Id =:a.Id];
    
    Zip_Code__c zipCode = [Select Name, Territory__c from Zip_Code__c where Name =:a.BillingPostalCode];
    
    System.debug('Territory after trigger fired: ' + a.Territory_New__c);
        
    System.assertEquals(zipCode.Territory__c, a.Territory_New__c);
    
    }

}
Jenna HildebrandJenna Hildebrand
Hello,

The TerritoryManagement class and trigger are working well in my Sandbox, but I need to make one small modification before rolling it to Production. How can I specify to assign the territory to the account when the account's billing zip code contains the name of one of the Zip Code records? For example, if the Zip Code record is named '12345' and the account's billing zip code is '12345-1234' (the full format), then the territory related to that Zip Code record should still be assigned to the account. Right now, these values would not be recognized as a match.

Here's the section of the class that assigns the territory to the account:
for (Account a :account) {

   // if the zip code map contains the account zip code...
   if(zipVsTerritoryMap.containsKey(a.BillingPostalCode)) {
                
        // then assign the appropriate territory id 
        a.Territory_New__c = zipVsTerritoryMap.get(a.BillingPostalCode);
   }
   else {
                    
        a.Territory_New__c = 'a1mQ0000005gxNh';
                    
   }
}
Thank you!
bainesybainesy
for (Account a :account) {
   String zipCodeFull = a.BillingPostalCode;
   String zipCodeLeftFive = zipCodeFull.left(5);

   // if the zip code map contains the account zip code...
   if(zipVsTerritoryMap.containsKey(zipCodeLeftFive)) {
                
        // then assign the appropriate territory id 
        a.Territory_New__c = zipVsTerritoryMap.get(zipCodeLeftFive);
   }
   else {
                    
        a.Territory_New__c = 'a1mQ0000005gxNh';
                    
   }
}

Use the left function to get the 5 leftmost characters.
Jenna HildebrandJenna Hildebrand
Thank you so much!

I'm using Data Loader to mass-update some records to test this out. I received the following error:

Sandbox

Apex script unhandled trigger exception by user/organization: 005G0000008GGd4/00DQ000000EeWPG
Source organization: 00DA0000000JfyF (null)
TerritoryManagementTrigger: execution of BeforeUpdate

caused by: System.NullPointerException: Attempt to de-reference a null object

()

Any idea why this could be happening?
Jenna HildebrandJenna Hildebrand
To clarify, the error is only occurring with the new code using the .left function. When I use the original code, I can successfully update records via Data Loader.
bainesybainesy
for (Account a :account) {
   String zipCodeFull = a.BillingPostalCode;
   String zipCodeToLookup;

   if (zipCodeFull != null && zipCodeFull.length() > 5) 
   {
      zipCodeToLookup = zipCodeFull.left(5);
   }
   else {
      zipCodeToLookup = zipCodeFull;
   }

   // if the zip code map contains the account zip code...
   if(zipVsTerritoryMap.containsKey(zipCodeToLookup)) {
                
        // then assign the appropriate territory id 
        a.Territory_New__c = zipVsTerritoryMap.get(zipCodeToLookup);
   }
   else {
                    
        a.Territory_New__c = 'a1mQ0000005gxNh';
                    
   }
}

Sorry I gave you the lazy man's version.
Try this. There's a hundred ways to do this, so this is just one example.
 
Jenna HildebrandJenna Hildebrand
@bainesy, thank you so much for your help! I really appreciate it.