+ Start a Discussion
alxalx 

Query Trigger Help

Hey everyone,

 

This trigger query is killing me.

 

I am writing a trigger to sum up custom field Scores in Contact.

 

I want to display the sum in Account.

 

I want to query the database for all the Scores for each Contact in an Account.

 

I guess my fundamental understanding of apex code sucks, nothing i'm doing seems to even compile.

Here's my code:

 

 

trigger sumContactScore on Account (after insert, after update) {
	
	Set<Id> accIDs = new Set<Id>();
	for(Account a: Trigger.new){
		accIDs.add(a.Id);
	}
	
	for(Account a : Trigger.new){
		
		Double sum = 0;

		List<Contact> scoreList = new List<Contact>();

		scoreList = [SELECT LS_Implicit_Contact_Score__c FROM Contact WHERE Email != null and Contact.AccountId in :accIDs];
		
		for(Contact i : scoreList){
			sum += i.LS_Implicit_Contact_Score__c;
		}
		
		a.Sum_of_Implicit_Behavior_Scores__c = sum;	
	}

}

 

 

 

Help.

dwwrightdwwright

Try this out...

 

 

trigger sumContactScore on Account (before insert, before update) {
	
	Set<Id> accIDs = new Set<Id>();
	for(Account a: Trigger.new){
		accIDs.add(a.Id);
	}
        //Get all contacts that are associated with the accounts in this trigger
        List<Contact> scoreList = [SELECT LS_Implicit_Contact_Score__c, Email FROM Contact WHERE AccountId in :accIDs]
	
	for(Account a : Trigger.new){
		
		for(Contact i : scoreList){
                        //If the Email field is not null and this contact belongs to this account
                        if(i.Email != null && i.AccountId == a.ID)
			a.Sum_of_Implicit_Behavior_Scores__c += i.LS_Implicit_Contact_Score__c;
		}
		
			
	}

}

 

 

alxalx

dwwright,

 

Thanks for the response. I put in your code and i got the following exception:

 

Apex trigger sumContactScore caused an unexpected exception, contact your administrator: sumContactScore: execution of BeforeUpdate caused by: System.SObjectException: SObject row was retrieved via SOQL without querying the requested field: Contact.AccountId: Trigger.sumContactScore: line 14, column 47

 

Could you explain why you changed the insert and update to before?

aalbertaalbert

Here is some psuedo-code that should get you close. A few comments:

(a) you don't need the 'before insert' since you won't have any Contacts related to a newly created Account

(b) you can use SOQL relationships to get Account and Contacts in a single query.

(c) I use a 'before update' trigger instead of 'after' so you can avoid doing an additional update DML statement to apply the changes.

(d) this logic will only fire after an account is updated. what if a new score record is created therefore changing the rollup totals? you might need another trigger on the score records to capture that event.

 

 

trigger sumContactScore on Account (before update) {
	
	List<Account> accounts = [select id, name, (SELECT Id, LS_Implicit_Contact_Score__c, AccountId FROM Contacts WHERE Email != null ) from Account where id in :trigger.new];


	for(Account a : accounts){
		
		Double sum = 0;

		
		for(Contact i : a.Contacts){
			sum += i.LS_Implicit_Contact_Score__c;
		}
		
		a.Sum_of_Implicit_Behavior_Scores__c = sum;	
	}

}

 

 

alxalx

aalbert,

 

Thank you. You bring up an excellent point.

 

I think i'm just going to batch update the accounts on a scheduled apex task instead.

 

As for your code, it throws a null pointer at line 17 the sum += line..

aalbertaalbert

Yep, my bad. Here is a revised version (I didn't query for the sum field in my soql statement):

 

 

trigger sumContactScore on Account (before update) {
	
	List<Account> accounts = [select id, Sum_of_Implicit_Behavior_Scores__c , name, (SELECT Id, LS_Implicit_Contact_Score__c, AccountId FROM Contacts WHERE Email != null ) from Account where id in :trigger.new];


	for(Account a : accounts){
		
		Double sum = 0;

		
		for(Contact i : a.Contacts){
			sum += i.LS_Implicit_Contact_Score__c;
		}
		
		a.Sum_of_Implicit_Behavior_Scores__c = sum;	
	}

}

 

 

alxalx

aalbert,

 

Still getting the following error:

 

Apex trigger sumContactScore caused an unexpected exception, contact your administrator: sumContactScore: execution of BeforeUpdate caused by: System.NullPointerException: Attempt to de-reference a null object: Trigger.sumContactScore: line 12, column 11

 

Do you think i should shift gears and just write an apex scheduled batch update?

aalbertaalbert

You might need to check to make sure the sum custom field on the contact object is not null. You can use the System Log or Debug Logs to troubleshoot further.

mohimohi

 No need for set collection you may write simple soql query on what u want, than,

just put if condition to chk for null in score__c while iterating on contact .

alxalx

I'm having trouble coming up with the actual soql query.

 

Do you have any tips on where i there are more examples of soql queries? I have been over the web documentation and the boards, but i still cant seem to write stuff from scratch.