+ Start a Discussion
rosscorossco 

Trigger to update Parent record based on concatenation of child record

Hi

 

I want to write a trigger (before insert and after update) to set a single field on the Account record which is the concatentation of a field from many child records (from ERP_Account). 

 

For exmaple: if the ACCOUNT record has many ERP_Accounts with the value in field ACCOUNTNO as 'TEST1','TEST2','TEST3' respectively, then I want to set ACCOUNTNOS field in the Account record with the value 'TEST1, TEST2, TEST3,' 

 

Dont know where to start - any help would be greatly appreciated.

 

Thanks

Ross

petergascoynepetergascoyne

Hi Rossco,

 

I had a quick think, see below my idea of how to solve your problem.

 

 

trigger updateAccountNos on ERP_Account bulk(before insert, after update) {

    // Get ERP_Accounts related accounts
    
    // For Each Account, set Account.AccountNOS = ""; , for Each    
    // Account->EPE_Account set Account.AccountNOS += EPE_Account.AccountNo
    
    // update accounts

}

 Let me know if you have any issues.

 

Peter

 

WillyumWillyum

Were you able to get this to work?  I need to write a similar trigger and I have no clue how to write one.

rosscorossco

No, Im still struggling! Any code would be greatly appreciated

petergascoynepetergascoyne

Below is some code to put you in the right direction. I may have made some little mistakes, as I did it quickly in notepad. If ERP_Account is a custom object append '__c' and 

for(ERP_Account erpAccount : acc.ERP_Account__r){

 

trigger updateAccountNos on ERP_Account bulk(before insert, after update) {

    // Gets ERP_Accounts related accounts ids
    Set<Id> accountIds = new Set<Id>();
    for (ERP_Account erpAccount : Trigger.new){
        accountIds.add(erpAccount.AccountId);
    }
    
    // Gets related accounts with ERP_Accounts
    List<account> relatedAccounts =
        [Select a.AccountNOS
            (Select ACCOUNTNO From  ERP_Account)
            From Account a where id in: accountIds];
    Select (Select AccountId From Contacts) From Account a

    // Used to not add ',' on first erpAccount
    Boolean isFirst = true;
    
    // set each related Account's AccountNOS
    for(Account acc : relatedAccounts){
        acc.AccountNos = ''; // empty it for now
        // !acc.ERP_Accounts maybe wrong if custom object ERP_Account__r
        for(ERP_Account erpAccount : acc.ERP_Accounts){
            if(isFirst){
                acc.AccountNos += EPE_Account.AccountNo;
                isFirst = fasle;
            }
            else{
                acc.AccountNos += ',' + EPE_Account.AccountNo; 
            }
            
        }
    }
    
    update relatedAccounts;

}

 

Best of luck,

 

Peter

 

 

 

 

 

symantecAPsymantecAP

Hi Peter I am trying to mimic u r code for my requirement. My Parent Object is Opportunity and its child is Big Machine Quotes. It has to run in Batch . If status field on Big Machine Quote contains Unison then Opportunity Stage changes to Closed Won.

 

Kindly help. Here is my following code.

global class updateOpportunityStage implements Database.Batchable<sObject>,Schedulable{
global string query ;

global updateOpportunityStage(){

Query = 'Select Id,BigMachines__Status__c  from BigMachines__Quote__c' ;

}

global database.querylocator start(Database.BatchableContext BC){
return Database.getQueryLocator(query);    
}
    global void execute(SchedulableContext SC){
        updateOpportunityStage stg = new updateOpportunityStage();
        //String SCHEDULE_NAME = 'Process Quotes';
        //id cronid = System.schedule(SCHEDULE_NAME, '0 0 * * * ?', stg);
        
       String sch1 = '0 0 * * * ?';
System.schedule('Schedule Job1', sch1,stg);

  updateOpportunityStage stg2 = new updateOpportunityStage();
String sch2 = '0 15 * * * ?';
System.schedule('Schedule Job2', sch2, stg2);

        updateOpportunityStage stg3 = new updateOpportunityStage();
String sch3= '0 30 * * * ?';
System.schedule('Schedule Job3', sch3, stg3);

        updateOpportunityStage stg4 = new updateOpportunityStage();
String sch4 = '0 45 * * * ?';
System.schedule('Schedule Job4', sch4, stg4);

        
        
        
      //  System.abortJob(cronid);
//String cronStr =Datetime.now().addSeconds(10).format('s m H d M ? yyyy');
     //  System.schedule('Process Quotes', cronStr, stg);
        //System.abortJob(cronstr);
        
       /* String hour = String.valueOf(Datetime.now().hour());

String min = String.valueOf(Datetime.now().minute());

String ss = String.valueOf(Datetime.now().second());

String nextFireTime = ss + ' ' + min + ' ' + hour + ' * * ?';

system.schedule('Start me once', nextFireTime, stg); */
        database.executebatch(stg);
        
    }

global void execute(Database.BatchableContext BC, List<sObject> scope){

     
        Set<id> liOppIds = new Set<id>();
//List <Opportunity> oppList = new List<Opportunity>() ;
for(sObject s : scope){

BigMachines__Quote__c quote = (BigMachines__Quote__c)s;
System.debug('Adil'+quote);
if(quote.BigMachines__Status__c == '*unison*' && quote.BigMachines__Is_Primary__c == true)
liOppIds.add(quote.BigMachines__Opportunity__c);

}


//query all the opportunities in a single query
List<Opportunity> opp = new List<Opportunity>();
opp = [select id, StageName from Opportunity where id in :liOppIds and stagename != 'Closed Won'];
for ( Opportunity opps : opp)
{
opps.StageName = 'Closed Won' ;
}
//update all opportunities in a single DML
if(opp.size() > 0)
update opp;
 
    }
  global void finish(Database.BatchableContext BC){}  

}

 Any help is appreciated