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
Brianne WilsonBrianne Wilson 

Attach Child Record Based on Field Value (Find Record ID by Value and Attach)

Without getting too wordy:

I have a Zipcode object where the record name is a 5 digit zipcode. (Object Name = Zipcode__c)

I have a lookup field to the Zipcode object on the Account object (Field Name = Zipcode__c)


Goal If BillingPostalCode is not null, on insert/update I want to query the BillingPostalCode of the Account, search for the Zipcode record whose Name matches the Account BillingPostalCode, and attach. Note to query only on update if BillingPostalCode has been updated.

Ex: Account 123 is located in Zipcode 12345. Upon save, the Zipcode record called 12345 is located and attached to the Account in the Zipcode__c lookup field.

I know in theory how the Apex should look (create a list of Zipcode records where Name equals Account.BillingPostalCode), but having difficulty putting it into code. Any takers?
Best Answer chosen by Brianne Wilson
Brianne WilsonBrianne Wilson
Thanks James. I did have to make a couple tweaks due to one error (needed to Select the Name in the query) and since I need it for Insert as well. Here is what worked:
  
trigger UpdateZipcode on Account(before update, before insert){

    //Maintain a map relating billing postal codes / zipcodes to their respective accounts.
    Map<String,List<Account>> postcodeToAccountMap = new Map<String,List<Account>>();

    for(Account current : Trigger.new){
        //Check to see if billingpostalcode was updated as part of this update
        if(!String.isEmpty(current.BillingPostalCode) || current.BillingPostalCode != Trigger.oldMap.get(current.Id).BillingPostalCode){

            //Add the current account to the zip code map.
            List<Account> accts = postcodeToAccountMap.get(current.BillingPostalCode);
            if(accts == null){
                accts = new List<Account>();
                postcodeToAccountMap.put(current.BillingPostalCode,accts);
            }
            accts.add(current);      
        }
    }

    //Query for matching zip codes.  Note, the zipcodes would have to be an exact match.
    for(Zipcode__c zip : [Select Id, Name From Zipcode__c Where Name in : postcodeToAccountMap.keySet()]){
        //Loop through all accounts that match the zipcode, and then set the account's zipcode__c lookup field (presumably this is the correct api name)
        for(Account acct : postcodeToAccountMap.get(zip.Name)){
            acct.Zipcode__c = zip.Id;
        }
    }
}

 

All Answers

James LoghryJames Loghry

If you have the Process Builder Beta on your org (you can request it via a support ticket), then you could do this with flow.
 

Otherwise, you could write a trigger on Account similar to the following:

 

trigger UpdateZipcode on Account(before update){

    //Maintain a map relating billing postal codes / zipcodes to their respective accounts.
    Map<String,List<Account>> postcodeToAccountMap = new Map<String,List<Account>>();

    for(Account current : Trigger.new){
        //Check to see if billingpostalcode was updated as part of this update
        if(!String.isEmpty(current.BillingPostalCode) && current.BillingPostalCode != Trigger.oldMap.get(current.Id).BillingPostalCode){

            //Add the current account to the zip code map.
            List<Account> accts = postcodeToAccountMap.get(current.BillingPostalCode);
            if(accts == null){
                accts = new List<Account>();
                postcodeToAccountMap.put(current.BillingPostalCode,accts);
            }
            accts.add(current);      
        }
    }

    //Query for matching zip codes.  Note, the zipcodes would have to be an exact match.
    for(Zipcode__c zip : [Select Id From Zipcode__c Where Name in : postcodeToAccountMap.keySet()]){
        //Loop through all accounts that match the zipcode, and then set the account's zipcode__c lookup field (presumably this is the correct api name)
        for(Account acct : postcodeToAccountMap.get(zip.Name)){
            acct.Zipcode__c = zip.Id;
        }
    }
}

Note, you could simplify this potentially, by utilizing external ids for the zipcode instead.
Brianne WilsonBrianne Wilson
Thanks James. I did have to make a couple tweaks due to one error (needed to Select the Name in the query) and since I need it for Insert as well. Here is what worked:
  
trigger UpdateZipcode on Account(before update, before insert){

    //Maintain a map relating billing postal codes / zipcodes to their respective accounts.
    Map<String,List<Account>> postcodeToAccountMap = new Map<String,List<Account>>();

    for(Account current : Trigger.new){
        //Check to see if billingpostalcode was updated as part of this update
        if(!String.isEmpty(current.BillingPostalCode) || current.BillingPostalCode != Trigger.oldMap.get(current.Id).BillingPostalCode){

            //Add the current account to the zip code map.
            List<Account> accts = postcodeToAccountMap.get(current.BillingPostalCode);
            if(accts == null){
                accts = new List<Account>();
                postcodeToAccountMap.put(current.BillingPostalCode,accts);
            }
            accts.add(current);      
        }
    }

    //Query for matching zip codes.  Note, the zipcodes would have to be an exact match.
    for(Zipcode__c zip : [Select Id, Name From Zipcode__c Where Name in : postcodeToAccountMap.keySet()]){
        //Loop through all accounts that match the zipcode, and then set the account's zipcode__c lookup field (presumably this is the correct api name)
        for(Account acct : postcodeToAccountMap.get(zip.Name)){
            acct.Zipcode__c = zip.Id;
        }
    }
}

 
This was selected as the best answer