+ Start a Discussion
Michael CallaghanMichael Callaghan 

Updating a Child Record

I have run into a myriad of issues with process builder and have decided to build a trigger.

LLC_BI__Loan__c is the 'Parent'
LLC_BI__Loan_Collateral2__c is the 'Child'

I wanted to update the Stage_Number__c field on the child record with a number based on the stage of the parent when a stage is changed.  

trigger LoanStageNumberUpdate on LLC_BI__Loan__c (after update) {
 
 for(LLC_BI__Loan__c loan: Trigger.new){
  LLC_BI__Loan__c oldloan = Trigger.oldMap.get(loan.Id);
  if(oldloan.LLC_BI__Stage__c != loan.LLC_BI__Stage__c)
   { 
    List<LLC_BI__Loan_Collateral2__c> collateral = [ SELECT Id, LLC_BI__Loan__c FROM LLC_BI__Loan_Collateral2__c WHERE LLC_BI__Loan__c = :loan.Id];
    List<LLC_BI__Loan_Collateral2__c> newcollateral = new List<LLC_BI__Loan_Collateral2__c>();
 
    for(LLC_BI__Loan_Collateral2__c col: collateral)
    {
        if(loan.LLC_BI__Stage__c == 'Pre-Underwriting'){col.Stage_Number__c = 1;}
            else if(loan.LLC_BI__Stage__c == 'Underwriting'){col.Stage_Number__c = 2;}
              else if(loan.LLC_BI__Stage__c == 'Credit Decision'){col.Stage_Number__c = 3;}   
                  else if(loan.LLC_BI__Stage__c == 'Client Decision'){col.Stage_Number__c = 4;}   
                      else if(loan.LLC_BI__Stage__c == 'Pre-Closing'){col.Stage_Number__c = 5;}   
                          else if(loan.LLC_BI__Stage__c == 'Doc Prep'){col.Stage_Number__c = 6;}  
                              else if(loan.LLC_BI__Stage__c == 'Doc Review'){col.Stage_Number__c = 7;}  
                                  else if(loan.LLC_BI__Stage__c == 'Post-Closing'){col.Stage_Number__c = 8;}   
                                      else if(loan.LLC_BI__Stage__c == 'Funding/Booking'){col.Stage_Number__c = 9;}   
    }
           {
       if (newcollateral.isEmpty() == false){
       update newcollateral;
      }
    }
  }
}
}
Best Answer chosen by Michael Callaghan
JeffreyStevensJeffreyStevens
well - first of all - you will get into trouble by putting the SOQL in the for loop of the Trigger.new.  You should never do a SOQL in a loop.  There is a limit of 100 SOQL's in any invocation.  And a trigger can be called with up-to 200 records in the trigger.new list.

But in this case - I think you should be able to just do one SOQL to get all of the details, and then as you're looping through those details, get the matching parent - and test the stage, then updating the detail stage number.

More of something like this...
trigger loanStageNumberUpdate ON LLC_BI__Loan__c (after update) {
	
	list<LLC_BI__Loan_Collateral2__c>	Collaterals = new list<LLC_BI__Loan_Collateral2__c>([SELECT id,LLC_BI__Loan__c,Stage_Number__c FROM LLC_BI__Loan_Collateral2__c WHERE LLC_BI__Loan__c IN :trigger.newmap.keyset()]);
	
      for(LLC_BI__Loan_Collateral2__c collateral :Collaterlas) {
		if(trigger.newmap.containsKey(collateral.LLC_BI__Loan__c)) {
			string stage = trigger.newmap.get(Acollateral.LLC_BI__Loan__c).LLC_BI__Stage__c;
			if(stage == 'Pre-Underwriting') 	collateral.Stage_Number__c	= 1;
			if(stage == 'Underwriting') 		collateral.Stage_Number__c	= 2;
			if(stage == 'Credit Decision') 		collateral.Stage_Number__c	= 3;
			if(stage == 'Client Decision') 		collateral.Stage_Number__c	= 4;
			if(stage == 'Pre-Closing') 			collateral.Stage_Number__c	= 5;
			if(stage == 'Doc Prep') 			collateral.Stage_Number__c	= 6;
			if(stage == 'Doc Review') 			collateral.Stage_Number__c	= 7;
			if(stage == 'Post-Closing') 		collateral.Stage_Number__c	= 8;
			if(stage == 'Funding/Booking') 		collateral.Stage_Number__c	= 9;
		}
	}
	
	if(collaterals.size()>0) {
		update collaterals;
	}

	
}




 

All Answers

R Z KhanR Z Khan
Hi Michael,

whats your question? I think you can also make Stage_Number__c a formula field. That way it will be evaluated every time you query the LLC_BI__Loan_Collateral2__c adn oyu dont need a trigger.

Now, if oyu want ot stick with the trigger, then make sure to remove the queries from the loop and update call as that will cause you to hit Apex limtis pretty fast
 
Michael CallaghanMichael Callaghan
Hi R Z Khan,

Stage_Number__c is a number(1,0) field on the LLC_BI__Loan_Collateral2__c and it is being used as part of a roll-up.

I thought by tieing it to stage changes it would no longer be recursive?

 
JeffreyStevensJeffreyStevens
well - first of all - you will get into trouble by putting the SOQL in the for loop of the Trigger.new.  You should never do a SOQL in a loop.  There is a limit of 100 SOQL's in any invocation.  And a trigger can be called with up-to 200 records in the trigger.new list.

But in this case - I think you should be able to just do one SOQL to get all of the details, and then as you're looping through those details, get the matching parent - and test the stage, then updating the detail stage number.

More of something like this...
trigger loanStageNumberUpdate ON LLC_BI__Loan__c (after update) {
	
	list<LLC_BI__Loan_Collateral2__c>	Collaterals = new list<LLC_BI__Loan_Collateral2__c>([SELECT id,LLC_BI__Loan__c,Stage_Number__c FROM LLC_BI__Loan_Collateral2__c WHERE LLC_BI__Loan__c IN :trigger.newmap.keyset()]);
	
      for(LLC_BI__Loan_Collateral2__c collateral :Collaterlas) {
		if(trigger.newmap.containsKey(collateral.LLC_BI__Loan__c)) {
			string stage = trigger.newmap.get(Acollateral.LLC_BI__Loan__c).LLC_BI__Stage__c;
			if(stage == 'Pre-Underwriting') 	collateral.Stage_Number__c	= 1;
			if(stage == 'Underwriting') 		collateral.Stage_Number__c	= 2;
			if(stage == 'Credit Decision') 		collateral.Stage_Number__c	= 3;
			if(stage == 'Client Decision') 		collateral.Stage_Number__c	= 4;
			if(stage == 'Pre-Closing') 			collateral.Stage_Number__c	= 5;
			if(stage == 'Doc Prep') 			collateral.Stage_Number__c	= 6;
			if(stage == 'Doc Review') 			collateral.Stage_Number__c	= 7;
			if(stage == 'Post-Closing') 		collateral.Stage_Number__c	= 8;
			if(stage == 'Funding/Booking') 		collateral.Stage_Number__c	= 9;
		}
	}
	
	if(collaterals.size()>0) {
		update collaterals;
	}

	
}




 
This was selected as the best answer
JeffreyStevensJeffreyStevens
but the other people are correct - I think you could do this with just a formula field on the detail record.
Michael CallaghanMichael Callaghan
Hi Jeffrey, thanks so much for this!

Thank you both for the very cogent points!
Mathew Andresen 5Mathew Andresen 5
Do you have a specific question?  Also, it's helpful if you paste as code, it makes it more readable
 
trigger LoanStageNumberUpdate on LLC_BI__Loan__c (before update) {

    set<id> collateralSet = new set<id>(); // use this to put the loans you want to query
    
    for(LLC_BI__Loan__c loan: Trigger.new){
    	LLC_BI__Loan__c oldloan = Trigger.old.get(loan.Id);    
        if(oldloan.LLC_BI__Stage__c != loan.LLC_BI__Stage__c) {
            collateralSet.add(load.id);
        }
    } // end the for loop
            
    List<LLC_BI__Loan_Collateral2__c> collateral = [ SELECT Id, LLC_BI__Loan__c FROM LLC_BI__Loan_Collateral2__c WHERE LLC_BI__Loan__c = In: collateralSet];
   
        List<LLC_BI__Loan_Collateral2__c> newcollateral = new List<LLC_BI__Loan_Collateral2__c>();
     
        for(LLC_BI__Loan_Collateral2__c col: collateral)
        {
            if(loan.LLC_BI__Stage__c == 'Pre-Underwriting'){col.Stage_Number__c = 1;}
                else if(loan.LLC_BI__Stage__c == 'Underwriting'){col.Stage_Number__c = 2;}
                  else if(loan.LLC_BI__Stage__c == 'Credit Decision'){col.Stage_Number__c = 3;}   
                      else if(loan.LLC_BI__Stage__c == 'Client Decision'){col.Stage_Number__c = 4;}   
                          else if(loan.LLC_BI__Stage__c == 'Pre-Closing'){col.Stage_Number__c = 5;}   
                              else if(loan.LLC_BI__Stage__c == 'Doc Prep'){col.Stage_Number__c = 6;}  
                                  else if(loan.LLC_BI__Stage__c == 'Doc Review'){col.Stage_Number__c = 7;}  
                                      else if(loan.LLC_BI__Stage__c == 'Post-Closing'){col.Stage_Number__c = 8;}   
                                          else if(loan.LLC_BI__Stage__c == 'Funding/Booking'){col.Stage_Number__c = 9;}   
        }
               {
           if (newcollateral.isEmpty() == false){
           update newcollateral;
          }
        }
      }
    }
}

Other things I noticed make sure you keep your quries out of loops.  Use a set or list to gather the information you want to query inside the loop and then do the query outside it.  

Also, this should probably be a "before update" query.  

I made some changes to the trigger on how I would set it up

 
Michael CallaghanMichael Callaghan
Thanks for your response Matthew!