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!
Best Answer
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:
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:
Should look something like: