+ Start a Discussion
tmbarrytmbarry 

Update an Account Record Field from a Child record, Based on an Account Update

I am trying update a field on a on an account record from a value on a child record.  Normally I would use a Roll Up summary field, but my Child record criteria field is a formula field and I cannot use a formula field as a roll up summary criteria.

 

My scenario is this.  On the account field I have a field :  "number of lives", a simple numeric field.  The then have a child object that have pricing values depending on the # of lives from the parent account.  Each Child record has a Min # of Lives Field and a Max # of Lives Field and if the Account.number of lives falls between the two, a formula field on the Pricing record returns a value of true.  

 

Whichever record has the true value, I want to return the Pricing__c.Total__c to the Account.Total_Price__c field.  Since the Number of lives updates on the Account record, I built the trigger on the account record:

 

trigger UpdatePricing on Account (before update) {
For (Account acct:Trigger.new){

String AcctId = acct.id;

List<Account> AccountToUpdate = new List<Account>();

For(Pricing__c pri: [Select id, Total__c from Pricing__c where Account__c =: acctid 
                                                         and Active__c = 'True' ]){

//AccountToUpdate.Total_Price__c = pri.total__c;
	Update AccountToUpdate;                         
}
}
}

But I can not for the life of me figure out how to make this work.  This is one of the error messages I am getting:

Error: Invalid Data. 
Review all error messages below to correct your data.
Apex trigger UpdatePricing caused an unexpected exception, contact your administrator: UpdatePricing: execution of BeforeUpdate caused by: System.DmlException: Update failed. First exception on row 0 with id 001i000000Crwx6AAB; first error: SELF_REFERENCE_FROM_TRIGGER, Object (id = 001i000000Crwx6) is currently in trigger UpdatePricing, therefore it cannot recursively update itself: []: Trigger.UpdatePricing: line 10, column 1

 

Any help would be greatly appreciate.  

 

Best Answer chosen by Admin (Salesforce Developers) 
sfdcfoxsfdcfox

You can't call a DML operation on a record from Trigger.new, which is effectively what you're trying to do. Nor do you need to do this, since simply assigning a new value will automatically patch the data into the database.

 

You're actually looking for an Aggregate-Query-Update pattern, which will look like the following in this case:

 

trigger UpdatePricing on Account (before update) {
  for(Pricing__c pri:[SELECT Id,Total__c,Account__c FROM Pricing__c WHERE Account__c IN :Trigger.new AND Active__c='TRUE']) {
    Trigger.newMap.get(pri.Account__c).Total_Price__c = pri.Total__c;
  }
}

Since Trigger.new already has the ID values we want, we have a shortened "aggregate" part of the AQU pattern, and we're also able to combine the query with the update, since we know that Trigger.new will cause those values to be committed to the database.

 

Note that if more than one pricing is active per account, the most recent pricing will win.

All Answers

hitesh90hitesh90

Hi,

 

Try below trigger code.

 

trigger UpdatePricing on Account (before update) {
	For (Account acct:Trigger.new){
		String AcctId = acct.id;		
		For(Pricing__c pri: [Select id, Total__c from Pricing__c where Account__c =: acctid and Active__c = 'True' ]){
			acct.Total_Price__c += pri.total__c;			
		}
	}
}

 

 

 

Important :
Hit Kudos if this provides you with useful information and if this is what you where looking for then please mark it as a solution for other benefits.
 
Thank You,
Hitesh Patel
SFDC Certified Developer & Administrator

Raj.ax1558Raj.ax1558

Hello, 

 

Here is few things that are wrong - 

> Trigger on update and u also write Udate statement. 

> Dml operation in for loop. 

 

I tried to resolve you trigger, try this 

 

trigger UpdatePricing on Account (before update) {
String AcctId;
For (Account acct:Trigger.new){
        AcctId = acct.id;
}
List<Account> AccountToUpdate = new List<Account>();
string getPriceTotal;
For(Pricing__c pri: [Select id, Total__c from Pricing__c where Account__c =: acctid  and Active__c = 'True' ]){

           getPriceTotal = pri.total__c;
}

for(Account acc: trigger.new)
{
          acc.Total_Price__c = getPriceTotal ;
}
}

 

Important :
Hit Kudos if this provides you with useful information and if this is what you where looking for then please mark it as a solution for other
benefits.

Thank You,
Raj Jha

sfdcfoxsfdcfox

You can't call a DML operation on a record from Trigger.new, which is effectively what you're trying to do. Nor do you need to do this, since simply assigning a new value will automatically patch the data into the database.

 

You're actually looking for an Aggregate-Query-Update pattern, which will look like the following in this case:

 

trigger UpdatePricing on Account (before update) {
  for(Pricing__c pri:[SELECT Id,Total__c,Account__c FROM Pricing__c WHERE Account__c IN :Trigger.new AND Active__c='TRUE']) {
    Trigger.newMap.get(pri.Account__c).Total_Price__c = pri.Total__c;
  }
}

Since Trigger.new already has the ID values we want, we have a shortened "aggregate" part of the AQU pattern, and we're also able to combine the query with the update, since we know that Trigger.new will cause those values to be committed to the database.

 

Note that if more than one pricing is active per account, the most recent pricing will win.

This was selected as the best answer
tmbarrytmbarry

sfdcfoc - I ran into a problem.  

 

I am doing this trigger "before update" because I am updating a field on the same oject as the trigger is based.  But here's the thing.  

 

The user needs to update a field on the Account Record.  Once that field is updated, a subsquent calculation is performed on the Pricing__c object.  It is the result of that subsquent calculation that i need returned back to the Account object.  By using your code and doing the "before upate"  the trigger is capturing the previous calculation value and not the one based on the new input on the Account record.  

 

Does that make sense? 

sfdcfoxsfdcfox

Your solution fails to work because the database can't see the changes until the commit occurs, which is too late for you to update the record. You also can't just do a recursive update, at least not trivially. The most straightforward solution is to use an asynchronous update.

 

Basically, your trigger does this:

 

trigger updateAccount on Account (after update) {
  if(System.isFuture()) {
    return;
  }
  updateAccountValues.usingRecordIds(Trigger.newMap.keySet());
}

You can then move your logic to the asynchronous class method:

 

public class updateAccountValues {
  @future public static void usingRecordIds(Set<Id> accountIDs) {
    Map<Id,Account> accounts = new Map<Id,Account>();
    for(Pricing__c pri:[SELECT Id,Total__c,Account__c FROM Pricing__c WHERE Account__c IN :accountIDs and Active__c='TRUE']) {
      accounts.put(pri.Account__c,new Account(Id=pri.Account__c,Total_Price__c=pri.Total__c));
    }
    update accounts;
  }
}

The System.isFuture bit prevents asychronous calls (results in error) in the trigger, while the future method calls the update after all the data has already been committed, satisfying both the needs to wait until the data is committed and still being able to update the record.

tmbarrytmbarry
Thanks for you help sdffox.

I am getting and error the the Class:

Error: Compile Error: DML requires SObject or SObject list type: MAP<Id,Account> at line 7 column 5

That's the "update accounts;" line. Any thoughts?
sfdcfoxsfdcfox
Sorry, it should have been update accounts.values(); instead of update accounts; For some reason, I do that quite frequently.