0

I have followed the instructions from this blog (http://blog.jeffdouglas.com/2010/07/13/building-a-dynamic-search-page-in-visualforce/) on how to create a table with dynamic search parameters and I keep running into the issue that only 2 out of the six search parameters are working properly. Everything is working fine, except for the fact that whenever I enter a letter (or the id since some of the fields are based on id fields ex) accountid) into the parameters (account name, amount, close date, or opportunity owner) the error message that is created within the controller pops up. When I type in a name into the "Quote Name" text area or choose a Forecast Category, the table works as it should. Does anyone know why the error message would keep popping up just on certain parameters and how I can fix this? Thanks!

Controller:

public with sharing class ContactSearchController {

 // the soql without the order and limit
 private String soql {get;set;}
 // the collection of opportunities to display
 public List<Opportunity> opportunities {get;set;}

 // the current sort direction. defaults to asc
 public String sortDir {
 get  { if (sortDir == null) {  sortDir = 'desc'; } return sortDir;  }
 set;
}

 // the current field to sort by. defaults to last name
public String sortField {
get  { if (sortField == null) {sortField = 'closedate'; } return sortField;  }
set;
}

// format the soql for display on the visualforce page
 public String debugSoql {
get { return soql + ' order by ' + sortField + ' ' + sortDir + ' limit 25'; }
set;
}

// init the controller and display some sample data when the page loads
public ContactSearchController() {
  soql = 'select name, accountid, amount, closedate, forecastcategoryname, ownerid from opportunity where accountid != null';
 runQuery();
}

// toggles the sorting of query from asc<-->desc
public void toggleSort() {
  // simply toggle the direction
  sortDir = sortDir.equals('asc') ? 'desc' : 'asc';
  // run the query again
  runQuery();
}

// runs the actual query
public void runQuery() {

  try {
     opportunities = Database.query(soql + ' order by ' + sortField + ' ' + sortDir + ' limit 25');
   } catch (Exception e) {
  ApexPages.addMessage(new ApexPages.Message(ApexPages.Severity.ERROR, 'Ooops!'));
  }

}

// runs the search with parameters passed via Javascript
public PageReference runSearch() {

  String name = Apexpages.currentPage().getParameters().get('name');
  String accountid = Apexpages.currentPage().getParameters().get('accountid');
  String amount = Apexpages.currentPage().getParameters().get('amount');
  String closedate = Apexpages.currentPage().getParameters().get('closedate');
  String forecastcategoryname = Apexpages.currentPage().getParameters().get('forecastcategoryname');
  String ownerid = Apexpages.currentPage().getParameters().get('ownerid');

  soql = 'select name, accountid, amount, closedate, forecastcategoryname, ownerid from opportunity where accountid != null';
  if (!name.equals(''))
    soql += ' and name LIKE \''+String.escapeSingleQuotes(name)+'%\'';
  if (!accountid.equals(''))
    soql += ' and accountid LIKE \''+String.escapeSingleQuotes(accountid)+'%\''; 
  if (!amount.equals(''))
    soql += ' and amount LIKE \''+String.escapeSingleQuotes(amount)+'%\'';    
  if (!closedate.equals(''))
    soql += ' and closedate LIKE \''+String.escapeSingleQuotes(closedate)+'%\'';
  if (!forecastcategoryname.equals(''))
    soql += ' and forecastcategoryname LIKE \''+String.escapeSingleQuotes(forecastcategoryname)+'%\'';
  if (!ownerid.equals(''))
    soql += ' and ownerid LIKE \''+String.escapeSingleQuotes(ownerid)+'%\'';

  // run the query again
  runQuery();

 return null;
}

// use apex describe to build the picklist values
public List<String> forecastcategoryname {
  get {
    if (forecastcategoryname == null) {

      forecastcategoryname = new List<String>();
      Schema.DescribeFieldResult field = opportunity.forecastcategoryname.getDescribe();

      for (Schema.PicklistEntry f : field.getPicklistValues())
        forecastcategoryname.add(f.getLabel());

    }
    return forecastcategoryname;          
  }
  set;
}

}

VF Page:

<apex:page controller="ContactSearchController" sidebar="false">

<apex:form >
 <apex:pageMessages id="errors" />

  <apex:pageBlock title="2014 Forecast" mode="edit">

  <table width="100%" border="0">
   <tr>  
     <td width="200" valign="top">

  <apex:pageBlock title="Parameters" mode="edit" id="criteria">

  <script type="text/javascript">
  function doSearch() {
    searchServer(
      document.getElementById("name").value,
      document.getElementById("accountid").value,
      document.getElementById("amount").value,
      document.getElementById("closedate").value,
      document.getElementById("forecastcategoryname").options[document.getElementById("forecastcategoryname").selectedIndex].value,
      document.getElementById("ownerid").value

      );
  }
  </script> 

  <apex:actionFunction name="searchServer" action="{!runSearch}" rerender="results,debug,errors">
      <apex:param name="name" value="" />
      <apex:param name="accountid" value="" />
      <apex:param name="amount" value="" />
      <apex:param name="closedate" value="" />
      <apex:param name="forecastcategoryname" value="" />
      <apex:param name="ownerid" value="" />
  </apex:actionFunction>

  <table cellpadding="2" cellspacing="2">
  <tr>
    <td style="font-weight:bold;">Quote Name<br/>
    <input type="text" id="name" onkeyup="doSearch();"/>
    </td>
  </tr>
  <tr>
    <td style="font-weight:bold;">Account Name<br/>
    <input type="text" id="accountid" onkeyup="doSearch();"/>
    </td>
  </tr>
  <tr>
    <td style="font-weight:bold;">Amount<br/>
    <input type="text" id="amount" onkeyup="doSearch();"/>
    </td>
  </tr>
  <tr>
    <td style="font-weight:bold;">Close Date<br/>
    <input type="text" id="closedate" onkeyup="doSearch();"/>
    </td>
  </tr>
 <tr>
    <td style="font-weight:bold;">Forecast Category<br/>
      <select id="forecastcategoryname" onchange="doSearch();">
        <option value=""></option>
        <apex:repeat value="{!forecastcategoryname}" var="category">
          <option value="{!category}">{!category}</option>
        </apex:repeat>
      </select>
    </td>
  </tr>
  <tr>
    <td style="font-weight:bold;">Opportunity Owner<br/>
    <input type="text" id="ownerid" onkeyup="doSearch();"/>
    </td>
  </tr>
  </table>

  </apex:pageBlock>

</td>
<td valign="top">

<apex:pageBlock mode="edit" id="results">

    <apex:pageBlockTable value="{!opportunities}" var="o">


        <apex:column >
            <apex:facet name="header">
                <apex:commandLink value="Name" action="{!toggleSort}" rerender="results,debug">
                    <apex:param name="sortField" value="name" assignTo="{!sortField}"/>
                </apex:commandLink>
            </apex:facet>
            <apex:outputField value="{!o.name}"/>
        </apex:column>

        <apex:column >
            <apex:facet name="header">
                <apex:commandLink value="Account Name" action="{!toggleSort}" rerender="results,debug">
                    <apex:param name="sortField" value="accountid" assignTo="{!sortField}"/>
                </apex:commandLink>
            </apex:facet>
            <apex:outputField value="{!o.accountid}"/>
        </apex:column>

         <apex:column >
            <apex:facet name="header">
                <apex:commandLink value="Amount" action="{!toggleSort}" rerender="results,debug">
                    <apex:param name="sortField" value="amount" assignTo="{!sortField}"/>
                </apex:commandLink>
            </apex:facet>
            <apex:outputField value="{!o.amount}"/>
        </apex:column>

         <apex:column >
            <apex:facet name="header">
                <apex:commandLink value="Close Date" action="{!toggleSort}" rerender="results,debug">
                    <apex:param name="sortField" value="closedate" assignTo="{!sortField}"/>
                </apex:commandLink>
            </apex:facet>
            <apex:outputField value="{!o.closedate}"/>
        </apex:column>

         <apex:column >
            <apex:facet name="header">
                <apex:commandLink value="Forecast Category" action="{!toggleSort}" rerender="results,debug">
                    <apex:param name="sortField" value="forecastcategoryname" assignTo="{!sortField}"/>
                </apex:commandLink>
            </apex:facet>
            <apex:outputField value="{!o.forecastcategoryname}"/>
        </apex:column>

         <apex:column >
            <apex:facet name="header">
                <apex:commandLink value="Opportunity Owner" action="{!toggleSort}" rerender="results,debug">
                    <apex:param name="sortField" value="ownerid" assignTo="{!sortField}"/>
                </apex:commandLink>
            </apex:facet>
            <apex:outputField value="{!o.ownerid}"/>
        </apex:column>


    </apex:pageBlockTable>

</apex:pageBlock>

</td>
</tr>
</table>

<apex:pageBlock title="Debug - SOQL" id="debug">
    <apex:outputText value="{!debugSoql}" />           
</apex:pageBlock>    

</apex:pageBlock>

</apex:form>

</apex:page>

Updated Question: If I want to include the quote line item "quantity" in my opportunity table, how do I reference that field in my code? I have a couple of fields that I need to reference from the Quote object and the QuoteLineItem object but can't seem to figure out the traversing to make them function as they should. I have read the documentation from Salesforce on parent to child and child to parent relationships, but it seems like any combination I try isn't working. Any help would be appreciated. Thanks!

4
  • Most common issue in such case is missing space or typo, can you instead of returning just error message return also query String? Thanks to that our job here will be easier :) Feb 3, 2014 at 20:13
  • How do I go about returning query Strings as well? Feb 3, 2014 at 20:35
  • Just change chatch block to : ApexPages.addMessage(new ApexPages.Message(ApexPages.Severity.ERROR, 'Ooops!' + soql + ' order by ' + sortField + ' ' + sortDir + ' limit 25')); Feb 3, 2014 at 20:43
  • This is what the error message read after I tried to search for a value in the account name field: Error: Ooops!select name, accountid, amount, closedate, forecastcategoryname, ownerid from opportunity where accountid != null and accountid LIKE 'valley%' order by closedate desc limit 25 Feb 3, 2014 at 20:53

1 Answer 1

3

A good way to test this is to look at the generated debug SOQL at the bottom of the page and then run it in SOQL Xplorer or something. This will give you a good indication what is going on under the hood. For instance, when I entered 60 into the Amount field, the generated SOQL is:

select name, accountid, amount, closedate, forecastcategoryname, ownerid from opportunity where accountid != null and amount LIKE '60%' order by closedate desc limit 25

Since amount is a numeric field, and amount LIKE '60%' order is not going to work. You are constructing the SOQL as string values when some are numeric and dates. For instance, your amount section:

if (!amount.equals(''))
    soql += ' and amount LIKE \''+String.escapeSingleQuotes(amount)+'%\'';

Should look something like:

if (!amount.equals(''))
    soql += ' and amount > '+amount+''';
1
  • Thank you Jeff! I really appreciate the blog post on the dynamic search and the help you have supplied. Feb 4, 2014 at 18:45

You must log in to answer this question.

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