-2

Problem : I am trying to diaplay all the opportunity records in vf page. The records are more than 10000. Also I have set readOnly=true on vf page. The collection size increased from 1000 to 10000 but still I am not able to display.

What I did : I queried the opportunity records and stored in a list and used datatable to populate that list on vf page. I also tried using StandardSetController and used pagination . But then I faced issue of " Too many query locator rows: 10003 " .I know its the limitation.

Please help me to achieve this scenario.Any workaround for this ???

4
  • 2
    According to documentation, standard set controller can not work with more then 10 000 records, check documentation here -- developer.salesforce.com/docs/atlas.en-us.pages.meta/pages/…
    – kurunve
    Jun 25, 2018 at 7:10
  • @kurunve the limitation you have mentioned is while displaying and rendering records through the Visualforce engine on the server. I see the possibility of being able to display more than 10K records through a client side script which directly does not impact the Visualforce view state
    – Jigar Shah
    Jun 25, 2018 at 15:24
  • @JigarShah the limitation that I have mentioned is limitation of StandardSetController. Of course, you can show more with remoting, or rest api or so
    – kurunve
    Jun 25, 2018 at 15:35
  • @kurunve I am quering the records in controller itself and storing in collection. Display this list on vf page on a data table. I am also performing some calculations with these records and hence append dynamic columns to that datatable . so in the process of storing the records throws error of collection size and hence view state too.
    – samita das
    Jun 26, 2018 at 13:13

2 Answers 2

3

The following approach can help you work around the 10K record limitation but should be limited to 50K records at max. However, you might still want to check if it helps work around the Visualforce Page view state limit of 135Kb.

  1. Query the records within your Apex controller and return the query result set as a JSON string to the Visualforce Page.

  2. Convert the JSON string into a Javascript array through a Javascript on the Page.

  3. Iterate over the array using Javascript to construct a html table, consisting of table, tr and td tags, dynamically within a Javascript method.

  4. Insert the constructed DOM in a predefined <div> using the following code.

    document.getElementById("div-id").innerHTML = "Your Html table string";

  5. Show the <div> to display the records to the user.

0
1

You can only list maximum 1000 elements in visual force pages components. You can use a paginator to show 1000 elements max per page. I have written you page and class using StandardSetController. It will take care of all pagination related functionalities which you are trying to achieve in a very simplified way. Based on your dynamic query, the results will be assigned to StandardSetController instance, here it is setCon. In the page, first, next, previous, last etc. methods have been used. You can also, limit the records on the page, based on the drop-down, a default is shown 10 records.

 Refer StandardSetController

 Visualforce :

<apex:page id="filterPage" controller = "FilterSearch">
    <apex:form style="overflow-y:auto;overflow-x:auto;margin-right: 5px;margin-left: 5px;margin-top: 3px;" id="searchForm">
        <apex:actionFunction name="refreshPageSize" action="{!refreshPageSize}" status="fetchStatus" reRender="searchBlock"/>

        <apex:pageBlock title="Results" rendered="{!showResult}" id="searchBlock"> 
            <apex:pageblockTable value="{!Opps}" var="a" id="tabId">  
                <apex:column>
                    <a href="/{!a.id}" target="_blank"> {!a.name}</a>
                </apex:column>
                <apex:column rendered="false" id="id">
                    <apex:outputField value="{!a.id}" />
                </apex:column>
                <apex:column headerValue="Account Name">
                    <apex:outputField value="{!a.account.name}" />
                </apex:column>

                <apex:column headerValue="Stage">
                    <apex:outputField value="{!a.stagename}" />
                </apex:column>

                <apex:column headerValue="Forecast Category" >
                    <apex:outputField value="{!a.ForecastCategoryName}"  />
                </apex:column>

            </apex:pageBlockTable>
            <apex:panelGrid columns="8">             
                <apex:selectList value="{!size}" multiselect="false" size="1" onchange="refreshPageSize();">
                    <apex:selectOptions value="{!paginationSizeOptions}"/>
                </apex:selectList>           
                <apex:commandButton status="fetchStatus" reRender="searchBlock" value="<<" action="{!setCon.first}" disabled="{!!setCon.hasPrevious}" title="First Page"/>
                <apex:commandButton status="fetchStatus" reRender="searchBlock" value="<" action="{!setCon.previous}" disabled="{!!setCon.hasPrevious}" title="Previous Page"/>
                <apex:commandButton status="fetchStatus" reRender="searchBlock" value=">" action="{!setCon.next}" disabled="{!!setCon.hasNext}" title="Next Page"/>
                <apex:commandButton status="fetchStatus" reRender="searchBlock" value=">>" action="{!setCon.last}" disabled="{!!setCon.hasNext}" title="Last Page"/>
                <apex:outputText >{!(setCon.pageNumber  size)+1-size}-{!IF((setCon.pageNumber  size)>noOfRecords, noOfRecords,
                     (setCon.pageNumber * size))} of {!noOfRecords}
                </apex:outputText>                 
                <apex:outputPanel >                      
                </apex:outputPanel>
            </apex:panelGrid>                                      
      </apex:pageBlock> 
    </apex:form>    
</apex:page>

Controller--- : 

public class FilterSearch  
{
    public String searchString{get;set;}
    public Boolean showResult{get;set;}
    public Integer noOfRecords{get; set;}


    public Integer size{get;set;}
    String errorStr = '';    
    public ApexPages.StandardSetController setCon{get;set;}    
    public List<SelectOption> paginationSizeOptions{get;set;}


    /**
    * FilterSearch
    * Constructor to initiate the default values at the time of Loading
    */    
    public FilterSearch()
    {
        searchString = ApexPages.currentPage().getParameters().get('searchText');

        size=10;

        paginationSizeOptions = new List<SelectOption>();
        paginationSizeOptions.add(new SelectOption('10','10'));
        paginationSizeOptions.add(new SelectOption('20','20'));
        paginationSizeOptions.add(new SelectOption('30','30'));
        showAll();        
    }

    public void showAll()
    {
        try
        {
            if(String.isNotBlank(searchString))
            {  
                //change your query here, just to show you I have omitted the searchString
                String query='Select Id,Name, account.name, stagename, ForecastCategoryName FROM Opportunity LIMIT 1000';
                setCon = new ApexPages.StandardSetController(Database.getQueryLocator(query));            
                if(setCon.getResultSize() >0)
                {
                showResult = true;
                }else{
                    showResult = false;
                    ApexPages.addmessage(new ApexPages.message(ApexPages.severity.INFO,'No Records Found.'));
                }        
            }            
        }catch(Exception ex)
        {
            errorStr ='Error Occured while Searching.';
            ApexPages.addmessage(new ApexPages.message(ApexPages.severity.ERROR, 'errorStr:' +ex.getMessage()));
        }   
    }

    public List<Opportunity> getOpps() 
    {
        if(setCon.getResultSize() >0)
        {
          setCon.setPageSize(size);  
          noOfRecords = setCon.getResultSize();
          return (List<Opportunity>)setCon.getRecords();
        }else
        {
          return null;
        }
        return null;
    }

    /**
    * refreshPageSize
    * Changes the size of Pagination.
    * @param    
    * @return void
    */
    public void refreshPageSize() 
    {
        setCon.setPageSize(size);         
    }
}

enter image description here

0

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .