+ Start a Discussion
Shawn ReichnerShawn Reichner 

Trigger help with Rollup of Opportunity Sales to Custom Object

Hello,

FIrst off I am very new to coding, and have attempted to try to get the following Trigger and Class to work (Phase 1) which is to have amounts populate from "Closed-Won" opportunities to a custom object I created to house Salespeople total sales.   You will notice in my code I do not have the parameters to take dates into account, which what I am after is is an opportunity is closed within the month of Januaray 2015, then a certain field on the custom object will be the rollup for all opps closed in Janurary.  There will be a field for each month of the 2015 year that will need to be populated with amounts from closed won opps in that particular month for that specific salesperson.   So far my trigger and class is build to test the waters just to see that the rollup is working, but I am receiving an error when attempting to save an opportunity. So I am hopeful that someone here would be nice enough to tell me where I may be going wrong, and also some points into getting the above scenerio to work to bring over amounts from certail months into certain fields on the Custom Object.  Again new at code so if I left anything out, please ask if that will help you to help me the newbie! :)

Here is my trigger code

trigger OpportunityRollUpEstPrice on Opportunity (after delete, after insert, after update, after undelete) {
if(trigger.isInsert || trigger.isUpdate || trigger.isUnDelete){
list<RollUpSummaryUtility.fieldDefinition> fieldDefinitions =
 new list<RollUpSummaryUtility.fieldDefinition> {
 new RollUpSummaryUtility.fieldDefinition('SUM', 'Estimated_Price__c',
 'Jan_Rev_ACT__c')
 };
RollUpSummaryUtility.rollUpTrigger(fieldDefinitions, trigger.new,
 'Sales_Targets__c', 'Sales_Target__c', 'Opportunities', '');
}
if(trigger.isDelete){
list<RollUpSummaryUtility.fieldDefinition> fieldDefinitions =
 new list<RollUpSummaryUtility.fieldDefinition> {
 new RollUpSummaryUtility.fieldDefinition('SUM', 'Estimated_Price__c',
 'Jan_Rev_ACT__c')
 };
RollUpSummaryUtility.rollUpTrigger(fieldDefinitions, trigger.old,
 'Jan_Rev_ACT__c', 'Opportunity__c', 'Opportunities', '');
}
}

Here is my Class - 

public class RollUpSummaryUtility {
//the following class will be used to house the field names
 //and desired operations
 public class fieldDefinition {
 public String operation {get;set;}
 public String childField {get;set;}
 public String parentField {get;set;}
public fieldDefinition (String o, String c, String p) {
 operation = o;
 childField = c;
 parentField = p;
 }
 }
public static void rollUpTrigger(list<fieldDefinition> fieldDefinitions,
 list<sObject> records, String childObject, String childParentLookupField,
 String parentObject, String queryFilter) {
//Limit the size of list by using Sets which do not contain duplicate
 //elements prevents hitting governor limits
 set<Id> parentIds = new set<Id>();
for(sObject s : records) {
 parentIds.add((Id)s.get(childParentLookupField));
 }
//populate query text strings to be used in child aggregrator and
 //parent value assignment
 String fieldsToAggregate = '';
 String parentFields = '';
for(fieldDefinition d : fieldDefinitions) {
 fieldsToAggregate += d.operation + '(' + d.childField + ') ' +
 ', ';
 parentFields += d.parentField + ', ';
 }
//Using dynamic SOQL with aggergate results to populate parentValueMap
 String aggregateQuery = 'Select ' + fieldsToAggregate +
 childParentLookupField + ' from ' + childObject + ' where ' +
 childParentLookupField + ' IN :parentIds ' + queryFilter + ' ' +
 ' group by ' + childParentLookupField;
//Map will contain one parent record Id per one aggregate object
 map<Id, AggregateResult> parentValueMap =
 new map <Id, AggregateResult>();
for(AggregateResult q : Database.query(aggregateQuery)){
 parentValueMap.put((Id)q.get(childParentLookupField), q);
 }
//list of parent object records to update
 list<sObject> parentsToUpdate = new list<sObject>();
String parentQuery = 'select ' + parentFields + ' Id ' +
 ' from ' + parentObject + ' where Id IN :parentIds';
//for each affected parent object, retrieve aggregate results and
 //for each field definition add aggregate value to parent field
 for(sObject s : Database.query(parentQuery)) {
Integer row = 0; //row counter reset for every parent record
 for(fieldDefinition d : fieldDefinitions) {
 String field = 'expr' + row.format();
 AggregateResult r = parentValueMap.get(s.Id);
 //r will be null if no records exist
 //(e.g. last record deleted)
 if(r != null) {
 Decimal value = ((Decimal)r.get(field) == null ) ? 0 :
 (Decimal)r.get(field);
 s.put(d.parentField, value);
 } else {
 s.put(d.parentField, 0);
 }
 row += 1; //plus 1 for every field definition after first
 }
 parentsToUpdate.add(s);
 }
//if parent records exist, perform update of all parent records
 //with a single DML statement
 if(parentsToUpdate.Size() > 0) {
 update parentsToUpdate;
 }
}
}

Thanks again for any help you can provide, as I would love to get this working! 

Shawn
Best Answer chosen by Shawn Reichner
Neetu_BansalNeetu_Bansal
Hi Shawn,

The error says that you have used an invalid field, as per your code the query created is like this:
Select SUM(Estimated_Price__c), Sales_Target__c from Sales_Targets__c where Sales_Target__c IN: parentIds group by Sales_Target__c
Please check that it a valid query or not as per your object model. Please ensure that 'Estimated_Price__c' is a valid field on custom object 'Sales_Targets__c'

Let me know, if you need any other help.

Thanks,
Neetu

All Answers

Shawn ReichnerShawn Reichner
Sorry helpers...here is the error I am receiving when attempting to save an opportunity with the trigger active. 

Error: Invalid Data. 
Review all error messages below to correct your data.
Apex trigger OpportunityRollUpEstPrice caused an unexpected exception, contact your administrator: OpportunityRollUpEstPrice: execution of AfterUpdate caused by: System.QueryException: Invalid field: 'Estimated_Price__c': Class.RollUpSummaryUtility.rollUpTrigger: line 40, column 1
Neetu_BansalNeetu_Bansal
Hi Shawn,

The error says that you have used an invalid field, as per your code the query created is like this:
Select SUM(Estimated_Price__c), Sales_Target__c from Sales_Targets__c where Sales_Target__c IN: parentIds group by Sales_Target__c
Please check that it a valid query or not as per your object model. Please ensure that 'Estimated_Price__c' is a valid field on custom object 'Sales_Targets__c'

Let me know, if you need any other help.

Thanks,
Neetu
This was selected as the best answer
Shawn ReichnerShawn Reichner
Thank you very much for your insight! The Estimated Price field is actually on the Opportunity object which is the field that I want to use on all opps for that salesperson to roll up into the field JanRevACT_C on the custom object Sales Targets_c Does that make more sense….looks like I go this all backwards, as I said very beginner in code, so any help to get me back on track to get what I am looking for would be very much appreciated! Shawn Shawn Reichner Platform Developer, Salesforce AVI-SPL P: 866-588-6857 C: 813-347-7952 E: shawn.reichner@avispl.com [cid:7FC1BB64-B4F9-4F13-8C62-21193D9AB7D7][cid:140226DD-3280-41AB-B6C6-9A30140DFB86]