Apex Code Development You need to sign in to do that
Don't have an account?
gregj777 Roll up summary from Opportunities into Contract object
I need help with how to create a formula to roll-up the total amounts from a group of opportunities and sum the amount into the Contract object. Currently, I have the contract referenced in the opportunity and a related list in the Contract layout. I thought this would be easy by just creating a custom field under Contract and used the Roll-up summary field type. But this does not allow me to do that because its not considered the master in the master-detail relationship.
I think my only option is Apex Code. Can anyone help me with that?
Any help asap would be greatly appreciated.
You made a good observation.....Following changes can be made.
trigger updateContract on Opportunity (before Update) {
set<id> contIds = new set<id>();
list<contract> listcon = new list<contract>();
set<id> oppIds = new set<id>();
Double InitialSum ;
For(Opportunity o :trigger.new)
{
contIds.add(o.contract__c);
oppIds.add(o.id);
}
Map<id,contract> MapofCons = new Map<id,contract>([select id, name,sum_total__c from contract where id in :contIds]);
Map<id, opportunity> MapOfOpps = new Map<id,opportunity>([select id,name, amount from opportunity where id in :oppIds]);
For(opportunity o: trigger.new)
{
If( MapofCons.get(o.contract__c).sum_total__c ==null)
InitialSum = 0 ;
else
InitialSum = MapofCons.get(o.contract__c).sum_total__c;
If((MapOfOpps.get(o.id).amount==null)&& (o.amount!=null))
{ MapofCons.get(o.contract__c).sum_total__c = InitialSum + o.amount;
Listcon.add(MapofCons.get(o.contract__c));
}
else If((MapOfOpps.get(o.id).amount!=null)&& (o.amount==null))
{ MapofCons.get(o.contract__c).sum_total__c = InitialSum ;
Listcon.add(MapofCons.get(o.contract__c));
}
else
{
if(mapofOpps.get(o.id).amount != o.amount)
{ MapofCons.get(o.contract__c).sum_total__c = InitialSum + o.amount- mapofOpps.get(o.id).amount ;
Listcon.add(MapofCons.get(o.contract__c));
}
}
}
update Listcon;
}
All Answers
So what I'm getting is, you've simple look up relationship b/w contract(parent) and opportunity(child) and thats why you're not able to use roll up summary field on contract. I could try to give u a code for this. but before that want to ask- is that possible for you to convert field type to 'master-detail' from 'look-up' ? This would be the easiest solution, if I understood your question correctly.
I tried to do that but it was also not allowing me to change the field from lookup to master detail field type. Also I saw that in the help section on how to Creating the Two Master-Detail Relationships but found the instructions very confusing.
https://na5.salesforce.com/help/doc/user_ed.jsp?section=help&target=relationships_manytomany.htm&loc=help&hash=JORelationships
Also, maybe I am doing this wrong. Am I only one trying to manage a list of opportunities under a contract?
Apex_Keen, thanks for any help you can provide on this issue.:smileyhappy:
Got busy on other things. You can write following trigger on Opportunity object. This will update custom field(Number) on contract
trigger updateContract on Opportunity (before Update) {
set<id> contIds = new set<id>();
list<contract> listcon = new list<contract>();
For(Opportunity o :trigger.new)
{
contIds.add(o.contract__c);
}
// sum_total__c is the custom field on contract which will show total amount from linked opportunity(ies).
Map<id,contract> MapofCons = new Map<id,contract>([select id, name,sum_total__c from contract where id in :contIds]);
For(opportunity o: trigger.new)
{
If( MapofCons.get(o.contract__c).sum_total__c ==null)
MapofCons.get(o.contract__c).sum_total__c = 0;
MapofCons.get(o.contract__c).sum_total__c = MapofCons.get(o.contract__c).sum_total__c + o.amount ;
Listcon.add(MapofCons.get(o.contract__c));
}
update Listcon;
}
Get back to me in case of any issues.
I updated the code with my Contract custom field but it's giving me an error:
Line 7: contIds.add(o.contract__c);
This is the imaginary name, I've given to look up field on opportunity object. You've got to App Setup -> Customize-> Opportunity-> fields
and check the API Name given to look up field(link with contract object) and modify the trigger accordingly.
let me know in case of further doubts
It is working somewhat. The problem is it's creating a rolling summary everytime I make any changes to an opportunity. Here is the field assignemnt I replaced in the trigger below:
Contract_Number__c = From opportunity object lookup field
Total_Opportunity__Amount = From Contract object value field (Is it possible to make this a currency field instead?)
For example:
Opp1 = 3,000
Opp2 = 3,000
Total Opportunitiy Amount = 6,000
Go into opp1 make change and save
Total Opportunity Amount = 9,000
Go into opp2 make change and save
Total Opportunity Amount = 12,000
trigger updateContract on Opportunity (before Update) {
set<id> contIds = new set<id>();
list<contract> listcon = new list<contract>();
For(Opportunity o :trigger.new)
{
contIds.add(o.Contract_Number__c);
}
// sum_total__c is the custom field on contract which will show total amount from linked opportunity(ies).
Map<id,contract> MapofCons = new Map<id,contract>([select id, name,Total_Opportunity_Amount__c from contract where id in :contIds]);
For(opportunity o: trigger.new)
{
If( MapofCons.get(o.Contract_Number__c).Total_Opportunity_Amount__c ==null)
MapofCons.get(o.Contract_Number__c).Total_Opportunity_Amount__c = 0;
MapofCons.get(o.Contract_Number__c).Total_Opportunity_Amount__c = MapofCons.get(o.Contract_Number__c).Total_Opportunity_Amount__c + o.amount ;
Listcon.add(MapofCons.get(o.Contract_Number__c));
}
update Listcon;
}
You made a good observation.....Following changes can be made.
trigger updateContract on Opportunity (before Update) {
set<id> contIds = new set<id>();
list<contract> listcon = new list<contract>();
set<id> oppIds = new set<id>();
Double InitialSum ;
For(Opportunity o :trigger.new)
{
contIds.add(o.contract__c);
oppIds.add(o.id);
}
Map<id,contract> MapofCons = new Map<id,contract>([select id, name,sum_total__c from contract where id in :contIds]);
Map<id, opportunity> MapOfOpps = new Map<id,opportunity>([select id,name, amount from opportunity where id in :oppIds]);
For(opportunity o: trigger.new)
{
If( MapofCons.get(o.contract__c).sum_total__c ==null)
InitialSum = 0 ;
else
InitialSum = MapofCons.get(o.contract__c).sum_total__c;
If((MapOfOpps.get(o.id).amount==null)&& (o.amount!=null))
{ MapofCons.get(o.contract__c).sum_total__c = InitialSum + o.amount;
Listcon.add(MapofCons.get(o.contract__c));
}
else If((MapOfOpps.get(o.id).amount!=null)&& (o.amount==null))
{ MapofCons.get(o.contract__c).sum_total__c = InitialSum ;
Listcon.add(MapofCons.get(o.contract__c));
}
else
{
if(mapofOpps.get(o.id).amount != o.amount)
{ MapofCons.get(o.contract__c).sum_total__c = InitialSum + o.amount- mapofOpps.get(o.id).amount ;
Listcon.add(MapofCons.get(o.contract__c));
}
}
}
update Listcon;
}
Sorry but it now seems to be going the wrong way. Total_Opportunity_Amount__c was show -2,000 when:
Opp1 Amount = 3,000
Opp2 Amount = 3.000
I replaced your field with my custom field created under the Opportunity object as:
Contract__c = Contract_number__c
sum_amount__c = Total_Opportunity_Amount__c
If you like I granted accesss to my developers site for 3 days:
uersid: gjjconsulting@gmail.com
Password: Abraham777
Sorry to keep bothering you but were able to figure this out. I think we are close at least it looks that way...:)
Sorry got busy again.. things are working fine at my end. would suggest you to make totally new contract record, then create 2, 3 new opportunities linked to that contract .Make sure amount field is getting populated fine while 'updating' the opportunities and then try to verify amount at contract end.
if this fail, please provide me activation code to login so that I can look at your side.
Thanks so much. Everything works great!
Sorry, but slight issue with the trigger. When there is not a contract linked to the opportunity it gives me an error on save:
Error: Invalid Data.
Review all error messages below to correct your data.
Apex trigger updateContract caused an unexpected exception, contact your administrator: updateContract: execution of BeforeUpdate caused by: System.NullPointerException: Attempt to de-reference a null object: Trigger.updateContract: line 20, column 34
Can that be resolved?
I was wondering if you hav any update on this one. Is it possible to update this apex trigger so it allows to still create an opportunity without having the contract linked to it?
Would appreciate any help?