Take a user input to be use in SOQL

Question

I have a controller that counts the number of transaction and group it by store, year, month & day.

I am trying to do a user input box in VF page to allow user to enter the month only so that it will display those data with the selected month.

But it is not able to work properly.

Pls help. Thank you.

My controller:

public with sharing class TestController {
   public Summary[] Summaries { get; set; }    
   public Summary[] displaySummaries {get; set; }    
   public integer userinput{get; set;}    

   public TestController() {
   //constructor        
   AggregateResult[] results = [Select Store_Code__C, calendar_year(Transaction_Date_Time__c) OOB, calendar_month(Transaction_Date_Time__c) KKB, DAY_IN_MONTH(Transaction_Date_Time__c) CCB,  count_distinct(Termin_Receipt__C) NNP FROM Member_Transaction__c group by Store_Code__C, calendar_year(Transaction_Date_Time__c), calendar_month(Transaction_Date_Time__c), DAY_IN_MONTH(Transaction_Date_Time__c) order by Store_Code__C, calendar_year(Transaction_Date_Time__c), calendar_month(Transaction_Date_Time__c), DAY_IN_MONTH(Transaction_Date_Time__c)];
   Summaries = new List<Summary>();        
      for (AggregateResult ar : results) {
           Summaries.add(new Summary(ar));
      }
   displaySummaries = new List<Summary>(Summaries);           
   }

   //wrapper class to hold aggregate data    
   public class Summary {
   public String Store { get; private set; }
   public Integer yyear { get; private set; }
   public Integer mmonth { get;  set; }
   public Integer dday { get; private set; }
   public Integer CCount { get; private set; }
   public Summary(AggregateResult ar) {
        Store = (String) ar.get('Store_Code_POS__C');
        yyear = (Integer) ar.get('OOB');
        mmonth = (Integer) ar.get('KKB');
        dday = (Integer) ar.get('CCB');
        CCount = (Integer) ar.get('NNP');
       }
   }    

   public void filter(){
       displaySummaries = new List<Summary>();
       for(Summary o : Summaries){
           if (userinput == o.mmonth){
              displaySummaries.add(o);
           }
        }
   }
}

My VF page:

<apex:page controller="TestController">
<apex:sectionheader title="Transaction Count Report" />
<apex:form >   

  <apex:pageblock title="Filter">
      <apex:pageblocksection > 
        <apex:outputlabel value="Enter month here"/>
        <apex:inputtext value="{!userinput}">
        <apex:actionsupport event="onchange" action="{!filter}" rerender="TxCTable" />
        </apex:inputtext>  
      </apex:pageblocksection>
  </apex:pageblock>    

  <apex:pageblock title="Transaction Count " id="TxCTable">
       <apex:pageblocktable value="{!displaySummaries}" var="o">                    
           <apex:column value="{!o.Store}" headervalue="Store"/>
           <apex:column value="{!o.yyear}" headervalue="Year"/>
           <apex:column value="{!o.mmonth}" headervalue="Month"/>
           <apex:column value="{!o.dday}" headervalue="Day"/>
           <apex:column value="{!o.Ccount}" headervalue="Count"/>
       </apex:pageblocktable>
    </apex:pageblock>
</apex:form>

The table only shows the header & there is no data. If I replace the userinput in the SOQL with a integer, then the data is shown. Somehow, the input entered in VF cannot be passed to the controller....i can't figure out which part went wrong. — Ben, Feb 28 '18 at 09:37

score 3 · Answer 1 · answered Feb 28 '18 at 10:02

As the constructor isn't re-run on a POST (when the userinput value is submitted), one change needed is to move the query to the Summaries getter so the query is re-executed each time the table is re-rendered:

public Summary[] Summaries {
    get {
        AggregateResult[] results = [Select Store_Code__C, calendar_year(Transaction_Date_Time__c) OOB, calendar_month(Transaction_Date_Time__c) KKB, DAY_IN_MONTH(Transaction_Date_Time__c) CCB,  count_distinct(Termin_Receipt__C) NNP FROM Member_Transaction__c WHERE calendar_month(Transaction_Date_Time__c) =: userinput group by Store_Code__C, calendar_year(Transaction_Date_Time__c), calendar_month(Transaction_Date_Time__c), DAY_IN_MONTH(Transaction_Date_Time__c) order by Store_Code__C, calendar_year(Transaction_Date_Time__c), calendar_month(Transaction_Date_Time__c), DAY_IN_MONTH(Transaction_Date_Time__c)];
        Summaries = new List<Summary>();
        for (AggregateResult ar : results) {
            Summaries.add(new Summary(ar));
        }
        return Summaries;
    }
}

Hi,I had tried your method but is not working. I had rewritten the code such that when the constructor is run, there is a another list. There is a "filter' method. The filter method will compare against the user input & stored in the list. but now it will display the whole list. Pls help to take a look at the code. Thank you. — Ben, Mar 19 '18 at 08:08
@ben I can't see what is wrong with your code. Add System.debug to the Apex and use your browser's Network tab in the browser "Developer Tools" to see what is going wrong. See How do I start to debug my own Apex code? and How do I start to debug my own Visualforce/JavaScript?. — Keith C, Mar 19 '18 at 11:37

Take a user input to be use in SOQL

1 Answers1