function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Christina TomaselliChristina Tomaselli 

Apex Trigger using SOQL Totals Query

Hi,

I am trying to create a summary record on a related object using a SOQL totals query and an apex trigger. I am running into 2 issues I am having trouble referencing the summed fields from within my totals query and my trigger doesn't seem to be doing the upsert based on the external ID field Rollup_ID__c which matches in both objects. Here is my Trigger: 
trigger MeasureUpsert on Contract_Practice_Junction__c (after insert) 
{ 
    //Run totals query to aggregate all Contract_Practice_Junction__c records to get the sum of all numerators and denominators
    for (Contract_Practice_Junction__c measure : [SELECT Rollup_ID__c, rate_type__c, Health_Plan_Contract__c, Measure__c, Run_As_Of_Date__c, 
                                                  sum(Count_in_Denominator__c), sum(Count_in_Numerator__c)
                                                  FROM Contract_Practice_Junction__c GROUP BY Rollup_ID__c, rate_type__c, Health_Plan_Contract__c, Measure__c, Run_As_Of_Date__c])
    //use a for loop to run through all the aggregate records and upsert values to the 
    {
        //for each measure in the for loop, upsert a new Contract_Quality_Performance__c record with the values of the totals query using Rollup_ID__c as the external ID
        Contract_Quality_Performance__c CQP = new Contract_Quality_Performance__c();
            CQP.Rollup_ID__c            = measure.Rollup_ID__c;
            CQP.Rate_Type__c            = measure.rate_type__c;
            CQP.Measure__c              = measure.Measure__c;
            CQP.Health_Plan_Contract__c = measure.Health_Plan_Contract__c;
            CQP.Run_Date__c                = measure.Run_As_Of_Date__c;
            CQP.Denominator__c            = measure.sum(Count_in_Denominator__c);
            CQP.Numerator__c            = measure.sum(Count_in_Numerator__c);
            upsert CQP;
    }
}

Thank you in advance for any help/suggestions. 
Best Answer chosen by Christina Tomaselli
David Zhu 🔥David Zhu 🔥
SOQL Aggregate function return a list of AggregateResult data type. You may refer the code below. Some of the field casting might not match your actual field data type. You need to change it accordingly.
  
trigger MeasureUpsert on Contract_Practice_Junction__c (after insert) 
{ 
    List<Contract_Quality_Performance__c> cqps = new List<Contract_Quality_Performance__c>();
    //Run totals query to aggregate all Contract_Practice_Junction__c records to get the sum of all numerators and denominators
    for (AggregateResult  measure : [SELECT Rollup_ID__c, rate_type__c, Health_Plan_Contract__c, Measure__c, Run_As_Of_Date__c, 
                                                  sum(Count_in_Denominator__c)sumCount1, sum(Count_in_Numerator__c)sumCount2 
                                                  FROM Contract_Practice_Junction__c Where Id in :trigger.newMap.Keyset() 
                                                  GROUP BY Rollup_ID__c, rate_type__c, Health_Plan_Contract__c, Measure__c, Run_As_Of_Date__c])
    //use a for loop to run through all the aggregate records and upsert values to the 
    {
        //for each measure in the for loop, upsert a new Contract_Quality_Performance__c record with the values of the totals query using Rollup_ID__c as the external ID
        Contract_Quality_Performance__c CQP = new Contract_Quality_Performance__c();
            CQP.Rollup_ID__c            = string.valueOf(measure.get('Rollup_ID__c'));
            CQP.Rate_Type__c            = string.valueOf(measure.get('rate_type__c')); 
            CQP.Measure__c              = string.valueOf(measure.get('Measure__c'));  
            CQP.Health_Plan_Contract__c = string.valueOf(measure.get('Health_Plan_Contract__c'));   
            CQP.Run_Date__c                = date.valueOf(measure.get('Run_As_Of_Date__c'));    
            CQP.Denominator__c            = decimal.valueOf(measure.get('sumCount1')); 
            CQP.Numerator__c            = decimal.valueOf(measure.get('sumCount2'));  
            cqps.add(CQP);
    }
    upsert cqps;
}

 

All Answers

David Zhu 🔥David Zhu 🔥
SOQL Aggregate function return a list of AggregateResult data type. You may refer the code below. Some of the field casting might not match your actual field data type. You need to change it accordingly.
  
trigger MeasureUpsert on Contract_Practice_Junction__c (after insert) 
{ 
    List<Contract_Quality_Performance__c> cqps = new List<Contract_Quality_Performance__c>();
    //Run totals query to aggregate all Contract_Practice_Junction__c records to get the sum of all numerators and denominators
    for (AggregateResult  measure : [SELECT Rollup_ID__c, rate_type__c, Health_Plan_Contract__c, Measure__c, Run_As_Of_Date__c, 
                                                  sum(Count_in_Denominator__c)sumCount1, sum(Count_in_Numerator__c)sumCount2 
                                                  FROM Contract_Practice_Junction__c Where Id in :trigger.newMap.Keyset() 
                                                  GROUP BY Rollup_ID__c, rate_type__c, Health_Plan_Contract__c, Measure__c, Run_As_Of_Date__c])
    //use a for loop to run through all the aggregate records and upsert values to the 
    {
        //for each measure in the for loop, upsert a new Contract_Quality_Performance__c record with the values of the totals query using Rollup_ID__c as the external ID
        Contract_Quality_Performance__c CQP = new Contract_Quality_Performance__c();
            CQP.Rollup_ID__c            = string.valueOf(measure.get('Rollup_ID__c'));
            CQP.Rate_Type__c            = string.valueOf(measure.get('rate_type__c')); 
            CQP.Measure__c              = string.valueOf(measure.get('Measure__c'));  
            CQP.Health_Plan_Contract__c = string.valueOf(measure.get('Health_Plan_Contract__c'));   
            CQP.Run_Date__c                = date.valueOf(measure.get('Run_As_Of_Date__c'));    
            CQP.Denominator__c            = decimal.valueOf(measure.get('sumCount1')); 
            CQP.Numerator__c            = decimal.valueOf(measure.get('sumCount2'));  
            cqps.add(CQP);
    }
    upsert cqps;
}

 
This was selected as the best answer
Christina TomaselliChristina Tomaselli
Thanks, that worked, but I would like to change the query to reference the Rollup_ID__c in the where clause instead of the record ID. Can I do something like: 

    for (AggregateResult measure : [SELECT Rollup_ID__c, rate_type__c, Health_Plan_Contract__c, Measure__c, Run_As_Of_Date__c, 
                                                  sum(Count_in_Denominator__c)sumCount1, sum(Count_in_Numerator__c)sumCount2
                                                  FROM Contract_Practice_Junction__c where Rollup_ID__c in record.rollup_ID__c
                                                  GROUP BY Rollup_ID__c, rate_type__c, Health_Plan_Contract__c, Measure__c, Run_As_Of_Date__c]) 
David Zhu 🔥David Zhu 🔥
Then you need to add lines before for loop and set Roolup_id__c list in the soql.

......................
List<Id> recordIds = new LIst<Id>();

for (Contract_Practice_Junction__c  contract : trigger.New) {
     recordIds .add(contract.rollup_ID__c);
}

for (AggregateResult measure : [SELECT Rollup_ID__c, rate_type__c, Health_Plan_Contract__c, Measure__c, Run_As_Of_Date__c, 
                                                  sum(Count_in_Denominator__c)sumCount1, sum(Count_in_Numerator__c)sumCount2
                                                  FROM Contract_Practice_Junction__c where Rollup_ID__c in recordIds
                                                  GROUP BY Rollup_ID__c, rate_type__c, Health_Plan_Contract__c, Measure__c, Run_As_Of_Date__c]) 



...................
Christina TomaselliChristina Tomaselli
Thank you so much for all of your help! This works like a charm. Have a great day.