0

We are facing issue when extract data on click of a button,

  1. object have 81,258 records in the query.
  2. We click on that we are facing an error.

VF Page:

<apex:page standardController="CustomObject__c"  extensions="Export_extention_class" showHeader="false" contentType="text/csv#Export_EquivalentCoursesCompletions.xls" readOnly="true">
   
   
   <head>
        <meta http-equiv="Content-Type" content="text/html;charset=UTF-8" />
    </head>
   
   <style type="text/css">
      TD{font-family: Arial; font-size: 11pt;}
   </style>
   
   <apex:form >
       <table cellspacing="0" cellpadding="0" border = "1">
          
          
                             <col width="194"/>
              <col width="228"/>
              <col width="233"/>
              <col width="112"/>
                             <tr height="20">
                                    <th height="25" width="194"><strong>field 1</strong></th>
                                    <th height="25" width="194"><strong>Field 2</strong></th>
                                    
                               </tr>
          
          <apex:repeat value="{!CoRegList}" var="o1">
            
              
                               
                              <tr height="20">
                                    
                                    <td  align="left"><apex:outputField value="{!o1.field1_API__C}"/></td>
                                    <td  align="left"><apex:outputField value="{!o1.field2_API__C}"/></td>
                                   
                                   
                              
                              </tr>
  
         </apex:repeat>

Apex class:

public class Export_extention_classs{
    public transient list<PCCA_Course_Registration__c> CoRegList {get;set;}
   
    public Export_extention_class(ApexPages.StandardController controller) {
        set<id> sid1 = new set<id>();
  
  list<Web_Form__c> weblist = [select id,name,Course_Name_new__c from Web_Form__c where id =:ApexPages.currentPage().getParameters().get('id')] ; 
  
  
  
  CoRegList = [select id,name,PCCA_Offering_Registration_ID__r.Course_Name__r.name,PCCA_Offering_Registration_ID__r.Course_Name__r.Course_Code__c,PCCA_Module_Course_Name__r.LERS_CourseName__c,PCCA_Module_Course_Name__r.LERS_CourseName__r.name,PCCA_Offering_Registration_ID__r.User_Name__r.name,PCCA_Offering_Registration_ID__r.User_Name__r.PCCA_Person_ID__c,PCCA_Offering_Registration_ID__r.User_Name__r.PCCA_Email_address__c,PCCA_Module_Course_Name__r.LERS_CourseName__r.Course_Code__c,PCCA_Module_Course_Name__r.PCCA_Delete_Flag_Text__c,PCCA_Delete_Flag_Text__c from PCCA_Course_Registration__c where PCCA_Module_Course_Name__r.LERS_CourseName__c = : weblist[0].Course_Name_new__c and PCCA_Delete_Flag_Text__c ='No' and PCCA_Module_Course_Name__r.PCCA_Delete_Flag_Text__c='No'];
    }
}
5
  • Your code is breaching the salesforce governor limits (Total number of records retrieved by ... ) listed here: developer.salesforce.com/docs/atlas.en-us.apexcode.meta/…. You should try and break up the processing into batches to fit your requirement. – arut Sep 27 '20 at 5:59
  • If you're not doing any operation(DML) on data, use @ReadOnly to get more than 1Lakh records. – Ysr Shk Sep 27 '20 at 6:19
  • 1
    It is impossible to operate in VF with collections bigger than 10k records. Split it to multiple collections or use VF remoting and JS to show the data (last one is impossible to render data as excel/csv/pdf, so only first step might help) – kurunve Sep 27 '20 at 10:21
  • The best solution to the problem is to use a Bulk API call from your VF page. Submit the request and retrieve results once the job is complete. – Nishant Singh Panwar Sep 27 '20 at 13:04
  • The other suggestions above are worthwhile; you can also consider lists of lists (and your CSV then has repeats within repeats). This gets around the collection size limit - but be careful of heap and viewstate. – cropredy Sep 28 '20 at 3:53
1

If you're working with such high volumes of data, you might be better off using a standard Salesforce report with the report columns matching the fields you are querying (which can also easily be changed in future without changing code if needed), and the report filters matching your query's WHERE clause filters

You can then programmatically pass URL parameters to set report filter values dynamically (e.g. the Course Name in your case), and to tell the report to export its data into CSV, for example:

https://[MYDOMAIN].my.salesforce.com/[REPORTID]?fv0=[COURSENAME]&csv=1&exp=1&enc=UTF-8&isdtp=p1

For your scenario, you could change your VF page to redirect to the report URL with the relevant URL parameters set in the extension

The extension class would look something like this:

public class Export_Extention_Classs{
    private String courseName;
    
    public Export_Extention_Class(ApexPages.StandardController controller) {
        List<Web_Form__c> webList = [SELECT Id, Name, Course_Name_new__c FROM Web_Form__c WHERE Id =:controller.getId()];
        courseName = webList[0].Course_Name_new__c;
    }
    
    public PageReference onPageLoad() {
        Id reportId = [SELECT Id FROM Report WHERE Name = '[REPORT NAME]'].Id;
        
        PageReference reportExportPage = new PageReference('/' + reportId + '?fv0=' + courseName + '&csv=1&exp=1&enc=UTF-8&isdtp=p1');
        reportExportPage.setRedirect(true);
        
        return reportExportPage;
    }
}

Note: it's helpful to query the Report Id based on the name as it will likely change between your sandbox/production environments. Also, the URL parameters shown are for Lightning Experience, and would be slightly different in Classic

The VF Page can then simply be:

<apex:page standardController="Web_Form__c" extensions="Export_Extention_Class" showHeader="false" action="{!onPageLoad}">
</apex:page>

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.