+ Start a Discussion
Reppin__505Reppin__505 

Calculate the Total Opportunity Amount for the Associated Contact Record

Hello,

 

I would like to calculate the total amount of all child opportunities associated with the parent contact.

 

If a contact record has 2 opportunies, one with an amount of $20 and one with an amount of $50, i would like the contact record to have a field reflect the total of $50.

 

Below is the code i have so far.

------------------- 

trigger TestBed on Opportunity (after insert, after update)

{

 

 

//The list returns all the name of the contacts and the opportunity id and opportunity amount associated with that

contact

List<Contact> contax = [SELECT Contact.Name, (Select Opportunity.Id, Opportunity.Amount From Contact.Opportunities) From Contact Where Contact.AccountId != Null];

 

 

 

// Loop through the contacts and update their Opportunity Total field.

 for(Contact c : contax)

{

c.c_Opportunity_Total__c = contax.size();

}

 

// Update the database

 update contax;

 

 

 

 

}

---------------------

 

I would think it would be as easy as summing up the Opportunity.Amount.

 

LIke

 

c.c_Opportunity_Total__c = Sum(contax.Opportunity.Amount)

 

But that is not possible in this environment. Apparently the query fields associated with the list cannot be referenced. Or can they?

 

My questions are:

 

1.) With the above code, how can i reference the SOQL field Opportunity.Amount, and sum the total results of that query's findings for that field? Then add that total to the Contact record.

 

2.) Where can i find information about how to use math methods with SOQL?

 

Thanks so much for your help, so far i've spent hours trying to research this on my own with no luck. Newbie here.

Best Answer chosen by Admin (Salesforce Developers) 
Reppin__505Reppin__505

Figured it out myself. Would've been alot sooner if i would have gotten some direction...... 

 

Here's the code if anyone out there is even remotely interested.

 

-------------------

 

trigger TestBed on Opportunity (after insert, after update)

{

Integer i = 0;

 

// Grab this opportunities ID

 

String intTest = Trigger.new[i].Id;

 

 

//Must use the colon that specifies the bind variable. This query pulls one row from the Opportunity contact role table. I will use the contact id from this record.

 OpportunityContactRole test2 = [SELECT Id, OpportunityId, ContactId FROM OpportunityContactRole WHERE OpportunityId = :intTest];

//Step 2. Create a list of opportunities who are children of this contact record.

  

 

List<Opportunity> opp = [Select Opportunity.Id, Opportunity.Amount From Opportunity Where Opportunity.Id IN (Select OpportunityContactRole.OpportunityId from OpportunityContactRole where OpportunityContactRole.ContactId = :test2.ContactId)];

 

Double j = 0;

 

// Loop through the filtered opportunites and sum up their amounts.

 for(Opportunity op : opp)

{

If (op.Amount != Null)

{

j += op.Amount;

}

}

//Place the summed total in a custom field on the associated contact record.

 

Contact test3 = [SELECT Id, c_Opportunity_Total__c FROM Contact WHERE Id = :test2.ContactId];

test3.c_Opportunity_Total__c = j;

//Do all this when the trigger is initiated.

update test3;

 

}

All Answers

Reppin__505Reppin__505

Figured it out myself. Would've been alot sooner if i would have gotten some direction...... 

 

Here's the code if anyone out there is even remotely interested.

 

-------------------

 

trigger TestBed on Opportunity (after insert, after update)

{

Integer i = 0;

 

// Grab this opportunities ID

 

String intTest = Trigger.new[i].Id;

 

 

//Must use the colon that specifies the bind variable. This query pulls one row from the Opportunity contact role table. I will use the contact id from this record.

 OpportunityContactRole test2 = [SELECT Id, OpportunityId, ContactId FROM OpportunityContactRole WHERE OpportunityId = :intTest];

//Step 2. Create a list of opportunities who are children of this contact record.

  

 

List<Opportunity> opp = [Select Opportunity.Id, Opportunity.Amount From Opportunity Where Opportunity.Id IN (Select OpportunityContactRole.OpportunityId from OpportunityContactRole where OpportunityContactRole.ContactId = :test2.ContactId)];

 

Double j = 0;

 

// Loop through the filtered opportunites and sum up their amounts.

 for(Opportunity op : opp)

{

If (op.Amount != Null)

{

j += op.Amount;

}

}

//Place the summed total in a custom field on the associated contact record.

 

Contact test3 = [SELECT Id, c_Opportunity_Total__c FROM Contact WHERE Id = :test2.ContactId];

test3.c_Opportunity_Total__c = j;

//Do all this when the trigger is initiated.

update test3;

 

}

This was selected as the best answer
rameshvpsgrameshvpsg

Hi Reppin,

 i also have similar requirement. I just want to sum all the opportunity amount that a contact is associated to and populate in a contact object custom field. Please let me know whether you have updated code on this.

 

Thanks,

Ramesh V

Andrew LikensAndrew Likens
Hi Reppin,

Did you create a test class for your trigger above? Also, thank you for posting your final trigger code. I am actually going to use this for one of my company's needs.