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
queondagueroqueondaguero 

Trouble displaying aggregate results in visualforce page

I'm brand new to Apex and I'm having some trouble displaying aggregate results in a visualforce page. The extension isn't throwing any errors but the visualforce says "Error: Invalid field cv__Opportunity__c for SObject AggregateResult".

 

The way I read this is, either I'm not calling the fields properly in the visualforce page, or I'm not presenting the aggregate properly in the extension. Any help is of course greatly appreciated!

 

Here's the extension:

public class AccountDesignationHistoryExtension {
    
    Account CurrentAccount = null;
    List<AggregateResult> AccountDesignationHistory = null;
    
    public AccountDesignationHistoryExtension(ApexPages.StandardController controller) {
        CurrentAccount = (Account)controller.getRecord();
        AccountDesignationHistory = [SELECT COUNT(cv__Opportunity__c), Designation_Name__c, SUM(cv__Amount__c), MAX(Close_Date__c)
        From cv__Donation_Designation_Relationship__c
        WHERE cv__Opportunity__r.AccountId = :CurrentAccount.Id
        AND cv__Opportunity__r.StageName = 'Received'
        AND cv__Opportunity__r.cv__Posted__c = False
        GROUP BY Designation_Name__c];
    }
    
    public List<AggregateResult> getAccountDesignationHistory() { return AccountDesignationHistory; }
    
}

Here's the visualforce:

<apex:page standardController="Account" extensions="AccountDesignationHistoryExtension">
    <apex:pageBlock >
        <apex:pageBlockTable value="{!AccountDesignationHistory}" var="a">
            <apex:column value="{!a.cv__Opportunity__c}" headerValue="Number of Donations" />
            <apex:column value="{!a.Designation_Name__c}" headerValue="Designation Name" />
            <apex:column value="{!a.cv__Amount__c}" headerValue="Total Designated Amount" />
            <apex:column value="{!a.Close_Date__c}" headerValue="Most Recent Donation" />
        </apex:pageBlockTable>
    </apex:pageBlock>
</apex:page>

 

jackeejackee

Try testing the query in Force.com Explorer. Look's like some issue with your query.

 

 
queondagueroqueondaguero

Thanks but the query works fine. I built it and tested it in the Explorer. Obviously, I used a specific account Id instead of "CurrentAccount.Id", when testing the query.
icemft1976icemft1976

hmm...The error shows it is balking at the cv__Opportunity__c  field. This field has an aggregate function applied to it. I assume you need to either provide the field an alias or refer to it using the general notation for aggregated fields? 

 

(see more info here)   (<- 8/16 4:30: updated link to right section of developer docs...)

 

options:

 

1) provide the aggregated field an alias in the query & use that alias on the page

 

 AccountDesignationHistory = [SELECT COUNT(cv__Opportunity__c) myCount, Designation_Name__c, SUM(cv__Amount__c), MAX(Close_Date__c)

then do something like   <apex:column value="{!a.myCount}" headerValue="Number of Donations" />

 

2) Don't provide an alias and just use the default expression

 

<apex:column value="{!a.expr0}" headerValue="Number of Donations" />

 

I find option 1 more desirable because it provides a known handle to get a specific value, AND if you change the order of the aggregated fields in the query at some point in the future  you don't have to update the visualforce   (in case 'aggregated field #1 becomes the third aggregated field in the query....etc) - as the default expressions depend on the order of aggregation

 

let me know if that works!
queondagueroqueondaguero

I'd love to be able to use that solution, but it doesn't work either. I pasted the modified code snippets below. I'm still getting the "invalid field" error on the visualforce page, except now it's saying TotalGifts is an invalid field, instead of cv__Opportunity__c.

 

public class AccountDesignationHistoryExtension {
    
    Account CurrentAccount = null;
    List<AggregateResult> AccountDesignationHistory = null;
    
    public AccountDesignationHistoryExtension(ApexPages.StandardController controller) {
        CurrentAccount = (Account)controller.getRecord();
        AccountDesignationHistory = [SELECT COUNT(cv__Opportunity__c) TotalGifts, cv__Designation__r.Name DesignationName, SUM(cv__Amount__c) TotalAmountDonated, MAX(cv__Opportunity__r.CloseDate) LastGiftDate
        From cv__Donation_Designation_Relationship__c
        WHERE cv__Opportunity__r.AccountId = :CurrentAccount.Id
        AND cv__Opportunity__r.StageName = 'Received'
        AND cv__Opportunity__r.cv__Posted__c = False
        GROUP BY cv__Designation__r.Name];
    }
    
    public List<AggregateResult> getAccountDesignationHistory() { return AccountDesignationHistory; }
    
}

 

<apex:page standardController="Account" extensions="AccountDesignationHistoryExtension">
    <apex:pageBlock >
        <apex:pageBlockTable value="{!AccountDesignationHistory}" var="a">
            <apex:column value="{!a.TotalGifts}" headerValue="Number of Donations" />
            <apex:column value="{!a.DesignationName}" headerValue="Designation Name" />
            <apex:column value="{!a.TotalAmountDonated}" headerValue="Total Designated Amount" />
            <apex:column value="{!a.LastGiftDate}" headerValue="Most Recent Donation" />
        </apex:pageBlockTable>
    </apex:pageBlock>
</apex:page>

 

A former colleague, who is an Apex wizzard, suggested I take a hint from either Jeff Douglas or Scott Hemmeter. These may be some simple modifications, but I couldn't figure out what to do. Like I said, I'm brand new at this; no coding background whatsoever. Thanks for your reply.
swathitswathit

<apex:column value = "${!a['TotalGifts']}" /> should help ..
Rinkle Kapur 7Rinkle Kapur 7
Hi
First, give an alias name to all the columns that you are fetching 
AccountDesignationHistory = [SELECT 
               COUNT(cv__Opportunity__c) opportunityCount,
               Designation_Name__c DesigName,
               SUM(cv__Amount__c) totalAmount,
               MAX(Close_Date__c)  maxCloseDate From cv__Donation_Designation_Relationship__c WHERE cv__Opportunity__r.AccountId = :CurrentAccount.Id AND cv__Opportunity__r.StageName = 'Received' AND cv__Opportunity__r.cv__Posted__c = False GROUP BY Designation_Name__c];
Next, access them in the Visual force page as 
aggregatrResultObj['aliasName']

For example: 
<apex:page standardController="Account" extensions="AccountDesignationHistoryExtension">
    <apex:pageBlock >
        <apex:pageBlockTable value="{!AccountDesignationHistory}" var="a">
            <apex:column value="{!a['opportunityCount']}" headerValue="Number of Donations" />
            <apex:column value="{!a.['DesigName']}" headerValue="Designation Name" />
            <apex:column value="{!a.['totalAmount']}" headerValue="Total Designated Amount" />
            <apex:column value="{!a.['maxCloseDate']}" headerValue="Most Recent Donation" />
        </apex:pageBlockTable>
    </apex:pageBlock>
</apex:page>

Hope this helps :D
Cheers.
Hanna BlizniovaHanna Blizniova

Hi,
I have this problem

User-added image

Case_Product__c - my junction table(I have many-to-many between Product and Case).

It is my component names 'CasesListForBoss':

<apex:component controller="CasesController" access="global">
        <apex:dataTable value="{!ListCasesForBoss}" var="case">
               <apex:column >
                       <p>Case Number: {!case['CaseNumber']}
                        Owner: {!case['OwnerFirstName']} {!case['OwnerLastName']}
                        Status: {!case['CaseStatus']} Product:{!case['ProductName']}</p>
              </apex:column>
         </apex:dataTable>
</apex:component>

CasesController:
public class CasesController {
public List<AggregateResult> getListCasesForBoss() {
    List<AggregateResult> results = Database.query('SELECT Product__r.Name ProductName,'+
                                                           'Case__r.CaseNumber CaseNumber,'+
                                                           'Case__r.Status CaseStatus,'+
                                                           'Owner.FirstName OwnerFirstName,'+
                                                           'Owner.LastName OwnerLastName'+
                                                           'FROM Case_Product__c WHERE CreatedDate = LAST_N_DAYS:7');
    return results;
}
}

Email template:
<messaging:emailTemplate subject="Case_Product__c" recipientType="User" relatedToType="Case_Product__c">
<messaging:htmlEmailBody >
        <p>As you requested, here is a list of all our cases</p>
        <c:CasesListForBoss />
        <p>Enjoy!</p>
    </messaging:htmlEmailBody>
</messaging:emailTemplate>

Please,help me