function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
Aron Schor 24Aron 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;
    }
}
Prateek Prasoon 25Prateek Prasoon 25
Answer :-

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:
List<AggregateResult> results = [SELECT SUM(Opportunity_Amount__c) sumAmount, CustomFieldId__r.Property_Group_text__c propertyGroupValue                                      
FROM CustomObject__c                                     
WHERE CustomFieldId__c IN :Trigger.newMap.keySet() AND CustomFieldId__r.Property_Group_text__c IN :updatedPropertyGroups                                     
GROUP BY CustomFieldId__r.Property_Group_text__c];

If you find my answer helpful, please mark this as the best answer. Thanks!
Aron Schor 24Aron Schor 24
Hi Prateek, sorry, that should be CustomObject__c, it looks lke it was removed when I copied and modified from the actual API names.  Any other ideas?