Wednesday, 3 July 2013

Dynamic SOQL implementation with Search Functionality in Salesforce

Hey Salesforce Plumbers,
I am new in here but not new to salesforce.
Recently i was working with Dynamic SOQL, which seriously goes right from the top floor of my head.
I just want to show you all about this dynamic SOQL and the different things i added to that code.

Description of the Code:

Dynamic SOQL :- One of the finest thing provided by the, in the below code, it is used to make field searchable, all the provided fields in the code will search the records of your Account object.(Some of the fields are custom , so if you are going to paste it on you Org, better to look at them first.)

Here in this code below, we are using the things as follows...

1. Dynamic SOQL
2. Paging
3. Header Links

As in below snap shown, value interred in the field, searched 2 related records starting from "E" from Account Object.

Here is the Apex Class
/** Description : This is usinng Dynamic soql to search record. Searching can be done on any field, if related records
* exist then it will show it below. "Paging" is also done,
* Created By : Abhi Tripathi
* Created Date : 03/05/2013
* Revisison Log : 03/015/2013
* Version : V1.0
public class AccountSearchRecordWithPaging{

//string defined for Parent Account's Owner Email
public string parentOwnerEmail {get;set;}

//string defined for owner Email
public string ownerEmail {get;set;}

//string for owner Name
public string ownName {get;set;}

//string for the query
public string soql ;

//integer defined for the Index of listOfListAcc
public Integer pageNumber {get; set;}

public Account acc {get; set;}

//List of account
public List listAcc{
//return listOListAcc with its correspondinbg index i.e pageNumber
return ListOfListAcc[pageNumber];
} set;

//List of Account list's used for paging
public List> listOfListAcc {get;set;}

//variable used
public boolean isSearched {get; set;}

//Method to disable next button
public Boolean getNextButtonDisabled(){

//disable when Index of the page is equal to total number of index OR WHEN THERE
return (pageNumber == listOfListAcc.size() || pageNumber == listOfListAcc.size()-1);

//Method to disable previous button
public Boolean getPreviousButtonDisabled(){

//or when multiplication of currenjt page no. and page size is > then account list
return (pageNumber == listOfListAcc.size() || pageNumber == 0);

//string for sorting============================sorting starts from here=========================================
private String sortDirection = 'ASC';

//string for sorting
private String sortExp = 'name';

public String sortExpression{
//return value of list to sort
return sortExp;
set {
//if the column is clicked on then switch between Ascending and Descending modes
if (value == sortExp)

//condition of ascending and descending order of list
sortDirection = (sortDirection == 'ASC')? 'DESC' : 'ASC';
sortDirection = 'ASC';
sortExp = value;

public String getSortDirection(){

//if column is selected retrun it ascending
if (sortExpression == null || sortExpression == '')
return 'ASC';
return sortDirection;

public void setSortDirection(String value){

sortDirection = value;

public AccountSearchRecordWithPaging(ApexPages.StandardController stdController){

//to hold value
List listAcc = new List();
pageNumber = 0 ;

//Allocating memory
listOfListAcc = new List>();

//fetching the records using getRecord coz we are using
this.acc = (Account)stdController.getRecord();

isSearched = false;

//runs the search with parameters passed
public void Search(){

isSearched = TRUE;

//build the full sort expression with sorting order 'ASC' & 'DESC' with 'NULLS LAST'
string sortFullExp = sortExpression + ' ' + sortDirection + ' ' + 'NULLS LAST';

//clear the record from the list saved in the previous hit of search button

pageNumber = 0;

//soql dynamic query
soql = 'select name, AccountNumber, Phone, Fax, NumberOfEmployees, parent.Owner.Email, Owner.Name, Owner.Email, dateTime__c, formula__c, AnnualRevenue, choose__c, Number_Of_Contact__c, SLAExpirationDate__c, Ownership, Contact__c FROM Account WHERE';

//Check for the User input and prepare the SOQLQuery data
if(Acc.Name != null && Acc.Name != '') {

//method to remove single quotes
soql += ' Name Like\'' + string.escapeSingleQuotes(acc.Name).replaceAll('\\*' ,'%').replaceAll('\\s+', '') + '%\'

system.Debug('EEEEEEE soql' + soql);

//condition if the account field is null or not filled===================ACCOUNT NUMBER=========================
if (acc.AccountNumber != null && acc.AccountNumber!= ''){

//method to remove single quotes
soql += ' AccountNumber Like\'' + string.escapeSingleQuotes(acc.AccountNumber).replaceAll('\\*'
,'%').replaceAll('\\s+', '') + '%\'AND';

if (acc.Phone != null && acc.Phone != ''){

//method to remove single quotes
soql += ' Phone Like\'' + string.escapeSingleQuotes(acc.Phone).replaceAll('\\*' ,'%').replaceAll('\\s+', '') +

if (acc.Fax != null && acc.Fax != ''){

//method to remove single quotes
soql += ' Fax Like\'' + string.escapeSingleQuotes(acc.Fax).replaceAll('\\*' ,'%').replaceAll('\\s+', '') +

//condition=========================================================CURRENCY ANNUAL REVENUE=====================
if (acc.AnnualRevenue != null){

string annualRev = string.ValueOf(acc.AnnualRevenue);

//this is single quote used for number datatype
soql += ' AnnualRevenue =' + annualRev + ' AND ';

system.Debug('$#$#$#$#' + soql);

//condition for picklist=============================================picklist==================================
if (acc.Ownership != null && acc.Ownership != ''){

//Modify SOQL query for picklist (picklist act as a string)
soql += ' Ownership =\'' + acc.Ownership + '\' AND';

//multiselect picklist==============================================Multiselect pick choose===================
if (acc.choose__c != null && acc.choose__c != ''){

//Modify SOQL query for picklist (picklist act as a string)
soql += ' choose__c includes (\''+ acc.choose__c +'\')' + ' AND' ;

//Number Field=====================================================Number=====================================
if (acc.NumberOfEmployees != null){

//Modify SOQL query
soql += ' NumberOfEmployees =' + acc.NumberOfEmployees + ' AND ';

system.debug('number of employes*&*&*&*&*' + soql);

//condition===================================================OWNER lookUp to User===============================
if(ownName != null && ownName != ''){

//method to remove single quote
soql += ' Owner.Name Like\'' + string.escapeSingleQuotes(ownName).replaceAll('\\*' ,'%').replaceAll('\\s+', '') +

system.Debug('#$#$#$#$ string' + SOQL);

if(OwnerEmail != null && OwnerEmail != ''){

//method to remove single quote
soql += ' Owner.Email Like\'' + string.escapeSingleQuotes(OwnerEmail).replaceAll('\\*' ,'%').replaceAll('\\s+', '')
+ '%\'AND';

//====================================================Date time================================================
//Variable defined previously coz of dataType of this field was integer n required here to convert into string
if (acc.DateTime__c != null ){

//fetching the value of field DateTime__c in the variable dateTim
dateTime dateTim = dateTime.valueOf(acc.DateTime__c);

//getting the format of date time in gmt format for the compatibility of dataBae
String formatedDt = dateTim.formatGmt('yyyy-MM-dd\'T\'HH:mm:ss\'Z\'');

//Modify SOQL query
soql += ' DateTime__c = ' + formatedDt + ' AND ';

//here is lookup field, only null condition is taken===================Lookup=====================================
if (acc.Contact__c != null){

//Modify SOQL query
soql += ' Contact__c = \'' + acc.Contact__c + '\' AND';

//here is lookup field to parent account===================Lookup Parent Owner Email==============================
if (parentOwnerEmail != null && parentOwnerEmail != ''){

//method to remove single quote
soql += ' parent.Owner.Email Like\'' + string.escapeSingleQuotes(parentOwnerEmail ).replaceAll('\\*'
,'%').replaceAll('\\s+', '') + '%\'AND';

//condition foer a date field================================================Date SLAExpiration===================
if (acc.SLAExpirationDate__c != null ){

//return the data type in string
String exdate = String.valueOf(acc.SLAExpirationDate__c);

//Remove 00 from the date to make compatible with string
String expirationDate = exdate.remove('00:00:00');

//Modify SOQL query
soql += ' SLAExpirationDate__c =' + expirationDate + ' AND ' ;

//Trim String
soql = soql.trim();

//if query ends with 'AND'
if(soql.endsWith('AND')) {

//fetching the value in between two 'AND's
soql = soql.subString(0, soql.lastIndexOf('AND'));

//If the query ends with 'WHERE' i.e. unfilled values

//Message method when no field is filled
ApexPages.addMessage(new ApexPages.message(ApexPages.severity.INFO,'No fields found filled'));

system.debug('TTTTTT ' + soql );
return ;

listAcc = database.query(soql + 'order by ' + sortFullExp );
system.debug('@@@@@ soql' + soql);

//Populating list of list with value
listOfListAcc.add(new List());

Integer i = 0;

for(Account acc : database.query(soql + 'order by ' + sortFullExp )){

//limit of list on first page

//adding new list to the list of list
listOfListAcc.add(new List());
system.debug( ' $$$$$$ listOfListAcc' + listOfListAcc);

//adding new list at the index of the list

system.debug( ' #######@@@@ listOfListAcc' + listOfListAcc);

//searched records
system.debug( ' +++++ listOfListAcc' + listOfListAcc);

//method for next button
public void NextButton(){

if(listOfListAcc.size() >= 0){


//method for next button
public void PreviousButton(){

if(listOfListAcc.size() != 0){

Here is the VisualForce Page


**    Description    :    This is usinng Dynamic soql to search record. Searching can be done on any field related records

*                         if exist then it will show it below. "Paging" is also done, i.e "Next" & "Previous" button

*                          functionalities.


*    Created By     :    Abhi Tripathi  


*    Created Date   :    03/05/2013


*    Revisison Log  :    03/015/2013


*    Version        :    V1.0



<apex:page standardController="Account" extensions="AccountSearchRecordWithPaging">

<apex:sectionHeader title="Accounts Searched Records"></apex:sectionHeader>



<c:Overlay style="processing" />



   <apex:form >



    <apex:actionStatus id="overlayStatus" onStart="showOverlay();" onstop="hideOverlay();"></apex:actionstatus>


        <apex:pageBlock title="Search Account" id="pBlock">


        <apex:pageMessages ></apex:pageMessages>



            <apex:pageblockButtons location="top" >

                <apex:commandButton value="SEARCH" action="{!Search}" status="overlayStatus" reRender="pBlock"/>



            <apex:pageBlockSection columns="2" >


                <apex:inputText value="{!account.Name}"/>

                <apex:inputField value="{!account.AccountNumber}"/>

                <apex:inputField value="{!account.Phone}"/>

                <apex:inputField value="{!account.Fax}"/>

                <apex:inputField value="{!account.Ownership}"/>

                <apex:inputField value="{!account.NumberOfEmployees  }"/>

                <apex:inputField value="{!account.Contact__c }"/>

                <apex:inputField value="{!account.SLAExpirationDate__c}"/>

                <apex:inputField value="{!account.choose__c}"/>

                <apex:inputField value="{!account.AnnualRevenue}"/>  

                <apex:inputField value="{!account.DateTime__c }"/>      


            <apex:pageBlockSectionItem >

                <apex:outputLabel value="Account Owner"/>

                <apex:inputText value="{!ownName}"/>



                 <!--field for owner Email -->

            <apex:pageBlockSectionItem >

                <apex:outputLabel value="Owner Email"/>

                <apex:inputText value="{!ownerEmail}"/>



             <apex:pageBlockSectionItem >

                <apex:outputLabel value="Parent Account Owner Email"/>

                <apex:inputText value="{!parentOwnerEmail }"/>






   <div align="right" style="display:{!IF(NOT(ISNULL(listOfListAcc)),'block','none')}">


            <apex:commandButton value="Previous" action="{!PreviousButton}" disabled="{!PreviousButtonDisabled}">                            


            <apex:commandButton value="Next" action="{!NextButton}" disabled="{!NextButtonDisabled}">




        <apex:pageBlockSection title="Related Record" columns="1" >


        <apex:pageBlockTable value="{!listAcc}" var="a" rendered="{!(listOfListAcc.size != 0)}" >



                <apex:column value="{!a.AccountNumber}"/>

                <apex:column value="{!a.Phone}"/>

                <apex:column value="{!a.Ownership}"/>

                <apex:column value="{!a.Number_Of_Contact__c}"/>

                <apex:column value="{!a.Contact__c }"/>

                <apex:column value="{!a.SLAExpirationDate__c}"/>-->


      <!--sorting name field-->        

      <apex:column value="{!a.Name}">

        <apex:facet name="header">

           <apex:commandLink action="{!search}" value="Name{!IF(sortExpression=='Name',IF(sortDirection='ASC','▼','▲'),'')}">

             <apex:param value="Name" name="column" assignTo="{!sortExpression}" ></apex:param>





      <!--sorting account number field-->    

      <apex:column value="{!a.AccountNumber}">

        <apex:facet name="header">

           <apex:commandLink action="{!search}" value="AccountNumber{!IF(sortExpression=='AccountNumber',IF(sortDirection='ASC','▼','▲'),'')}">

             <apex:param value="AccountNumber" name="column" assignTo="{!sortExpression}" ></apex:param>





      <!--sorting phone field-->

      <apex:column value="{!a.Phone}">

        <apex:facet name="header">

           <apex:commandLink action="{!search}" value="Phone{!IF(sortExpression=='Phone',IF(sortDirection='ASC','▼','▲'),'')}">

             <apex:param value="Phone" name="column" assignTo="{!sortExpression}" ></apex:param>





      <!--sorting Ownership field-->

      <apex:column value="{!a.Ownership}">

        <apex:facet name="header">

           <apex:commandLink action="{!search}" value="Ownership{!IF(sortExpression=='Ownership',IF(sortDirection='ASC','▼','▲'),'')}">

             <apex:param value="Ownership" name="column" assignTo="{!sortExpression}" ></apex:param>





      <!--sorting Number_Of_Contact__c field-->

      <apex:column value="{!a.Number_Of_Contact__c}">

        <apex:facet name="header">

           <apex:commandLink action="{!search}" value="Number Of Contact{!IF(sortExpression=='Number_Of_Contact__c',IF(sortDirection='ASC','▼','▲'),'')}">

             <apex:param value="Number_Of_Contact__c" name="column" assignTo="{!sortExpression}" ></apex:param>





     <!--sorting Contact__c field-->

      <apex:column value="{!a.Contact__c }">

        <apex:facet name="header">

           <apex:commandLink action="{!search}" value="Contact {!IF(sortExpression=='Contact__c ',IF(sortDirection='ASC','▼','▲'),'')}">

             <apex:param value="Contact__c " name="column" assignTo="{!sortExpression}" ></apex:param>





     <!--sorting SLAExpirationDate__c field-->

     <apex:column value="{!a.SLAExpirationDate__c}">

        <apex:facet name="header">

           <apex:commandLink action="{!search}" value="SLAExpiration{!IF(sortExpression=='SLAExpirationDate__c',IF(sortDirection='ASC','▼','▲'),'')}">

             <apex:param value="SLAExpirationDate__c" name="column" assignTo="{!sortExpression}" ></apex:param>







            <!--displaying the records when there is no record to display-->        

            <apex:outputText rendered="{!OR(listOfListAcc.size == 0,AND(listOfListAcc.size == 1, listAcc.size == 0) )}" value="No records to display." />






<div id="overlay"></div>



  1. Thanks for posting this very useful.

  2. This is very Important topic.
    Here it found in very simple and systematic way.
    Great job Abhi, Thanks. :)