You need to sign in to do that
Don't have an account?
Shawn 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
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
The error says that you have used an invalid field, as per your code the query created is like this: 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
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
The error says that you have used an invalid field, as per your code the query created is like this: 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