function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
ElkeElke 

Dynamic Query not working

Hi,

 

based on this helpful article

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

I try to develop a VF page with a custom controller extension to prepare a dynamic search for a name string.

I’m using a custom object named “ySkill__c” which has a relation to the SF User object (1:1).

Everything works as expected. I see a table of all my records, I can type in a search string. If I type in any letter in the input text field of my search the “onkeyup” event occurs. I get into the method runSearch() and at least into the method runQuery(). But the table is not filtered with the new query, I see the same set of records. Maybe the page is not refreshed?? If I press “Enter” the query is executed the records are filtered on the page. But it should also work with “KeyUp”, not only if I hit “Enter”.

Can anybody please help me to find my mistake? Attached is the code of my VF page and the Apex custom controller.

 

Thanks a lot,

Elke

<apex:page standardController="ySkill__c" recordSetvar="employees" extensions="TestListExt">
  <apex:form >
    <apex:pageMessages id="errors" />
    
    <apex:pageBlock title="Find Employees" mode="edit">
    <table width="100%" border="0">
        <tr>
            <td width="200" valign="top">
            <apex:pageBlock title="Search for:" mode="edit" id="criteria">
            
            <script type="text/javascript">
            function doSearch () {
               alert(document.getElementById("firstName").value);
               searchServer( document.getElementById("firstName").value);
            }
            </script>
            
            <apex:actionFunction name="searchServer" action="{!runSearch}" rerender="results,debug, errors">
                <apex:param name="firstName" value="" />
            </apex:actionFunction>
            <table cellpadding="2" cellspacing="2">
                <tr>
                    <td style="font-weight:bold;">Name<br/>
                    <input type="text" id="firstName" onkeyup="doSearch();"/>
                    </td>
                </tr>
            </table>
            </apex:pageBlock>
            </td>   
            <td valign="top">
            <apex:pageBlockTable value="{!employees}" var="e">
 
            <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="{!e.Name}"/>
            </apex:column>
 
            <apex:column >
                <apex:facet name="header">
                    <apex:commandLink value="Alias Name" action="{!toggleSort}" rerender="results,debug">
                        <apex:param name="sortField" value="yUserName__r.Alias" assignTo="{!sortField}"/>
                    </apex:commandLink>
                </apex:facet>
                <apex:outputField value="{!e.yUserName__r.Alias}"/>
            </apex:column>
            
         </apex:pageBlockTable>
     </td>
  </tr>
  </table>

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

</apex:page>

 

public with sharing class TestListExt {


    public TestListExt(ApexPages.StandardSetController controller) {
        soql = 'SELECT id, name, yUserName__r.name, yUserName__r.Alias FROM ySkill__c, ySkill__c.yUserName__r ';
        runQuery();


    }


  // the soql without the order and limit
  private String soql {get;set;}
  // the collection of employees based on custom onject ySkill__c to display
  public List<ySkill__c> employees {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 = 'Name'; } return sortField;  }
    set;
  }
 
 
   // format the soql for display on the visualforce page
  public String debugSoql {
    get { return soql + ' order by ' + sortField + ' ' + sortDir ; }
    set;
  }
 
    // 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();
  }
 public void runQuery() {
  //for test only
       ApexPages.addMessage(new ApexPages.Message(ApexPages.Severity.INFO, soql));

    try {
      employees = Database.query(soql + ' order by ' + sortField + ' ' + sortDir);
    } catch (Exception e) {
      ApexPages.addMessage(new ApexPages.Message(ApexPages.Severity.ERROR, 'Wrong SQL!'));
    }
 
  }
    // runs the search with parameters passed via Javascript
  public PageReference runSearch() {
     String theName = Apexpages.currentPage().getParameters().get('firstName');
        soql = 'SELECT id, name, yUserName__r.name, yUserName__r.Alias FROM ySkill__c, ySkill__c.yUserName__r ';
   if (!theName.equals(''))
      soql += ' where name LIKE \''+String.escapeSingleQuotes(theName)+'%\'';
   
   runQuery();
 
    return null;
  }
  
}

 

Best Answer chosen by Admin (Salesforce Developers) 
ElkeElke

Hi everybody,

I found the problem. I didn’t handle the redrawing of the page correctly. The results in the table are refreshed fine if the pageBlock with the table content has an Id such as

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

 

The apex function has a suiting rerenderer attribute with the Id’s of all apex components which have to be redrawn, for instance:

<apex:actionFunction name="searchServer" action="{!runSearch}" rerender="results,debug,errors">

Adding id=”results” to the pageBlock component with the resulting fields in the above code  got my query working dynamically.

My fault! Thanks!

Elke

All Answers

TehNrdTehNrd

See my comment on that post, http://blog.jeffdouglas.com/2010/07/13/building-a-dynamic-search-page-in-visualforce/#comment-2071 , you are probably typing faster than the page can process the request and response. What happens if you type very slowly?

ElkeElke

Hi Jason,

thanks for your suggestion. Unfortunately, fast typing is not the reason for the behavior as I understand. I type only one letter and wait. I see the new soql query recognizing the letter with the additional ApexPages.addMessage() method in runQuery(). It looks like SELECT id, name, yUserName__r.name, yUserName__r.Alias FROM ySkill__c, ySkill__c.yUserName__r where name LIKE 'R%’ With the same SF instance at the same web client machine Jeffs Code is working fine. My own isn’t, records are only filtered after “Enter”. I assume I made a mistake, but I cannot find it…I’m a newbie with VF and Apex, so maybe it’s a very simple thing..??

Same Problem in IE and Firefox.

Elke

ElkeElke

Hi everybody,

I found the problem. I didn’t handle the redrawing of the page correctly. The results in the table are refreshed fine if the pageBlock with the table content has an Id such as

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

 

The apex function has a suiting rerenderer attribute with the Id’s of all apex components which have to be redrawn, for instance:

<apex:actionFunction name="searchServer" action="{!runSearch}" rerender="results,debug,errors">

Adding id=”results” to the pageBlock component with the resulting fields in the above code  got my query working dynamically.

My fault! Thanks!

Elke

This was selected as the best answer