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
Carter85Carter85 

Help Displaying Dynamic Query from a For Loop Properly

I have a dynamic query set up:

public PageReference showResult(){
          for(String i : toDisplay){
          cnclNum = i;
          qry = 'SELECT Name, Contract_Holder__r.Vin_Number__c, Cust_Refund__c, Dealer_Refund__c, Contract_Holder__r.Effective_Date__c, Date__c, Deduction__c, Contract_Holder__r.Name, Customer_Pay__c, Cancelation_Fee__c, Policy_Type__c, Contract_Holder__r.Contract_Number__c FROM Cancelations__c WHERE Name =:cnclNum ORDER BY name';
          quoteResults = database.query(qry);
                }
                if((quoteResults != null)){
                showQuote = SHOW;
                resultChoice = SHOW;
                }
                return Apexpages.currentPage();
                }

 wherein toDisplay is a list of strings, the size of which is based on previous user interactions, so it will be different for any given situation.

 

I would like to display all results returned in however many loops there are on my VF page in a format like this:

<apex:pageBlockSection title="Quote Results" id="resultsBlock" columns="1">
          <apex:pageBlockTable value="{!quoteResults}" var="item" rendered="{!NOT(ISNULL(quoteResults))}">
          <apex:column style="text-align:center;" value="{!item.Contract_Holder__r.Vin_Number__c}" headerValue="Vin Number" width="95"/>
          <apex:column style="text-align:center;" value="{!item.Contract_Holder__r.Name}" headerValue="Name" width="105"/>
          <apex:column style="text-align:center;" value="{!item.Contract_Holder__r.Contract_Number__c}" headerValue="Contract #" width="85"/>
          <apex:column style="text-align:center;" value="{!item.Policy_Type__c}" headerValue="Policy Type" width="85"/>
          <apex:column style="text-align:center;" value="{!item.Date__c}" headerValue="Cancellation Date" width="95"/>
          <apex:column style="text-align:center;" value="{!item.Customer_Pay__c}" headerValue="Customer Purchase Price" width="95"/>
          <apex:column style="text-align:center;" value="{!item.Cancelation_Fee__c}" headerValue="Cancel Fee" width="95"/>
          <apex:column style="text-align:center;" value="{!item.Deduction__c}" headerValue="Claim Deductions" width="95"/>
          <apex:column style="text-align:center;" value="{!item.Cust_Refund__c}" headerValue="Customer Refund" width="95"/>
          <apex:column style="text-align:center;" value="{!item.Dealer_Refund__c}" headerValue="Dealer Refund" width="95"/>
          <apex:column style="text-align:center;" value="{!item.Name}" id="note" headerValue="Quote/Cancellation #" width="105"/>
          </apex:pageBlockTable>

 At the moment however, it will only populate the table with the last entry it finds in the database query and what I'm trying to do requires them all to be present for further user interaction so I was hoping someone might know the best way to get all the results to display at once the loop is completed or a better way to restructure the loop to accomplish the same.

 

Best Answer chosen by Admin (Salesforce Developers) 
SaraagSaraag

You may want to do your query outside of for loop. I would do

 

quoteResults=[select ....from cancelations__c where Name IN  :toDisplay order by name];

if((quoteResults != null)){
showQuote = SHOW;
resultChoice = SHOW;
}
return Apexpages.currentPage();

 

--------

or if you want to use existing code, currently it looks like you're replacing quoteResults every time your run database.query. Use list method addAll to get the new results appended to the existing elements. 

public PageReference showResult(){
 
          for(String i : toDisplay){
          cnclNum = i;
          qry = 'SELECT Name, Contract_Holder__r.Vin_Number__c, Cust_Refund__c, Dealer_Refund__c, Contract_Holder__r.Effective_Date__c, Date__c, Deduction__c, Contract_Holder__r.Name, Customer_Pay__c, Cancelation_Fee__c, Policy_Type__c, Contract_Holder__r.Contract_Number__c FROM Cancelations__c WHERE Name =:cnclNum ORDER BY name';
          quoteResults.addAll(database.query(qry));                }
                if((quoteResults != null)){
                showQuote = SHOW;
                resultChoice = SHOW;
                }
                return Apexpages.currentPage();
                }

I would encourage you to move your query outside the loop.

 

 

Saraag

 

 

All Answers

SaraagSaraag

You may want to do your query outside of for loop. I would do

 

quoteResults=[select ....from cancelations__c where Name IN  :toDisplay order by name];

if((quoteResults != null)){
showQuote = SHOW;
resultChoice = SHOW;
}
return Apexpages.currentPage();

 

--------

or if you want to use existing code, currently it looks like you're replacing quoteResults every time your run database.query. Use list method addAll to get the new results appended to the existing elements. 

public PageReference showResult(){
 
          for(String i : toDisplay){
          cnclNum = i;
          qry = 'SELECT Name, Contract_Holder__r.Vin_Number__c, Cust_Refund__c, Dealer_Refund__c, Contract_Holder__r.Effective_Date__c, Date__c, Deduction__c, Contract_Holder__r.Name, Customer_Pay__c, Cancelation_Fee__c, Policy_Type__c, Contract_Holder__r.Contract_Number__c FROM Cancelations__c WHERE Name =:cnclNum ORDER BY name';
          quoteResults.addAll(database.query(qry));                }
                if((quoteResults != null)){
                showQuote = SHOW;
                resultChoice = SHOW;
                }
                return Apexpages.currentPage();
                }

I would encourage you to move your query outside the loop.

 

 

Saraag

 

 

This was selected as the best answer
Carter85Carter85

Excellent, your suggestion of how to take it out of the loop works perfectly, thanks.