You need to sign in to do that
Don't have an account?
Aron Schor 24
Issue with Trigger that finds the SUM of Amount of records based on grouping
Hi, I utilized ChatGPT to help create a trigger. It seems run but does not seem update. From using the debug log it says "21:29:14.0 (164719242)|VARIABLE_ASSIGNMENT|[5]|pitch|null|" Can I get assistance understanding why it passes as null?
I essentially want:
1. If a value is set on CustomObject__c for CustomFieldId__c, which is a lookup to Product2, I want Salesforce to find the value for CustomFieldId__r.Property_Group_Text__c.
2. Then I want Salesforce to search all Records from CustomObject__c who have the same CustomFieldId__r.Property_Group_Text__c value
3. And find the SUM of Opportunity_Amount__c, which is populated when the Opportunity is added.
I have tried updating Property_Group_Text__c on the CustomObject and then running second trigger but I'd have problems with the record being in read only mode, so I figured I'd try to have this trigger do two things do the lookup as well.
If I could find some way to have the field on the CustomObject show the Sum of the Opportunity Amounts, either by lookup or by a field on CustomObject as shown, I'd be very appreciative. Aron
trigger UpdateTrigger on CustomObject__c (after insert, after update) {
Set<String> updatedPropertyGroups = new Set<String>();
// Loop through the trigger records and add their updated Property_Group_Text__c values to the set
for (CustomObject__c CustomObject : Trigger.new) {
if (CustomObject.CustomFieldId__c != null && (Trigger.isInsert || CustomObject.Property_Group_Text__c != Trigger.oldMap.get(CustomObject.Id).Property_Group_Text__c)) {
updatedPropertyGroups.add(CustomObject.CustomFieldId__r.Property_Group_Text__c);
}
}
// Query the Opportunities related to the updated CustomObject records and Property_Group_Text__c
List<AggregateResult> results = [SELECT SUM(Opportunity_Amount__c) sumAmount, Property_Group_text__c propertyGroupValue
FROM
WHERE CustomFieldId__c IN :Trigger.newMap.keySet() AND Property_Group_text__c IN :updatedPropertyGroups
GROUP BY Property_Group_text__c];
// Create a map of Property Group Text values to their SUM(Opportunity__r.Amount) totals
Map<String, Decimal> propertyGroupAmounts = new Map<String, Decimal>();
for (AggregateResult result : results) {
propertyGroupAmounts.put((String) result.get('propertyGroupValue'), (Decimal) result.get('sumAmount'));
}
// Create a list of CustomObject__c records to update
List<CustomObject__c> CustomObjectesToUpdate = new List<CustomObject__c>();
// Loop through the updated CustomObject records again and update their Property_Group_Amount__c field
for (CustomObject__c CustomObject : Trigger.new) {
if (updatedPropertyGroups.contains(CustomObject.Property_Group_text__c)) {
CustomObject.Property_Group_Amount__c = propertyGroupAmounts.get(CustomObject.Property_Group_text__c);
CustomObjectesToUpdate.add(CustomObject);
}
}
// Perform a DML update on the list of CustomObject__c records
if (!CustomObjectesToUpdate.isEmpty()) {
update CustomObjectesToUpdate;
}
}
I essentially want:
1. If a value is set on CustomObject__c for CustomFieldId__c, which is a lookup to Product2, I want Salesforce to find the value for CustomFieldId__r.Property_Group_Text__c.
2. Then I want Salesforce to search all Records from CustomObject__c who have the same CustomFieldId__r.Property_Group_Text__c value
3. And find the SUM of Opportunity_Amount__c, which is populated when the Opportunity is added.
I have tried updating Property_Group_Text__c on the CustomObject and then running second trigger but I'd have problems with the record being in read only mode, so I figured I'd try to have this trigger do two things do the lookup as well.
If I could find some way to have the field on the CustomObject show the Sum of the Opportunity Amounts, either by lookup or by a field on CustomObject as shown, I'd be very appreciative. Aron
trigger UpdateTrigger on CustomObject__c (after insert, after update) {
Set<String> updatedPropertyGroups = new Set<String>();
// Loop through the trigger records and add their updated Property_Group_Text__c values to the set
for (CustomObject__c CustomObject : Trigger.new) {
if (CustomObject.CustomFieldId__c != null && (Trigger.isInsert || CustomObject.Property_Group_Text__c != Trigger.oldMap.get(CustomObject.Id).Property_Group_Text__c)) {
updatedPropertyGroups.add(CustomObject.CustomFieldId__r.Property_Group_Text__c);
}
}
// Query the Opportunities related to the updated CustomObject records and Property_Group_Text__c
List<AggregateResult> results = [SELECT SUM(Opportunity_Amount__c) sumAmount, Property_Group_text__c propertyGroupValue
FROM
WHERE CustomFieldId__c IN :Trigger.newMap.keySet() AND Property_Group_text__c IN :updatedPropertyGroups
GROUP BY Property_Group_text__c];
// Create a map of Property Group Text values to their SUM(Opportunity__r.Amount) totals
Map<String, Decimal> propertyGroupAmounts = new Map<String, Decimal>();
for (AggregateResult result : results) {
propertyGroupAmounts.put((String) result.get('propertyGroupValue'), (Decimal) result.get('sumAmount'));
}
// Create a list of CustomObject__c records to update
List<CustomObject__c> CustomObjectesToUpdate = new List<CustomObject__c>();
// Loop through the updated CustomObject records again and update their Property_Group_Amount__c field
for (CustomObject__c CustomObject : Trigger.new) {
if (updatedPropertyGroups.contains(CustomObject.Property_Group_text__c)) {
CustomObject.Property_Group_Amount__c = propertyGroupAmounts.get(CustomObject.Property_Group_text__c);
CustomObjectesToUpdate.add(CustomObject);
}
}
// Perform a DML update on the list of CustomObject__c records
if (!CustomObjectesToUpdate.isEmpty()) {
update CustomObjectesToUpdate;
}
}
In any case, I noticed that there is a syntax error in the query in the "results" variable initialization. There is no object specified in the "FROM" clause of the query. Please update the query to include the object name in the "FROM" clause, like so:
If you find my answer helpful, please mark this as the best answer. Thanks!