function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
MSSBMeghanMSSBMeghan 

Rollup Summary on Lookup from Opportunity to Custom Object

***TRIGGER NEWBIE ALERT***

 

 

I have a custom Object "Goal" which houses a monthly quota for each rep.  Quota is based on several custom factors so using the standard RevenueForecast is not an option here.

 

I have a lookup from Opportunity to Goal and am trying to tally up the value of the New_Sales_Actual__c from the opportunity onto the ClosedBusiness__c field on the Goal record.

 

I found the example of how to do this 

 

I've tried this a number of ways and this one is the closest, except instead of totalling the value of New_Sales_Actual__c from all of the opportunities related to the goal, it counts the number of opportunities - so if I have 5 opportunities related to a goal valued at $1, $2, $2, $1, $1 instead of getting $7 in the ClosedBusiness__c field I get 5.

 

trigger OpportunityRollup on Opportunity (after delete, after insert,
after update) {
double sumNewSales = 0.00;

Set<id> goalIds = new Set<id>();
List<goal__c> goalsToUpdate = new List<goal__c>();

for (Opportunity item : Trigger.new)
goalIds.add(item.Goal__c);

if (Trigger.isUpdate || Trigger.isDelete) {
for (Opportunity item : Trigger.old)


goalIds.add(item.Goal__c);
}

Map<id,Goal__c> goalMap = new Map<id,Goal__c>([select id, ClosedBusiness__c
from Goal__c
where id IN :goalIds]);


for (Goal__c goal : [select Id, ClosedBusiness__c, (select id,
New_Sales_Actual__c from Opportunities__r) from Goal__c where Id IN
:goalIds]) {
goalMap.get(goal.Id).ClosedBusiness__c = goal.Opportunities__r.New_Sales_Actual__c.size();

goalsToUpdate.add(goalMap.get(goal.Id));
}

for (Goal__c goal : [select Id, ClosedBusiness__c, (select id,
New_Sales_Actual__c from Opportunities__r) from Goal__c where Id IN
:goalIds])

 

//**my playing around that didn't work


// {
// sumNewSales += goal.New_Sales_Actual__c;
// }

// goal.ClosedBusiness__c = sumNewSales;



update goalsToUpdate;

}

 

 

 

Best Answer chosen by Admin (Salesforce Developers) 
CaptainObviousCaptainObvious

This hasn't been tested, but see if it helps:

 

trigger OpportunityRollup on Opportunity (after delete, after insert, after update) {

//Create a set of Goal IDs and a Map of Goals to Update
Set<id> goalIds = new Set<id>();
Map<Id,Goal__c> goalsToUpdate = new Map<Id,Goal__c>();

//Add Goal ID's to the set

if(Trigger.isInsert || Trigger.isUpdate){
for (Opportunity item : Trigger.new) {
goalIds.add(item.Goal__c);
}
}
if (Trigger.isUpdate || Trigger.isDelete) {
for (Opportunity item : Trigger.old) {
goalIds.add(item.Goal__c);
}
}

//Populate the goalsToUpdate map with fields from the Goal

//and related Opportunities:
for (Goal__c goals :
[SELECT Id, ClosedBusiness__c,
(SELECT Id, New_Sales_Actual__c

FROM Opportunities__r)
FROM Goal__c
WHERE Id in :goalIds]){
goalsToUpdate.put(goals.id,goals);
}

//For every goal...
for (Goal__c goal : goalsToUpdate.values()) {

//Initialize all sums to '0':
double sumNewSales = 0;

//... loop through associated opportunities

//... and calculate the sum of sales:
for (Opportunity Oppty: goal.Opportunities__r){
sumNewSales += Oppty.New_Sales_Actual__c;
}
//Set the rollup sum on the current goal:
goal.ClosedBusiness__c = sumNewSales;
}
//Finally update all affected goals:
update goalsToUpdate.values();
}

 

Message Edited by CaptainObvious on 01-19-2010 05:11 PM
Edit: Updated solution with working code
Message Edited by CaptainObvious on 01-20-2010 04:08 PM

All Answers

aborchyaborchy

I am not sure if this will help but you could create a hyperlink field that opens a report when clicked. I had to do this for rolling of revenue from a related list.

 

Create a report in SF based on what you need. Copy the url.

Create a custome field in the oppty object......i used "click for report"

Enter in the formula 

HYPERLINK("ENTER YOUR REPORT URL HERE?pv0="+ Name +"&pv1="+Name, IMAGE("ADD YOUR IMAGE LINK CODE HERE", "Click For Report" , 40,40) , "_blank")

 

 

The formula I use looks like this

 

HYPERLINK("https://na5.salesforce.com/00O70000002gtNS?pv0="+ Name +"&pv1="+Name, IMAGE("http://i304.photobucket.com/albums/nn199/Mystikchick_2008/dollar_sign_rotate_ha.gif", "Click For Report" , 40,40) , "_blank")

 

 

The user just clicks the image and the report opens and runs in a new window.

Hope this helps....maybe??:)

 

CaptainObviousCaptainObvious

This hasn't been tested, but see if it helps:

 

trigger OpportunityRollup on Opportunity (after delete, after insert, after update) {

//Create a set of Goal IDs and a Map of Goals to Update
Set<id> goalIds = new Set<id>();
Map<Id,Goal__c> goalsToUpdate = new Map<Id,Goal__c>();

//Add Goal ID's to the set

if(Trigger.isInsert || Trigger.isUpdate){
for (Opportunity item : Trigger.new) {
goalIds.add(item.Goal__c);
}
}
if (Trigger.isUpdate || Trigger.isDelete) {
for (Opportunity item : Trigger.old) {
goalIds.add(item.Goal__c);
}
}

//Populate the goalsToUpdate map with fields from the Goal

//and related Opportunities:
for (Goal__c goals :
[SELECT Id, ClosedBusiness__c,
(SELECT Id, New_Sales_Actual__c

FROM Opportunities__r)
FROM Goal__c
WHERE Id in :goalIds]){
goalsToUpdate.put(goals.id,goals);
}

//For every goal...
for (Goal__c goal : goalsToUpdate.values()) {

//Initialize all sums to '0':
double sumNewSales = 0;

//... loop through associated opportunities

//... and calculate the sum of sales:
for (Opportunity Oppty: goal.Opportunities__r){
sumNewSales += Oppty.New_Sales_Actual__c;
}
//Set the rollup sum on the current goal:
goal.ClosedBusiness__c = sumNewSales;
}
//Finally update all affected goals:
update goalsToUpdate.values();
}

 

Message Edited by CaptainObvious on 01-19-2010 05:11 PM
Edit: Updated solution with working code
Message Edited by CaptainObvious on 01-20-2010 04:08 PM
This was selected as the best answer
MSSBMeghanMSSBMeghan

I can see that will get me much closer - but got an error that I can't seem to solve (again, newbie issue, I'm sure)

 

Error: Compile Error: DML requires SObject or SObject list type: MAP:Id,SOBJECT:Goal__c at line 42 column 5

 

Line 42 is:

 

    update goalsToUpdate;

Digging a bit on this on my own, but appreciate any help!

 

CaptainObviousCaptainObvious

My mistake! I changed your List to a Map and forgot to update that line :smileyvery-happy:

Try this instead:

 

update goalsToUpdate.values();

 

 

MSSBMeghanMSSBMeghan

***IT WORKED!!!***

 

I even was able to take your example and update renewal amounts in the same fashion!

 

 

 

 

trigger OpportunityRollup on Opportunity (after delete, after insert, after update) { //Create a set of Goal IDs and a Map of Goals to Update Set<id> goalIds = new Set<id>(); Map<Id,Goal__c> goalsToUpdate = new Map<Id,Goal__c>(); //Add Goal ID's to the set if(Trigger.isInsert || Trigger.isUpdate){ for (Opportunity item : Trigger.new) { goalIds.add(item.Goal__c); } } if (Trigger.isUpdate || Trigger.isDelete) { for (Opportunity item : Trigger.old) { goalIds.add(item.Goal__c); } } //Populate the goalsToUpdate map with fields from the Goal and related Opportunities: for (Goal__c goals : [SELECT Id, ClosedBusiness__c, Closed_Renewals__c, (SELECT Id, New_Sales_Actual__c, Renewal_Amount__c FROM Opportunities__r) FROM Goal__c WHERE Id in :goalIds]){ goalsToUpdate.put(goals.id,goals); } //For every goal... for (Goal__c goal : goalsToUpdate.values()) { //Initialize all sums to '0': double sumNewSales = 0; double sumRenewSales = 0; //... loop through associated opportunities and calculate the sum of sales: for (Opportunity Oppty: goal.Opportunities__r){ sumNewSales += Oppty.New_Sales_Actual__c; sumRenewSales += Oppty.Renewal_Amount__c; } //Set the rollup sum on the current goal: goal.ClosedBusiness__c = sumNewSales; goal.Closed_Renewals__c = sumRenewSales; } //Finally update all affected goals: update goalsToUpdate.values(); }

 

 Now I just have to struggle my way through a test class...this is so great, yet so tough for a relative newbie!!

 

 

CaptainObvious, you are wonderful.  Thank you so much for your help!!!!

 

CaptainObviousCaptainObvious
Glad that worked out for you! I updated the solution with the fixed code for future reference. Dont be afraid to ask if you get stuck on the test class :smileywink:
Message Edited by CaptainObvious on 01-20-2010 04:13 PM
MSSBMeghanMSSBMeghan

OK, so I have to admit bad process here...but blame it on my Apex newbie-ness!

 

I wrote the following to just get a test class that had enough coverage to get what I need into production - then once I was able to do that (and meet my deadline!) I was going to go back and refine.

 

In Sandbox 100% - running it in Eclipse - ugh....

 

 

@isTest
private class testOpportunityRollup {

static testMethod void testOpportunityRollup (){

Goal__c a = new goal__c(ownerId='00530000000xEi5AAE');
insert a;
a = [SELECT c.ClosedBusiness__c from Goal__c c WHERE Id = :a.id];
System.assert(a != null);

}
}

 

 Not looking for test to be written for me - just hoping that I can get a bit more clarity.  History major here who has never written a test class on her own....

 

Thanks in advance for taking pity on me!!!

 

CaptainObviousCaptainObvious

Here's where the fun starts!

@isTest private class testOpportunityRollup { static testMethod void testOpportunityRollup (){ /* * To determine what you need to test, pretend * that you have a brand new instance of salesforce... * * You'll have to create ALL your data from scratch! */ //First create a goal //If you have required fields or custom validation, you need //to include those fields in your test. This applies to any object //you create. //When Creating objects, DO NOT use hardcorded id's as below: //Goal__c a = new goal__c(ownerId='00530000000xEi5AAE'); //Although this may work in your sandbox, there's a good chance that it will //not work in production because the id may be different Goal__c g1 = new goal__c(name='Goal 1',ClosedBusiness__c=0); insert g1; //Create a new Account: Account a = new Account(name='Test Account'); insert a; //Now Create some Opportunities using the account and goal you just 'created': List<Opportunity> opportunities = new List<Opportunity>(); Opportunity OP1 = new Opportunity( Name = 'Test Opp 1', AccountId = a.id, New_Sales_Actual__c=10, Goal__c=g1.id ); Opportunity OP2 = new Opportunity( Name = 'Test Opp 2', AccountId = a.id, New_Sales_Actual__c=40, Goal__c=g1.id); //For the next opportunity, leave out the goal... Opportunity OP3 = new Opportunity( Name = 'Test Opp 3', AccountId = a.id, New_Sales_Actual__c=50); opportunities.add(OP1); opportunities.add(OP2); opportunities.add(OP3); //insert the opportunities you created... insert opportunities; /* * To do: * * Assert that the goal now contains the correct sum * Remember that we only added Goals to 2 of the 3 opportunities. * If the sum is '100' we're in trouble! * * Your trigger also fires 'on delete' and 'on update'... * You'll have to test those cases as well * Hint: * //update an opportunity: * OP1.New_Sales_Actual__c=0; * update OP1; * */ } }

For homework: finish the test! :smileyvery-happy:

Message Edited by CaptainObvious on 01-25-2010 05:21 PM
MSSBMeghanMSSBMeghan

WOW.  Ok, so first of THANK YOU!!  I can't believe (A) that you got me started in such detail and (B) what a geek am I that I just canceled plans to go see Avatar because it seemed like it would be more fun to learn how to do this  :-)

 

OK, so first question: - put the code you wrote into my sandbox, did a quick save and got the following:

 

Error: Compile Error: Field is not writeable: Goal__c.Name at line 23 column 35

 

the Goal Name is an auto number - how do you treat that in a test?

 

This is fun!  (so far)

 

 

 

Edited:

 

I tried the following and still got the same error:

 

 

Goal__c g1 = new goal__c(name='A-5555555',ClosedBusiness__c=0);

 

But setting that aside I took a stab at the rest, and so far came up with the following:

 

 

 

// validate single for(Goal__c g:[SELECT New_Sales_Actual__c FROM Opportunity WHERE Id= :g1.id AND New_Sales_Actual__c != null]) System.assertEquals(g.ClosedBusiness__c, 10); } for(Goal__c g:[SELECT New_Sales_Actual__c FROM Opportunity WHERE Id= :g1.id AND New_Sales_Actual__c != null]) System.assertEquals(g.ClosedBusiness__c, 40); //validate bulk ClosedBusiness__c = 0; System.debug('Inserting goal records (bulk validation)'); List<Opportunity> opportunities = new List<Opportunity>(); for(integer i=0; i<200; i++) {opportunity.add( new Goal__c(ClosedBusiness = 50, Date__c = System.today()) ); }

 

 

 

 

 Am I even close?

 

 

Message Edited by MSSBMeghan on 01-25-2010 02:56 PM
CaptainObviousCaptainObvious

If the name is an autonumber, just leave it out. You can populate another field.

MSSBMeghanMSSBMeghan

OK, so hit a few walls on this one.

 

New_Sales_Actual__c is a formula field so I can't set a value.  (It's actually the summary of two opportunity product rollup fields).  But I need the New_Sales_Actual__c to populate and confirm my g1.ClosedBusiness__c

 

so...

 

I ended up creating a product and a pricebook in the test to then calculate the New_Sales_Actual__c field in the test opportunities. Was able to get that working (took quite some doing!)

 

I'm still not there - my Assert is wrong and I can't get the after delete piece figured out.

 

I know I'm almost there - this is killing me!!  (Although it has been a good learning process).... 

 

 

@isTestprivate class testOpportunityRollup {

static testMethod void testOpportunityRollup (){

 

//Create a Goal

Goal__c g1 = new goal__c(Month__c=Date.valueof('2010-01-01'),ClosedBusiness__c=0);

insert g1;

 

//Create a new Account:

Account a = new Account(name='Test Account');

insert a;

 

//Create some Opportunities 

List<Opportunity> opportunities = new List<Opportunity>(); 

Opportunity OP1 = new Opportunity( Name = 'Test Opp 1', AccountId = a.id, CloseDate=Date.valueof('2010-01-01'), StageName='Final Payment Received', Goal__c=g1.id );

 

Opportunity OP2 = new Opportunity( Name = 'Test Opp 2', AccountId = a.id, CloseDate=Date.valueof('2010-01-01'), StageName='Final Payment Received', Goal__c=g1.id);

 

Opportunity OP3 = new Opportunity( Name = 'Test Opp 3', AccountId = a.id, CloseDate=Date.valueof('2010-01-01'), StageName='Final Payment Received');

opportunities.add(OP1);

opportunities.add(OP2);

opportunities.add(OP3);

 

insert opportunities;

 

// Create Products

Product2 testprod1 = new Product2 (name='test product one1');

testprod1.productcode = 'test pd code1one';

insert testprod1;

 

Product2 testprod2 = new Product2 (name='test product two2');

testprod2.productcode = 'test pd code2two';

insert testprod2;

 

// Get Pricebook

Pricebook2 testpb = [select id from Pricebook2 where IsStandard = true];

 

// Add to pricebook

PricebookEntry testpbe1 = new PricebookEntry ();

testpbe1.pricebook2id = testpb.id; 

testpbe1.product2id = testprod1.id;

testpbe1.IsActive = True;

testpbe1.UnitPrice = 50;

testpbe1.UseStandardPrice = false;

insert testpbe1;

 

OpportunityLineItem oli1 = new OpportunityLineItem();

oli1.Quantity = 1;

oli1.TotalPrice = 50;

oli1.One_Time_Amount__c = 50;

oli1.Renewable_Amount__c = 0;

oli1.PricebookEntryId = testpbe1.id;

oli1.OpportunityId = OP1.id;

insert oli1;

 

OpportunityLineItem oli3 = new OpportunityLineItem();

oli3.Quantity = 1;

oli3.TotalPrice = 50;

oli3.One_Time_Amount__c = 50;

oli3.Renewable_Amount__c = 0;

oli3.PricebookEntryId = testpbe1.id;

oli3.OpportunityId = OP2.id;

insert oli3;

 

System.assert(g1.ClosedBusiness__c <= 100);

 

//Update Opportunity OP1

Opportunity opp = [select o.Name, o.AccountId, o.CloseDate from Opportunity o where o.Name = 'Test Opp 1' ]; if(opp.Name=='Test Opp 1')

{

opp.Name='Test Opp 1b';

}

update opp;

 

// After Delete test

 

}

 

 

 

 Some more Details on the Various Custom Fields:

 

 New_Sales_Actual__c   (OPPORTUNITY)

          IF( IsWon, New_Amount__c + One_time_Amount__c , 0)

 

New_Amount__c (OPPORTUNITY)

 Opportunity Product: New_Amount (Rollup Summary OPP PROD)

 

 

One_time_Amount__c (OPPORTUNITY)

 Opportunity Product: One_Time_Amount (Rollup Summary OPP PROD)

 

 

Opportunity Product: New_Amount

     IF( TotalPrice - One_Time_Amount__c <= Renewable_Amount__c , 0,

          TotalPrice - One_Time_Amount__c - Renewable_Amount__c )

 

 

Opportunity Product: One_Time_Amount

  (currency)

 

Opportunity Product:

Renewable_Amount__c   (currency)

 

CaptainObviousCaptainObvious

Great progress!

Here are some things to try...

To update an opportunity you've already created, first edit a field in the opportunity, and then run the update:

OP1.Name='Test Opp 1b'; update OP1;

To get an idea why your assert is not working, retreive any relevant fields from the opportunity and add a debug statement (this will show up in the debug log). You'll want to check that the formula fields are being populated.

Opportunity opp = [Select Name, AccountId, CloseDate, New_Sales_Actual__c, New_Amount__c, One_time_Amount__c, IsWon from Opportunity where Id = :OP1.id]; System.debug('**************** Test Opportunity Fields: ' + opp);

To assert that the Closedbusiness equals 50, for example, use:

 

System.assertEquals(50,g1.ClosedBusiness__c);

The delete is just as easy as the update:

 

delete OP1;

Of course, once you delete it, you want to assert that the Goal has been updated!

 

Hope that helps.