You need to sign in to do that
Don't have an account?
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;
}
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();
}
All Answers
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??:)
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();
}
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!
My mistake! I changed your List to a Map and forgot to update that line :smileyvery-happy:
Try this instead:
update goalsToUpdate.values();
***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!!!!
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!!!
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:
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?
If the name is an autonumber, just leave it out. You can populate another field.
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)
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.