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
cambart14cambart14 

Finding Account ID if External ID Contains Value

Below is our code to find the account ID for inserted child records that have a unique identify (R12 Customer Number).  This works great if the R12 Customer Number is a unique match, but we are now consolidating accounts in SFDC and this may contain a string of customer numbers on the account level (example, R12 Customer Number = 'GEN12412;GEN12342;GEN12542' rather than R12 Customer Number = 'GEN12412').

I am trying to figure out how a child record being inserted with the R12 Customer Number = 'GEN12412' can find the right account ID if on the account the R12 Customer Number = 'GEN12412;GEN12342;GEN12542'.  The child record needs to trigger a contains search, but I am not certian how that would look in an Apex Trigger.

  
trigger UpdateCPInvoiceHeaderId on Customer_Order_Header__c (before insert) {
    //get the R12 Customer Numbers and store it in a set.
    set<String> acct = new set<String>();
    set<String> acct2 = new set<String>();
    for(Customer_Order_Header__c i: trigger.new){
        if(i.R12_Customer_Number__c != null){
        acct.add(i.R12_Customer_Number__c);
        }
        }
    
    //query the account records and get the associated IDs 
    map<string, Account> acctMap = new map<string, Account>();
    for(Account a:[SELECT Id, R12_Customer_Number__c FROM Account WHERE R12_Customer_Number__c IN: acct]){
        acctMap.put(a.R12_Customer_Number__c,a);
        }
    
    //update the account value based on the R12 Customer Number in the record    
    for(Customer_Order_Header__c i:trigger.new){
        if(i.R12_Customer_Number__c !=null && acctMap.containsKey(i.R12_Customer_Number__c)){
            i.Account__c=acctMap.get(i.R12_Customer_Number__c).Id;
        }
        
    }
    
}

 
Best Answer chosen by cambart14
goabhigogoabhigo
Instead of map you will have to use List. Because .containsKey() will match for exact key. 
trigger UpdateCPInvoiceHeaderId on Customer_Order_Header__c (before insert) {
    //get the R12 Customer Numbers and store it in a set.
    set<String> acct = new set<String>();
    set<String> acct2 = new set<String>();
    for(Customer_Order_Header__c i: trigger.new){
        if(i.R12_Customer_Number__c != null){
        acct.add(i.R12_Customer_Number__c);
        }
        }
    
    //query the account records and get the associated IDs 
    List<Account> acctList = [SELECT Id, R12_Customer_Number__c FROM Account WHERE R12_Customer_Number__c IN: acct];
      
    //update the account value based on the R12 Customer Number in the record    
    for(Customer_Order_Header__c coh : Trigger.new) {
        if(coh.R12_Customer_Number__c !=null) {
            for(Account acc: acctList) {
                  if(coh.R12_Customer_Number__c == acc.R12_Customer_Number__c) {
                       coh.Account__c=acc.Id;
                  }
            }
        }        
    }
}

Won't be as good as a map, but for these kind of situation map doesn't help. Assumption is that you don't have too many accounts. It is better to put a Limit in the SOQL query for safer side.

--
Abhi

If my answer helps to solve the issue/problem/doubt, please mark it as Best Answer; so that people who are stuck in the similar issue get benefitted. 
 

All Answers

goabhigogoabhigo
Instead of map you will have to use List. Because .containsKey() will match for exact key. 
trigger UpdateCPInvoiceHeaderId on Customer_Order_Header__c (before insert) {
    //get the R12 Customer Numbers and store it in a set.
    set<String> acct = new set<String>();
    set<String> acct2 = new set<String>();
    for(Customer_Order_Header__c i: trigger.new){
        if(i.R12_Customer_Number__c != null){
        acct.add(i.R12_Customer_Number__c);
        }
        }
    
    //query the account records and get the associated IDs 
    List<Account> acctList = [SELECT Id, R12_Customer_Number__c FROM Account WHERE R12_Customer_Number__c IN: acct];
      
    //update the account value based on the R12 Customer Number in the record    
    for(Customer_Order_Header__c coh : Trigger.new) {
        if(coh.R12_Customer_Number__c !=null) {
            for(Account acc: acctList) {
                  if(coh.R12_Customer_Number__c == acc.R12_Customer_Number__c) {
                       coh.Account__c=acc.Id;
                  }
            }
        }        
    }
}

Won't be as good as a map, but for these kind of situation map doesn't help. Assumption is that you don't have too many accounts. It is better to put a Limit in the SOQL query for safer side.

--
Abhi

If my answer helps to solve the issue/problem/doubt, please mark it as Best Answer; so that people who are stuck in the similar issue get benefitted. 
 
This was selected as the best answer
goabhigogoabhigo
Introduce a 'break' statement to make this code's performance much better.


for(Account acc: acctList) {
     if(coh.R12_Customer_Number__c == acc.R12_Customer_Number__c) {
          coh.Account__c=acc.Id;
          break;
     }
}
 
cambart14cambart14
Thanks goabhogo!  I had to make one change to your code above, rather than:

if(coh.R12_Customer_Number__c == acc.R12_Customer_Number__c) {

line 18 now reads:

if(acc.R12_Customer_Number__c.contains(coh.R12_Customer_Number__c)) {
  
trigger UpdateCPInvoiceHeaderId on Customer_Order_Header__c (before insert) {
    //get the R12 Customer Numbers and store it in a set.
    set<String> acct = new set<String>();
    set<String> acct2 = new set<String>();
    for(Customer_Order_Header__c i: trigger.new){
        if(i.R12_Customer_Number__c != null){
        acct.add(i.R12_Customer_Number__c);
        }
        }
    
    //query the account records and get the associated IDs 
    List<Account> acctList = [SELECT Id, R12_Customer_Number__c FROM Account WHERE R12_Customer_Number__c != null];
      
    //update the account value based on the R12 Customer Number in the record    
    for(Customer_Order_Header__c coh : Trigger.new) {
        if(coh.R12_Customer_Number__c !=null) {
            for(Account acc: acctList) {
                  if(acc.R12_Customer_Number__c.contains(coh.R12_Customer_Number__c)) {
                       coh.Account__c=acc.Id;
                       break;

                  }
            }
        }        
    }
}