0

I have a custom controller and visualforce page. The page loads fine with the SummaryList. When the user enters parameters, it should filter the list according to the runSearch method. I'm getting an error with runQuery2: illegal assignment from list to list. The database.query does not get an error if I create a separate list of type aggregateResult, and I use that list instead of SummaryList; but that is not going to work, because I can't query an aggregateResult type. How do I make this work?

Controller:

public with sharing class inspectionsDashboard {

    public List<AggregateResult> Result {get;set;}    
    public List<Summary> SummaryList {get;set;}
    public List<Account> AcctList {get;set;}
    public Map<Id, Account> IdAccount {get;set;}
    List<Id> Ids {get;set;}
    private String soql {get;set;}
    private String soql2 {get;set;}

    public String sortDir {
        get  { if (sortDir == null) {  sortDir = 'asc'; } return sortDir;  }
        set;
    }

    // the current field to sort by. defaults to last name
    public String sortField {
        get  { if (sortField == null) {sortField = 'Name'; } return sortField;  }
        set;
    }

    // format the soql for display on the visualforce page
    public String debugSoql {
        get { return soql + ' order by ' + sortField + ' ' + sortDir + ' limit 20'; }
        set;
    }

    public void toggleSort() {
        // simply toggle the direction
        sortDir = sortDir.equals('asc') ? 'desc' : 'asc';
        // run the query again
        runQuery();
    }

    public inspectionsDashboard() {
        SummaryList = new List<Summary>();
        Result = new List<AggregateResult>();
        Ids = new List<Id>();
        AcctList = new List<Account>();
        IdAccount = new Map<Id, Account>();
        soql = 'SELECT Name,BillingState FROM Account WHERE Id IN : Ids';

        Result = [SELECT Count(Id) Total, Sum(Home_Inspection_dollars__c) Dollars, Franchise__c 
                  FROM Inspection__c 
                  WHERE Month_Since_Launch__c > 0 AND Status__c = 'Completed' 
                  GROUP BY Franchise__c];

        for(AggregateResult a : Result) {
            Ids.add((Id)a.get('Franchise__c'));
        }

        System.debug('Account List' + AcctList);

        runQuery();

        for(Account a : AcctList) {
            IdAccount.put(a.Id, a);            
        }

        System.debug('Ids and Accounts are ' + IdAccount);

        for(AggregateResult a : Result) {
            Account TempAcct = new Account();            
            TempAcct = IdAccount.get((Id)(a.get('Franchise__c')));
            system.debug('Account Name is ' + TempAcct.Name);
            SummaryList.add(new Summary(a, TempAcct.Name, TempAcct.BillingState)); 

        }
    }


    public void runQuery() {

        try {
          AcctList = Database.query(soql + ' order by ' + sortField + ' ' + sortDir + ' limit 20');
        } catch (Exception e) {
          ApexPages.addMessage(new ApexPages.Message(ApexPages.Severity.ERROR, 'Ooops!'));
        }
    }

    public void runQuery2() {

        try {
          SummaryList = Database.query(soql2);
        } catch (Exception e) {
          ApexPages.addMessage(new ApexPages.Message(ApexPages.Severity.ERROR, 'Ooops!'));
        }
    }

    public class Summary {
        public Integer Total {get;set;}
        public Decimal Dollars {get;set;}
        public String AcctName {get;set;}
        public String StateProvince {get;set;}

        public Summary(AggregateResult a, String AccountName, String State) {
            Total =  (Integer)a.get('Total');
            Dollars = (Decimal)a.get('Dollars');
            AcctName = AccountName;
            StateProvince = State;
        }
    }

    public PageReference runSearch() {

        String AccountName = Apexpages.currentPage().getParameters().get('AccountName');
        String State = Apexpages.currentPage().getParameters().get('State');
        //soql = 'SELECT Name,BillingState FROM Account WHERE Id IN : Ids';
        soql2 = 'SELECT AcctName,StateProvince,Dollars,Total FROM Summary__c WHERE AcctName != null';

        List<String> li = new List<String>();
        if (!AccountName.equals(''))
          li.add(' AcctName LIKE \''+String.escapeSingleQuotes(AccountName)+'%\'');
        if (!State.equals(''))
          li.add(' StateProvince LIKE \''+String.escapeSingleQuotes(State)+'%\'');  
        String c = String.join( li, ' or ');
        if (c.length() > 0)
            c = ' and (' + c + ')';
        system.debug('condition: ' + c);
        soql2 += c;

        // run the query again

        runQuery2();

        return null;
    }
}

Visualforce Page:

<apex:page controller="inspectionsDashboard" sidebar="false">
    <apex:form >
    <apex:pageMessages id="errors" />

        <apex:pageBlock title="Find Me A Franchisee!" mode="edit">

          <table width="100%" border="0">
              <tr>  
                <td width="200" valign="top">

            <apex:pageBlock title="Parameters" mode="edit" id="criteria">

              <script type="text/javascript">
              function doSearch() {
                searchServer(
                  document.getElementById("AccountName").value,
                  document.getElementById("State").value,
                  );
              }
              </script> 

              <apex:actionFunction name="searchServer" action="{!runSearch}" rerender="results,debug,errors">
                  <apex:param name="AccountName" value="" />
                  <apex:param name="State" value="" />
              </apex:actionFunction>

              <table cellpadding="2" cellspacing="2">
              <tr>
                <td style="font-weight:bold;">Account Name<br/>
                <input type="text" id="AccountName" onkeyup="doSearch();"/>
                </td>
              </tr>
              <tr>
                <td style="font-weight:bold;">State/Province<br/>
                <input type="text" id="State" onkeyup="doSearch();"/>
                </td>
              </tr>
              </table>

              </apex:pageBlock>
              </td>
              <td valign="top">

        <apex:pageBlock mode="edit" id="results">
            <apex:pageblockTable value="{!SummaryList}" var="r"> 

                <apex:column >
                    <apex:facet name="header">
                        <apex:commandLink value="Account Name" action="{!toggleSort}" rerender="results,debug">
                            <apex:param name="sortField" value="{!r.AcctName}" assignTo="{!sortField}"/>
                        </apex:commandLink>
                    </apex:facet>
                    <apex:outputText value="{!r.AcctName}"/>
                </apex:column>
                <apex:column >
                    <apex:facet name="header">
                        <apex:commandLink value="State/Province" action="{!toggleSort}" rerender="results,debug">
                            <apex:param name="sortField" value="{!r.StateProvince}" assignTo="{!sortField}"/>
                        </apex:commandLink>
                    </apex:facet>
                    <apex:outputText value="{!r.StateProvince}"/>
                </apex:column>
                <apex:column >
                    <apex:facet name="header">
                        <apex:commandLink value="Number of Home Inspections" action="{!toggleSort}" rerender="results,debug">
                            <apex:param name="sortField" value="{!r.Total}" assignTo="{!sortField}"/>
                        </apex:commandLink>
                    </apex:facet>
                    <apex:outputText value="{!r.Total}"/>
                </apex:column>
                <apex:column >
                    <apex:facet name="header">
                        <apex:commandLink value="Home Inspection $" action="{!toggleSort}" rerender="results,debug">
                            <apex:param name="sortField" value="{!r.Dollars}" assignTo="{!sortField}"/>
                        </apex:commandLink>
                    </apex:facet>
                    <apex:outputText value="{!r.Dollars}"/>
                </apex:column>

            </apex:pageblockTable>
        </apex:pageBlock>
            </td>
            </tr>   
        </table>

        <apex:pageBlock title="Debug - SOQL" id="debug">
            <apex:outputText value="{!debugSoql}" />           
        </apex:pageBlock>

        </apex:pageBlock>
    </apex:form>
</apex:page>
2

You have to do the same thing you did originally; extract the resulting values, and convert them to a new List<Summary>:

SummaryList.clear();
for(AggregateResult result: (AggregateResult[])Database.query(soql2)) {
  ...
  SummaryList.add(new Summary(...));
}
  • Thanks. I tried that, but I got the error message: sObject type 'Summary__c' is not supported. If you are attempting to use a custom object, be sure to append the '__c' after the entity name. To be sure, the class Summary was defined in this controller, but it's not an object created in the org. – Sebastian Aug 8 '18 at 0:55
  • 1
    @Sebastian Correct. You need to create new Summary objects, not new Summary__c objects. – sfdcfox Aug 8 '18 at 0:56
  • I'm not sure I'm following. I change the query 'FROM Summary__c' to 'FROM Summary' but that doesn't work. What am I missing? – Sebastian Aug 8 '18 at 0:58
  • @Sebastian I'm not sure you expect'd soql2 to work; I should have looked at it closer. You need to do an aggregate result query, as near as I can tell. – sfdcfox Aug 8 '18 at 1:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.