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
wedaftwedaft 

Trouble with AggregateResult list

Hi everyone,

 

I have a question about my Apex controller below. I'm having major problems getting my AggregatedResults list to work. Ultimately, I want a list that I can reference in a custom visualforce component. I am an admin, not a developer, so I am sure that I've messed up something that is pretty simple. I don't get any errors or anything, it's just that nothing shows up in the visualforce email template where data from this list should be displayed. The last 10-15 lines of code are the ones of interest. Any help is very much appreciated! Thanks!

 

public class TeacherlistatSchoolX2{


    public Id thisContactId {get;set;} 
    private List<Teacher_Implementation__c> Teacherlist;
    private List<Class_Implementation__c> Classlist;
    private List<AggregateResult> Metriclist;
    
    public List<Teacher_Implementation__c> getTeacherlist() {
        if (Teacherlist == null) {
            Teacherlist = 
            [SELECT Id, Name, School_Implementation__r.Name, School_Implementation__r.Principal_Name__c, 
            School_Implementation__r.Id FROM Teacher_Implementation__c 
            WHERE School_Implementation__r.Principal_Name__c = :thiscontactid];
        }
        return Teacherlist;
    }
    
    public List<Class_Implementation__c> getClasslist() {
        if (Classlist == null) {
            Classlist = [SELECT Id, Name, Teacher_Implementation__r.Name, 
            Teacher_Implementation__r.Id, Grade__c
            FROM Class_Implementation__c 
            WHERE Teacher_Implementation__r.id IN :getTeacherlist()];
        }
        return Classlist;
    }
  
    public List<AggregateResult> getMetriclist() {
    if (Metriclist == null) {
    AggregateResult[] Metriclist =
        [SELECT AVG(A__c), AVG(of_scheduled_time_online__c), AVG(Hours_Online__c), Start_Date__c,
        MIN(Class_Implementation__r.Name), MIN(Class_Implementation__r.Id), MIN(Class_Implementation__r.Date_of_previous_admin_email__c), MIN(class_grade__c)
        FROM Class_Metric__c
        WHERE Class_Implementation__r.Id IN :getClasslist()
        GROUP BY Start_Date__c];
    }
        return Metriclist;
    }
}

 

Best Answer chosen by Admin (Salesforce Developers) 
BritishBoyinDCBritishBoyinDC

Trying adding the constructor back into the code, but with the call commented out

 

//Initiate in constructor
    public TeacherlistatSchoolX2 () {
    //getMetriclist();
    }

 

All Answers

BritishBoyinDCBritishBoyinDC

So I don't think you can't directly put Aggregate Result lists on a page like that - you have to convert the results back to something VisualForce can iterate over - either a list of custom objects, or a list of a custom class objects you've created...

 

See the docs here for a simple example:

http://www.salesforce.com/us/developer/docs/apexcode/Content/langCon_apex_SOQL_agg_fns.htm

 

AggregateResult[] groupedResults
  = [SELECT CampaignId, AVG(Amount)
      FROM Opportunity
      GROUP BY CampaignId];
for (AggregateResult ar : groupedResults)  {
    System.debug('Campaign ID' + ar.get('CampaignId'));
    System.debug('Average amount' + ar.get('expr0'));
}

 

In the same way, assuming you are expecting more than one result, you would need loop through the result list (MetricList) and use the same syntax to extract the results back into data you can display and add them to a list of something you can add to the page...bearing in mind each value in the result is itself an object, so you often need to 'cast' the expression back into a Decimal/String/Date...

 

Aggregrate results are very powerful, but can be tricky to ulitilize in this scenario...so if what I am saying doesn't make sense, I'll write a more detailed example tomorrow... 

 

 

BritishBoyinDCBritishBoyinDC

Here's a quick example, using Campaign as the source object for the metrics.

 

I created four campaigs, two ending on one date, the other two on a later date, and then set some expected revenue and expected response for each campaign. If I wanted to display the results on page/in an email template, this is the appraoch I would use (and it worked okay for me just now)

 

public with sharing class pncCampaign {

public List<pagemetrics> pm {get;set;}

//Initiate in Constructor
public pncCampaign () {
getMetriclist();
}

//new Class to hold metrics
public class pagemetrics {
public Date sDate {get;set;}
public Double ExpRevenue {get;set;}
public Decimal ExpResponse {get;set;}

public pagemetrics (Date idate, Double er, Decimal eresp) {
sDate = idate;
ExpRevenue = er;
ExpResponse = eresp;
}
}

//Generate metrics on Campaign
public void getMetriclist() {

//Create new list to hold results based on new class
pm = new List<pagemetrics>();

AggregateResult[] Metriclist =
        [SELECT AVG(ExpectedRevenue) er, 
        StartDate,
        MIN(ExpectedResponse) bc
        FROM Campaign
        WHERE NumberOfContacts > 0
        GROUP BY StartDate];
    //Loop through results and add each result to new class so we can display    
    for (AggregateResult ar : Metriclist )  {
    pm.add(new pagemetrics ((Date)ar.get('StartDate'), (Double)ar.get('er'), (Decimal)ar.get('bc')));
}
}   

}

 And the the component loops through the list of pagemetrics records (our new class) and displays them in table:

 

<apex:component access="global" Controller="pncCampaign" >
 
 <apex:dataTable value="{!pm}" var="m">
 <apex:column value="{!m.sDate}"/>
 <apex:column value="{!m.ExpRevenue}"/>
 <apex:column value="{!m.ExpResponse}"/>
 </apex:dataTable>
 
</apex:component>

 With Aggregrate results, the casting back of the results is critical - so to reference the avg for Expected Response, I have to explicitly say I want to get back the data type - in this case a double, by using this syntax:

(Double)ar.get('er'),

 

The 'er' is just an alias from the SOQL - if you don't do that, SF will supply one for each summary value (as it does in the example from the docs I posted)

 

So you can use this method to add as many summary fields as you want - just extend the page metrics class, and then include the additional metric from the results in the pagemetric creation process...

 

Hope that helps!

wedaftwedaft

Thanks for the tip! I will try these changes this morning

wedaftwedaft

Hey,

 

So I tried making the modifications, but I'm running into some errors. Specifically, when I try to save my component I get the error: Unknown property 'String.Class_Grade'. Also, I am uncertain about which instances I should be appending the "__c" to class grade and which instances I should not. Thank you for your help so far!

 

Controller:

public with sharing class TeacherlistatSchoolX2{

    public List<pagemetrics> pm {get;set;}
    public Id thisContactId {get;set;}

    
    //Initiate in constructor
    public TeacherlistatSchoolX2 () {
    getMetriclist();
    }
    
    //new Class to hold metrics
    public class pagemetrics {
    public integer class_grade {get;set;}
    
    public pagemetrics (integer CG) {
    class_grade = CG;
    }
    }
    
    public List<Teacher_Implementation__c> Teacherlist;
    public List<Class_Implementation__c> Classlist;
    
    public List<Teacher_Implementation__c> getTeacherlist() {
        if (Teacherlist == null) {
            Teacherlist = 
            [SELECT Id, Name, School_Implementation__r.Name, School_Implementation__r.Principal_Name__c, 
            School_Implementation__r.Id FROM Teacher_Implementation__c 
            WHERE School_Implementation__r.Principal_Name__c = :thiscontactid];
        }
        return Teacherlist;
    }
    
    public List<Class_Implementation__c> getClasslist() {
        if (Classlist == null) {
            Classlist = [SELECT Id, Name, Teacher_Implementation__r.Name, 
            Teacher_Implementation__r.Id, Grade__c
            FROM Class_Implementation__c 
            WHERE Teacher_Implementation__r.id IN :getTeacherlist()];
        }
        return Classlist;
    }
 
 
//Generate metrics
public void getMetriclist(){ 

//Create new list to hold results based on new class
pm = new List<pagemetrics>();

    AggregateResult[] Metriclist =
        [SELECT MIN(Class_Implementation__r.Id), Start_Date__c, MIN(class_grade__c) CG
        FROM Class_Metric__c
        WHERE Class_Implementation__r.Id IN :getClasslist()
        GROUP BY Start_Date__c];
    
    //Loop through results and add each result to new class so we can display
    for (AggregateResult ar : Metriclist) {
    pm.add(new pagemetrics ((Integer)ar.get('CG')));
    }

    }
}

 

Component:

<apex:component controller="TeacherlistatSchoolX2" access="global">
  <apex:attribute name="ContactID" description="This is the contact Id." type="Id" assignTo="{!thisContactId}"/>

[ %A ] - [ % of scheduled time online ] - [ Hours Online ]
<apex:repeat var="cx" value="{!pm}">
[ {!cx.Class_Grade__c} ] [{!cx.A__c}]
</apex:repeat>

</apex:component>

 

 

BritishBoyinDCBritishBoyinDC

So the fields you are referencing here:

<apex:repeat var="cx" value="{!pm}">
[ {!cx.Class_Grade__c} ] [{!cx.A__c}]
</apex:repeat>

 need to be the fields in your new class, not the fields on the original custom object. So you need to add a field to store the value you are calling 'A'...I created one called avalue, and I am just defaulting it to 0, but you can pass it in like you are doing with class grade

 

pagemetrics { 

public integer class_grade {get;set;} 
public integer avalue {get;set;} public pagemetrics (integer CG) { class_grade = CG; avalue= 0; } }

 And then your repeat tag on the page would need to look like this:

<apex:repeat var="cx" value="{!pm}"> 

[ {!cx.Class_Grade} ] [{!cx.avalue}] 

</apex:repeat> 

 

wedaftwedaft

Okay, I feel like I'm getting close. I'm getting this error when I try to save the controller, though (line 66 is the one at the very bottom, "pm.add..."): Error: Compile Error: Constructor not defined: [TeacherlistatSchoolX2.pagemetrics].<Constructor>(Integer, Integer, Decimal, Integer) at line 66 column 12

 

public with sharing class TeacherlistatSchoolX2{

    public List<pagemetrics> pm {get;set;}
    public Id thisContactId {get;set;}

    
    //Initiate in constructor
    public TeacherlistatSchoolX2 () {
    getMetriclist();
    }
    
    //new Class to hold metrics
    public class pagemetrics {
    public integer class_grade {get;set;}
    public integer A {get;set;}
    public integer of_scheduled_time_online {get;set;}
    public decimal hours_online {get;set;}
    
    public pagemetrics (integer CG, integer perA, integer TO, decimal HO) {
    class_grade = CG;
    A = perA;
    of_scheduled_time_online = TO;
    hours_online = HO;
    
    }
    }
    
    public List<Teacher_Implementation__c> Teacherlist;
    public List<Class_Implementation__c> Classlist;
    
    public List<Teacher_Implementation__c> getTeacherlist() {
        if (Teacherlist == null) {
            Teacherlist = 
            [SELECT Id, Name, School_Implementation__r.Name, School_Implementation__r.Principal_Name__c, 
            School_Implementation__r.Id FROM Teacher_Implementation__c 
            WHERE School_Implementation__r.Principal_Name__c = :thiscontactid];
        }
        return Teacherlist;
    }
    
    public List<Class_Implementation__c> getClasslist() {
        if (Classlist == null) {
            Classlist = [SELECT Id, Name, Teacher_Implementation__r.Name, 
            Teacher_Implementation__r.Id, Grade__c
            FROM Class_Implementation__c 
            WHERE Teacher_Implementation__r.id IN :getTeacherlist()];
        }
        return Classlist;
    }
 
 
//Generate metrics
public void getMetriclist(){ 

//Create new list to hold results based on new class
pm = new List<pagemetrics>();

    AggregateResult[] Metriclist =
        [SELECT MIN(date_difference__c), MIN(Class_Implementation__r.Id), AVG(A__c) perA, Start_Date__c, MIN(class_grade__c) CG
        FROM Class_Metric__c
        WHERE (date_difference__c >= 0) AND (Class_Implementation__r.Id IN :getClasslist())
        GROUP BY Start_Date__c];
    
    //Loop through results and add each result to new class so we can display
    for (AggregateResult ar : Metriclist) {
    pm.add(new pagemetrics ((Integer)ar.get('CG'), (Integer)ar.get('TO'), (Decimal)ar.get('HO'), (Integer)ar.get('perA')));
    }

    }
}

 

BritishBoyinDCBritishBoyinDC

So I think this line is causing you the issue:

 

pm.add(new pagemetrics ((Integer)ar.get('CG'), (Integer)ar.get('TO'), (Decimal)ar.get('HO'), (Integer)ar.get('perA'))); } } }

 But the class constructor is expecting the values in a different order:

public pagemetrics (integer CG, integer perA, integer TO, decimal HO) 

 The order is important...so if you change the problem line to this it should work:

pm.add(new pagemetrics ((Integer)ar.get('CG'), (Integer)ar.get('perA'), (Integer)ar.get('TO'), (Decimal)ar.get('HO') )); } } }

 

wedaftwedaft

Hmm, the controller saves with no errors now, and the component seems to accept it alright, but when I actually test everything out by sending a test visualforce template, the data that should be displayed isn't displayed, it's just blank. I know the problem isn't with the visualforce template. Any ideas?

BritishBoyinDCBritishBoyinDC

Can you post the component code as well...

 

if you do a debug, does the pagemetrics class have data in it after the processing is complete?

wedaftwedaft

I can't use execute annonymous to get the debug because when I try to execute the code I get this error: "line 13, column 11: unexpected token: 'class'". This is despite the fact that it lets me save the controller in my browser with no errors given.

 

Controller:

public with sharing class TeacherlistatSchoolX2{

    public List<pagemetrics> pm {get;set;}
    public Id thisContactId {get;set;}

    
    //Initiate in constructor
    public TeacherlistatSchoolX2 () {
    getMetriclist();
    }
    
    //new Class to hold metrics
    public class pagemetrics {
    public integer class_grade {get;set;}
    public integer A {get;set;}
    public integer of_scheduled_time_online {get;set;}
    public decimal hours_online {get;set;}
    
    public pagemetrics (integer CG, integer perA, integer TO, decimal HO) {
    class_grade = CG;
    A = perA;
    of_scheduled_time_online = TO;
    hours_online = HO;
    
    }
    }
    
    public List<Teacher_Implementation__c> Teacherlist;
    public List<Class_Implementation__c> Classlist;
    
    public List<Teacher_Implementation__c> getTeacherlist() {
        if (Teacherlist == null) {
            Teacherlist = 
            [SELECT Id, Name, School_Implementation__r.Name, School_Implementation__r.Principal_Name__c, 
            School_Implementation__r.Id FROM Teacher_Implementation__c 
            WHERE School_Implementation__r.Principal_Name__c = :thiscontactid];
        }
        return Teacherlist;
    }
    
    public List<Class_Implementation__c> getClasslist() {
        if (Classlist == null) {
            Classlist = [SELECT Id, Name, Teacher_Implementation__r.Name, 
            Teacher_Implementation__r.Id, Grade__c
            FROM Class_Implementation__c 
            WHERE Teacher_Implementation__r.id IN :getTeacherlist()];
        }
        return Classlist;
    }
 
 
//Generate metrics
public void getMetriclist(){ 

//Create new list to hold results based on new class
pm = new List<pagemetrics>();

    AggregateResult[] Metriclist =
        [SELECT MIN(date_difference__c), MIN(Class_Implementation__r.Id), AVG(A__c) perA, Start_Date__c, MIN(class_grade__c) CG
        FROM Class_Metric__c
        WHERE (date_difference__c >= 0) AND (Class_Implementation__r.Id IN :getClasslist())
        GROUP BY Start_Date__c];
    
    //Loop through results and add each result to new class so we can display
    for (AggregateResult ar : Metriclist) {
    pm.add(new pagemetrics ((Integer)ar.get('CG'), (Integer)ar.get('perA'), (Integer)ar.get('TO'), (Decimal)ar.get('HO')));
    }

    }
    
}

 

Component:

<apex:component controller="TeacherlistatSchoolX2" access="global">
  <apex:attribute name="ContactID" description="This is the contact Id." type="Id" assignTo="{!thisContactId}"/>

[ %A ] - [ % of scheduled time online ] - [ Hours Online ]
<apex:repeat var="cx" value="{!pm}">
[ {!cx.Class_Grade} ] [{!cx.A}]
</apex:repeat>

</apex:component>

 

 

Email Template:

<messaging:emailTemplate recipientType="Contact"
    relatedToType="School_Implementation__c"
    subject="Update on your teacher implementations" 
    replyTo="trevor.gill@reasoningmind.org">
       
<messaging:plainTextEmailBody >
Dear {!recipient.salutation} {!recipient.lastname},

<c:CL_controller ContactID="{!recipient.Id}"/>

</messaging:plainTextEmailBody>   
</messaging:emailTemplate>

 

BritishBoyinDCBritishBoyinDC

Okay - looks like it was an issue of timing - by firing the request in the constructor, it was firing before the ContactId variable had been set, so produced no results...

 

I changed my code a bit to change the timing, and it worked, so if you update your class to use the code below, it should now hopefully work:

 

Re the debug, you can add a debug statement in the code (I added one below) and it will appear under Monitor - Debug Log if you set a log for the current user - that way you can run the code and then search the log for the debug statement and see what was happening at that moment. You can also use the Developer Console, but that is a bit more involved.

 

public with sharing class TeacherlistatSchoolX2{

    public List<pagemetrics> pm;
    public Id thisContactId {get;set;}

    
	public List<pagemetrics> getpm () {
	if (pm == null) {
	pm = new List<pagemetrics> ();
	getMetriclist();
	}
system.debug('Current PM List' + pm); return pm; } //new Class to hold metrics public class pagemetrics { public integer class_grade {get;set;} public integer A {get;set;} public integer of_scheduled_time_online {get;set;} public decimal hours_online {get;set;} public pagemetrics (integer CG, integer perA, integer TO, decimal HO) { class_grade = CG; A = perA; of_scheduled_time_online = TO; hours_online = HO; } } public List<Teacher_Implementation__c> Teacherlist; public List<Class_Implementation__c> Classlist; public List<Teacher_Implementation__c> getTeacherlist() { if (Teacherlist == null) { Teacherlist = [SELECT Id, Name, School_Implementation__r.Name, School_Implementation__r.Principal_Name__c, School_Implementation__r.Id FROM Teacher_Implementation__c WHERE School_Implementation__r.Principal_Name__c = :thiscontactid]; } return Teacherlist; } public List<Class_Implementation__c> getClasslist() { if (Classlist == null) { Classlist = [SELECT Id, Name, Teacher_Implementation__r.Name, Teacher_Implementation__r.Id, Grade__c FROM Class_Implementation__c WHERE Teacher_Implementation__r.id IN :getTeacherlist()]; } return Classlist; } //Generate metrics public void getMetriclist(){ AggregateResult[] Metriclist = [SELECT MIN(date_difference__c), MIN(Class_Implementation__r.Id), AVG(A__c) perA, Start_Date__c, MIN(class_grade__c) CG FROM Class_Metric__c WHERE (date_difference__c >= 0) AND (Class_Implementation__r.Id IN :getClasslist()) GROUP BY Start_Date__c]; //Loop through results and add each result to new class so we can display for (AggregateResult ar : Metriclist) { pm.add(new pagemetrics ((Integer)ar.get('CG'), (Integer)ar.get('perA'), (Integer)ar.get('TO'), (Decimal)ar.get('HO'))); } } }

 

wedaftwedaft

Thanks again for all your help on this. When I put in the code you attached I get this:  Error: Compile Error: The method object <Constructor>() is referenced by Visualforce Component (CL_controller) in salesforce.com. Remove the usage and try again. at line 8 column 12 

BritishBoyinDCBritishBoyinDC

Trying adding the constructor back into the code, but with the call commented out

 

//Initiate in constructor
    public TeacherlistatSchoolX2 () {
    //getMetriclist();
    }

 

This was selected as the best answer
wedaftwedaft

Huzzah, it works! Thanks so much for your help with this! You saved my life--I was just getting ready to jump off my balcony.

BritishBoyinDCBritishBoyinDC

Excellent!

 

As folks who work with me know, I am a big fan of Aggregrate Queries, but they can be tricky to get started with - but hopefully you'll find them as useful as I know do!