+ Start a Discussion
Jim MontgomeryJim Montgomery 

Aggregate query has too many rows for direct assignment use for loop listing activities custom controller

Here is my code.
Not quite sure how to use a for loop in this.
public class AccExtensions{
      transient Public List<Account> AHS{get;set;}
             
                 public AccExtensions(ApexPages.StandardController controller) {
          AHS=[select id,(select id,ActivityDate,short_notes__c,OwnerId, Owner.name,Activity_Type__c,description,subject,Who.Id,Who.Name from ActivityHistories where (owner.id<>'005i0000001gIQJAA2' and owner.id<>'00531000008bhlRAAQ') order by activitydate DESC ) from account where ID=:ApexPages.currentPage().getParameters().get('id') ];
                     
          System.Debug('AHS: ' + AHS.get(0));
        System.Debug('Activities: ' + AHS.get(0).ActivityHistories);
        
}

}
Abdul KhatriAbdul Khatri
Like this
 
public class AccExtensions{
 
    transient Public List<Account> AHS { get; set; }
             
    public AccExtensions(ApexPages.StandardController controller) {

        AHS = [select id,
               	(select id,ActivityDate,short_notes__c,OwnerId, Owner.name,Activity_Type__c,description,subject,Who.Id,Who.Name 
                 from ActivityHistories 
                 where (owner.id<>'005i0000001gIQJAA2' and owner.id<>'00531000008bhlRAAQ') order by activitydate DESC ) 
               from account 
               where ID=:ApexPages.currentPage().getParameters().get('id') ];
                     
        System.Debug('AHS: ' + AHS.get(0));
        System.Debug('Activities: ' + AHS.get(0).ActivityHistories);
        
        for(Account account : AHS) {
            
            if(account.ActivityHistories == null) continue;
            
            for(ActivityHistory AH : account.ActivityHistories) {
                
                //CODE
            }
            
        }
        
    }
}

 
Abdul KhatriAbdul Khatri
One suggestion Do not hard code the Ids because the code has a potential to fail in other Orgs as they may differ so it's better to either use the Name like
owner.Name <> 'test'

Or use the Custom Settings. 
Abdul KhatriAbdul Khatri
Hey Steven, Have you really tried your code to see if it works. I have a doubt. Can you please confirm?

I think the Jim approach is find he just need a kick up to get going which I provided.

I don't think your solution will work
Steven NsubugaSteven Nsubuga
Hi Abdul, I was inspired by https://opfocus.com/aggregate-query-has-too-many-rows-for-direct-assignment-use-for-loop/ where a similar problem was addressed.
Abdul KhatriAbdul Khatri
Your approach was right but you chosen the wrong object. You can run SOQL on ActivityHistories as there is no such object and even if you use ActivityHistory you would have issues. 

if it would have been a different SObject Like Account, Contact etc. Yes that approach would have worked as charm.

Please don't take me wrong, just trying to clear my view. Thanks for listening.
Steven NsubugaSteven Nsubuga
Hi Abdul, it is ok. I should have checked earlier, you are right, it is impossible to run SOQL on ActivityHistory, and ActivityHistories does not exist.
Thanks for the corrections!!
Jim MontgomeryJim Montgomery
I tried the first solution and still getting the error.
Abdul KhatriAbdul Khatri
What is the error you are getting?
Abdul KhatriAbdul Khatri
I didn't give you the full code except how to iterate, what you asked?
Jim MontgomeryJim Montgomery
All the controller does is retrieve a list of activity histories that I display in a VF page that allowes filtering and searching.
Once it hits an account with more that 700 histories, it errors out.

Aggregate query has too many rows for direct assignment use for loop
Abdul KhatriAbdul Khatri
BTW, There is an out of box Activity History Related List

Is there any specific reason to go through this route?
Jim MontgomeryJim Montgomery
The custom one I created allows for searching, print, export to excel etc.
It also via the controller weeds out a lot of automated Pardot junk.
Would you like to see that code?
Abdul KhatriAbdul Khatri
That should be find if you can
Jim MontgomeryJim Montgomery
Here is the VF page code. I just need the controller code not to die at 700 entries.
This is fully searchable, and sortable. Just embed this in a section on the account page layout.

<apex:page standardController="Account" extensions="AccExtensions" > <style> .filterMatches{ background-color: #BFFF00; } .tertiaryPalette{ color: #000 !important; } .dt-buttons{ margin-left: 10px; } </style> <div> <apex:OutputLink value="/apex/LogContactCall?id={!account.id}" target="_Blank"> <apex:image id="LogContactCall" value="{!$Resource.LogaCallButton}" /> </apex:OutputLink> &nbsp;&nbsp; <apex:OutputLink value="/_ui/core/email/author/EmailAuthor?p2_lkid={!Account.Id}&rtype=003&p3_lkid={!Account.Id}&rtype=003retURL=%2F{!Account.Id}" Target="_blank"> <apex:image id="SendEmail" value="{!$Resource.SendEmailButton}" /> </apex:OutputLink> </div> <apex:form > <div style="overflow:scroll; height:1180px;"> <apex:pageBlock > <apex:repeat value="{!AHS}" var="acct"> <apex:pageblockTable value="{!acct.ActivityHistories}" var="activity" html-cid="activitiesTable" style="width:100%;height:1080px;"> <apex:Column headervalue="" style="width:5%;"> <apex:outputLink target="_blank" value="/{!activity.id}"> <apex:outputText style="color:blue;" Value="Sel"/> </apex:OutputLink> </apex:column> <apex:Column headervalue="Date" style="width:7%;" > <apex:OutputText value="{0,date,MM'/'dd'/'yyyy}"><apex:param value="{!Activity.ActivityDate}" /></apex:outputText> </apex:column> <apex:column headervalue="Name" style="width:7%;"> <apex:OutputLink value="/{!Activity.WhoId}" target="_Blank"> <apex:OutputText value="{!Activity.Who.Name}"/> </apex:OutputLink> </apex:Column> <apex:Column headervalue="Subject" style="width:7%;" value="{!activity.Subject}"/> <apex:Column headervalue="Description" style="width:50%;line-height:13px;lines:8;" value="{!activity.Short_Notes__c}"/> <apex:Column HeaderValue="User" style="width:10%;" value="{!activity.Owner.Name}"/> </apex:pageblockTable> </apex:repeat> </apex:pageBlock> </div> <!-- Jquery --> <apex:includeScript value="//code.jquery.com/jquery-1.11.3.min.js"/> <apex:stylesheet value="//ajax.googleapis.com/ajax/libs/jqueryui/1.11.4/themes/smoothness/jquery-ui.css"/> <!-- DataTable --> <apex:includeScript value="//cdn.datatables.net/1.10.9/js/jquery.dataTables.min.js"/> <apex:stylesheet value="//cdn.datatables.net/1.10.9/css/jquery.dataTables.min.css"/> <!-- Search Highlight --> <apex:includeScript value="//bartaz.github.io/sandbox.js/jquery.highlight.js"/> <apex:includeScript value="//cdn.datatables.net/plug-ins/1.10.9/features/searchHighlight/dataTables.searchHighlight.min.js"/> <apex:stylesheet value="//cdn.datatables.net/plug-ins/1.10.9/features/searchHighlight/dataTables.searchHighlight.css"/> <!-- DataTable buttons --> <apex:includeScript value="https://cdn.datatables.net/buttons/1.0.3/js/dataTables.buttons.min.js"/> <apex:includeScript value="//cdn.datatables.net/buttons/1.0.3/js/buttons.flash.min.js"/> <apex:includeScript value="//cdnjs.cloudflare.com/ajax/libs/jszip/2.5.0/jszip.min.js"/> <apex:includeScript value="//cdn.rawgit.com/bpampuch/pdfmake/0.1.18/build/pdfmake.min.js"/> <apex:includeScript value="//cdn.rawgit.com/bpampuch/pdfmake/0.1.18/build/vfs_fonts.js"/> <apex:includeScript value="//cdn.datatables.net/buttons/1.0.3/js/buttons.html5.min.js"/> <apex:includeScript value="//cdn.datatables.net/buttons/1.0.3/js/buttons.print.min.js"/> <apex:stylesheet value="https://cdn.datatables.net/buttons/1.0.3/css/buttons.dataTables.min.css"/> <script type="text/javascript"> $(document).ready(function(){ var accountsTable = $('[cid$="activitiesTable"]').parent('table').eq(0).DataTable({ //enables results highlight searchHighlight: true, //sets record lengths to show in picklist aLengthMenu: [ [10], [10] ], "iDisplayLength": 10, //adds copy, print buttons... dom: 'lBfrtip', //l=length, B=buttons, f=filter(search), r=processing, t=the table, I=table summary, p=page controls buttons: [ 'excel', { extend: 'print', customize: function(win) { $(win.document.body) .css( 'font-size', '10pt' ) .prepend('<img src="http://datatables.net/media/images/logo-fade.png" style="position:absolute; top:0; left:0;" />'); $(win.document.body).find( 'table' ) .addClass( 'compact' ) .css( 'font-size', 'inherit' ); } } ], }); }); </script> </apex:form> </apex:page>
Abdul KhatriAbdul Khatri
I would suggest to add the pagination so that you limit your list per page. I think this will be a long term solution. You can make it configurable and check how many records per page you can show. I guess this is related to governor limit of having max 1000 per List.