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
SeanCenoSeanCeno 

Dynamic Search - OnKeyUp / SOQL escapeSingleQuotes

All,

I'm working on a project for a dynamic search of clients using Jeff Douglas's blog as a template

http://blog.jeffdouglas.com/2010/07/13/building-a-dynamic-search-page-in-visualforce/

I have everything displayed correctly, but when I go to search, the debug soql query doesn't seem to be adding the search letter I type with the wild card. If you look at the demo here:

https://jeffdouglas-developer-edition.na5.force.com/examples/CustomerSearch

you can see that when a letter is entered, the debug soql changed to add that new search to the original soql.

From:
select firstname, lastname, account.name, interested_technologies__c from contact where account.name != null order by lastName asc limit 20
To:
select firstname, lastname, account.name, interested_technologies__c from contact where account.name != null and firstname LIKE 'a%' order by lastName asc limit 20

Does anybody have any idea why my search doesn't seem to be firing when the 'OnKeyUp' function is executed? I feel like it may have something to do with the 'escapeSingeQuotes' method or the LIKE.

Here is a snippet of my code:

// runs the search with parameters passed via Javascript
  public PageReference runSearch() {
      
    String FirstName = Apexpages.currentPage().getParameters().get('FirstName');
    String LastName = Apexpages.currentPage().getParameters().get('LastName');
    String Broker = Apexpages.currentPage().getParameters().get('Broker_Dealer_Name__c');
    String MailingCity = Apexpages.currentPage().getParameters().get('MailingCity');
    String MailingState = Apexpages.currentPage().getParameters().get('MailingState');
    String MailingPostalCode = Apexpages.currentPage().getParameters().get('MailingPostalCode');
    String OwnerName = Apexpages.currentPage().getParameters().get('Owner.Name');
    String NSIncomeProducerStatus = Apexpages.currentPage().getParameters().get('Producer_Status__c');
    String NSIncomeIIProducerStatus = Apexpages.currentPage().getParameters().get('NS_Income_II_Producer_Status__c');
    String NSIncomeHIProducerStatus = Apexpages.currentPage().getParameters().get('NS_HI_Producer_Status__c');

    soql = 'select FirstName, LastName, Broker_Dealer_Name__c, MailingStreet, MailingCity, MailingState, 
MailingPostalCode, Owner.Name, Phone, Total_Sales__c, Producer_Status__c, NS_Income_II_Producer_Status__c, 
NS_HI_Producer_Status__c FROM Contact Where Broker_Dealer_Name__c !=null';

    if (!FirstName.equals(''))
      soql += ' and FirstName LIKE \''+String.escapeSingleQuotes(FirstName)+'%\'';
    if (!LastName.equals(''))
      soql += ' and LastName LIKE \''+String.escapeSingleQuotes(LastName)+'%\'';
    if (!Broker.equals(''))
      soql += ' and Broker_Dealer_Name__c LIKE \''+String.escapeSingleQuotes(Broker)+'%\'';
    if (!MailingCity.equals(''))
      soql += ' and MailingCity LIKE \''+String.escapeSingleQuotes(MailingCity)+'%\'';  
    if (!MailingState.equals(''))
      soql += ' and MailingState LIKE \''+String.escapeSingleQuotes(MailingState)+'%\'';
    if (!MailingPostalCode.equals(''))
      soql += ' and MailingPostalCode LIKE \''+String.escapeSingleQuotes(MailingPostalCode)+'%\''; 
    if (!OwnerName.equals(''))
      soql += ' and Owner.Name LIKE \''+String.escapeSingleQuotes(OwnerName)+'%\'';
    if (!NSIncomeProducerStatus.equals(''))
      soql += ' and Producer_Status__c = (\''+NSIncomeProducerStatus+'\')';
    if (!NSIncomeIIProducerStatus.equals(''))
      soql += ' and NS_Income_II_Contact_Type__c = (\''+NSIncomeIIProducerStatus+'\')';
    if (!NSIncomeHIProducerStatus.equals(''))
      soql += ' and NS_HI_Contact_Type__c = (\''+NSIncomeHIProducerStatus+'\')';
  
    // run the query again
    runQuery();

    return null;
  }

User-added image

Best Answer chosen by SeanCeno
Tim BarsottiTim Barsotti
There is an extra ");" on the VF page - line 28

All Answers

Tim BarsottiTim Barsotti
Please post your VF page.
SeanCenoSeanCeno
<apex:page controller="ContactSearchController" sidebar="false">

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

  <apex:pageBlock title="Rep Search" 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("FirstName").value,
          document.getElementById("LastName").value,
          document.getElementById("Broker").value,
          document.getElementById("MailingCity").value,
          document.getElementById("MailingState").value,
          document.getElementById("MailingPostalCode").value,
          document.getElementById("OwnerName").value,
          document.getElementById("NSIncomeProducerStatus").options[document.getElementById("NSIncomeProducerStatus").selectedIndex].value,
          document.getElementById("NSIncomeIIProducerStatus").options[document.getElementById("NSIncomeIIProducerStatus").selectedIndex].value,
          document.getElementById("NSIncomeHIProducerStatus").options[document.getElementById("NSIncomeHIProducerStatus").selectedIndex].value
          );
          );
      }
      </script> 

      <apex:actionFunction name="searchServer" action="{!runSearch}" rerender="results,debug,errors">
          <apex:param name="FirstName" value="" />
          <apex:param name="LastName" value="" />
          <apex:param name="Broker" value="" />
          <apex:param name="MailingCity" value="" />
          <apex:param name="MailingState" value="" />
          <apex:param name="MailingPostalCode" value="" />
          <apex:param name="OwnerName" value="" />
          <apex:param name="NSIncomeProducerStatus" value="" />
          <apex:param name="NSIncomeIIProducerStatus" value="" />
          <apex:param name="NSIncomeHIProducerStatus" value="" />
      </apex:actionFunction>

      <table cellpadding="2" cellspacing="2">
      <tr>
        <td style="font-weight:bold;">First Name<br/>
        <input type="text" id="FirstName" onkeyup="doSearch();"/>
        </td>
      </tr>
      <tr>
        <td style="font-weight:bold;">Last Name<br/>
        <input type="text" id="LastName" onkeyup="doSearch();"/>
        </td>
      </tr>
      <tr>
        <td style="font-weight:bold;">Broker/Dealer<br/>
        <input type="text" id="Broker" onkeyup="doSearch();"/>
        </td>
      </tr>
      <tr>
        <td style="font-weight:bold;">City<br/>
        <input type="text" id="MailingCity" onkeyup="doSearch();"/>
        </td>
      </tr>
       <tr>
        <td style="font-weight:bold;">State<br/>
        <input type="text" id="MailingState" onkeyup="doSearch();"/>
        </td>
      </tr>
       <tr>
        <td style="font-weight:bold;">Zip Code<br/>
        <input type="text" id="MailingPostalCode" onkeyup="doSearch();"/>
        </td>
      </tr>
       <tr>
        <td style="font-weight:bold;">Owner<br/>
        <input type="text" id="OwnerName" onkeyup="doSearch();"/>
        </td>
      </tr> 
      <tr>
        <td style="font-weight:bold;">NS Income I<br/>
          <select id="NSIncomeProducerStatus" onchange="doSearch();">
            <option value=""></option>
            <apex:repeat value="{!NSIncomeProducerStatus}" var="NSI">
              <option value="{!NSI}">{!NSI}</option>
            </apex:repeat>
          </select>
        </td>
      </tr>
      <tr>
        <td style="font-weight:bold;">NS Income II<br/>
          <select id="NSIncomeIIProducerStatus" onchange="doSearch();">
            <option value=""></option>
            <apex:repeat value="{!NSIncomeIIProducerStatus}" var="NSII">
              <option value="{!NSII}">{!NSII}</option>
            </apex:repeat>
          </select>
        </td>
       </tr>
       <tr>
        <td style="font-weight:bold;">NS HealthCare<br/>
          <select id="NSIncomeHIProducerStatus" onchange="doSearch();">
            <option value=""></option>
            <apex:repeat value="{!NSIncomeHIProducerStatus}" var="NSHI">
              <option value="{!NSHI}">{!NSHI}</option>
            </apex:repeat>
          </select>
        </td>
       </tr>
      </table>
        </apex:pageBlock>
      </td>
    <td valign="top">

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

        <apex:pageBlockTable value="{!contacts}" var="contact">

            <apex:column >
                <apex:facet name="header">
                    <apex:commandLink value="First Name" action="{!toggleSort}" rerender="results,debug">
                        <apex:param name="sortField" value="FirstName" assignTo="{!sortField}"/>
                    </apex:commandLink>
                </apex:facet>
                <apex:outputLink value="/{!contact.id}" target="_blank">{!contact.FirstName}</apex:outputLink>
            </apex:column>
            
            <apex:column >
                <apex:facet name="header">
                    <apex:commandLink value="Last Name" action="{!toggleSort}" rerender="results,debug">
                        <apex:param name="sortField" value="LastName" assignTo="{!sortField}"/>
                    </apex:commandLink>
                </apex:facet>
                <apex:outputLink value="/{!contact.id}" target="_blank">{!contact.LastName}</apex:outputLink>
            </apex:column>
 
            <apex:column >
                <apex:facet name="header">
                    <apex:commandLink value="Broker/Dealer" action="{!toggleSort}" rerender="results,debug">
                        <apex:param name="sortField" value="Broker_Dealer_Name__c" assignTo="{!sortField}"/>
                    </apex:commandLink>
                </apex:facet>
                <apex:outputField value="{!contact.Broker_Dealer_Name__c}"/>
            </apex:column>
            
            <apex:column >
                <apex:facet name="header">
                    <apex:commandLink value="Address" action="{!toggleSort}" rerender="results,debug">
                        <apex:param name="sortField" value="MailingCity, MailingState, MailingPostalCode" assignTo="{!sortField}"/>
                    </apex:commandLink>
                </apex:facet>
                <div><apex:outputText value="{!contact.MailingStreet}"/></div>
                <div><apex:outputText value="{!contact.MailingCity}, {!contact.MailingState} {!contact.MailingPostalCode}"/></div>
            </apex:column>
            
             <apex:column >
                <apex:facet name="header">
                    <apex:commandLink value="Phone" action="{!toggleSort}" rerender="results,debug">
                        <apex:param name="sortField" value="Phone" assignTo="{!sortField}"/>
                    </apex:commandLink>
                </apex:facet>
                <apex:outputField value="{!contact.Phone}"/>
            </apex:column>
            
             <apex:column >
                <apex:facet name="header">
                    <apex:commandLink value="Total Sales" action="{!toggleSort}" rerender="results,debug">
                        <apex:param name="sortField" value="Total_Sales__c" assignTo="{!sortField}"/>
                    </apex:commandLink>
                </apex:facet>
                <apex:outputField value="{!contact.Total_Sales__c}"/>
            </apex:column>
            
             <apex:column >
                <apex:facet name="header">
                    <apex:commandLink value="Owner" action="{!toggleSort}" rerender="results,debug">
                        <apex:param name="sortField" value="Owner.Name" assignTo="{!sortField}"/>
                    </apex:commandLink>
                </apex:facet>
                <apex:outputField value="{!Contact.Owner.Name}"/>
            </apex:column>

            <apex:column >
                <apex:facet name="header">
                    <apex:commandLink value="NS Income I" action="{!toggleSort}" rerender="results,debug">
                        <apex:param name="sortField" value="Producer_Status__c" assignTo="{!sortField}"/>
                    </apex:commandLink>
                </apex:facet>
                <apex:outputField value="{!contact.Producer_Status__c}"/>
            </apex:column>
            
            <apex:column >
                <apex:facet name="header">
                    <apex:commandLink value="NS Income II" action="{!toggleSort}" rerender="results,debug">
                        <apex:param name="sortField" value="NS_Income_II_Producer_Status__c" assignTo="{!sortField}"/>
                    </apex:commandLink>
                </apex:facet>
                <apex:outputField value="{!contact.NS_Income_II_Producer_Status__c}"/>
            </apex:column>
            
            <apex:column >
                <apex:facet name="header">
                    <apex:commandLink value="NS HealthCare" action="{!toggleSort}" rerender="results,debug">
                        <apex:param name="sortField" value="NS_HI_Producer_Status__c" assignTo="{!sortField}"/>
                    </apex:commandLink>
                </apex:facet>
                <apex:outputField value="{!contact.NS_HI_Producer_Status__c}"/>
            </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>
SeanCenoSeanCeno
Here's the full and (updated) controller:

public with sharing class ContactSearchController {

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

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

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

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

  // init the controller and display some sample data when the page loads
  public ContactSearchController() {
    soql = 'select FirstName, LastName, Broker_Dealer_Name__c, MailingStreet, MailingCity, MailingState, MailingPostalCode, Owner.Name, Phone, Total_Sales__c, Producer_Status__C, NS_Income_II_Producer_Status__C, NS_HI_Producer_Status__C FROM Contact Where Broker_Dealer_Name__c !=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 {
      contacts = Database.query(soql + ' order by ' + sortField + ' ' + sortDir + ' limit 20');
    } catch (Exception e) {
      ApexPages.addMessage(new ApexPages.Message(ApexPages.Severity.ERROR, 'Ooops!'));
    }

  }

  // runs the search with parameters passed via Javascript
  public PageReference runSearch() {
      
    String FirstName = Apexpages.currentPage().getParameters().get('FirstName');
    String LastName = Apexpages.currentPage().getParameters().get('LastName');
    String Broker = Apexpages.currentPage().getParameters().get('Broker');
    String MailingCity = Apexpages.currentPage().getParameters().get('MailingCity');
    String MailingState = Apexpages.currentPage().getParameters().get('MailingState');
    String MailingPostalCode = Apexpages.currentPage().getParameters().get('MailingPostalCode');
    String OwnerName = Apexpages.currentPage().getParameters().get('OwnerName');
    String NSIncomeProducerStatus = Apexpages.currentPage().getParameters().get('NSIncomeProducerStatus');
    String NSIncomeIIProducerStatus = Apexpages.currentPage().getParameters().get('NSIncomeIIProducerStatus');
    String NSIncomeHIProducerStatus = Apexpages.currentPage().getParameters().get('NSIncomeHIProducerStatus');

    soql = 'select FirstName, LastName, Broker_Dealer_Name__c, MailingStreet, MailingCity, MailingState, MailingPostalCode, Owner.Name, Phone, Total_Sales__c, Producer_Status__c, NS_Income_II_Producer_Status__c, NS_HI_Producer_Status__c FROM Contact Where Broker_Dealer_Name__c !=null';
    if (!FirstName.equals(''))
      soql += ' and FirstName LIKE \''+String.escapeSingleQuotes(FirstName)+'%\'';
    if (!LastName.equals(''))
      soql += ' and LastName LIKE \''+String.escapeSingleQuotes(LastName)+'%\'';
    if (!Broker.equals(''))
      soql += ' and Broker_Dealer_Name__c LIKE \''+String.escapeSingleQuotes(Broker)+'%\'';
    if (!MailingCity.equals(''))
      soql += ' and MailingCity LIKE \''+String.escapeSingleQuotes(MailingCity)+'%\'';  
    if (!MailingState.equals(''))
      soql += ' and MailingState LIKE \''+String.escapeSingleQuotes(MailingState)+'%\'';
    if (!MailingPostalCode.equals(''))
      soql += ' and MailingPostalCode LIKE \''+String.escapeSingleQuotes(MailingPostalCode)+'%\''; 
    if (!OwnerName.equals(''))
      soql += ' and Owner.Name LIKE \''+String.escapeSingleQuotes(OwnerName)+'%\'';
    if (!NSIncomeProducerStatus.equals(''))
      soql += ' and Producer_Status__c = (\''+NSIncomeProducerStatus+'\')';
    if (!NSIncomeIIProducerStatus.equals(''))
      soql += ' and NS_Income_II_Contact_Type__c = (\''+NSIncomeIIProducerStatus+'\')';
    if (!NSIncomeHIProducerStatus.equals(''))
      soql += ' and NS_HI_Contact_Type__c = (\''+NSIncomeHIProducerStatus+'\')';
  
    // run the query again
    runQuery();

    return null;
  }

  // use apex describe to build the picklist values
  public List<String> NSIncomeProducerStatus {
      get {
      if (NSIncomeProducerStatus == null) {
 
        NSIncomeProducerStatus = new List<String>();
        Schema.DescribeFieldResult field = Contact.Producer_Status__c.getDescribe();
 
        for (Schema.PicklistEntry a : field.getPicklistValues())
          NSIncomeProducerStatus.add(a.getLabel());
 
      }
      return NSIncomeProducerStatus;          
    }
    set;
  }
    
  public List<String> NSIncomeIIProducerStatus {
    get {
      if (NSIncomeIIProducerStatus == null) {
 
        NSIncomeIIProducerStatus = new List<String>();
        Schema.DescribeFieldResult field = Contact.NS_Income_II_Producer_Status__c.getDescribe();
 
        for (Schema.PicklistEntry b : field.getPicklistValues())
          NSIncomeIIProducerStatus.add(b.getLabel());
 
      }
      return NSIncomeIIProducerStatus;          
    }
    set;
  }
    
  public List<String> NSIncomeHIProducerStatus {
    get {
      if (NSIncomeHIProducerStatus == null) {
 
        NSIncomeHIProducerStatus = new List<String>();
        Schema.DescribeFieldResult field = Contact.NS_HI_Producer_Status__c.getDescribe();
 
        for (Schema.PicklistEntry c : field.getPicklistValues())
          NSIncomeHIProducerStatus.add(c.getLabel());
 
      }
      return NSIncomeHIProducerStatus;
    }
    set;
  }

}
Tim BarsottiTim Barsotti
There is an extra ");" on the VF page - line 28
This was selected as the best answer
SeanCenoSeanCeno
Running like a champ!