Sunday 29 January 2012

Tricky SOQL Queries

A typical SOQL Query return a list of Opportunity records(with conditions) may be in the following pattern,


Database.getQueryLocator([SELECT Name,Id,StageName from Opportunity StageName='Closed Won']).


The above case may work only if our returned records meeting Query conditions are fixed, as StageName='Closed Won' above.
Hence specifying SOQL directly between square brackets may not work in all conditions, as above.
Implying that it works only at compile time costing to run the query again and again.


Quite simple solution for this is, to assign the SOQL Query to string variable and then by appending query/filter conditions to the assigned string variable at run time as below.


string strQuery='SELECT Name, Id, closedate, StageName from Opportunity WHERE StageName=\'Closed Won\'';


Suppose if we want to classify/filter the records based on a VF page field value (in this case Name LIKE %Univ%),then


strQuery+=' AND Name LIKE \'%Unive%\'';


Finally run the Query as below,


Database.getQueryLocator(strQuery);


Makes sense, isn't it?


Note: In this case, use the field value between \' and \' as we try to specify a string inside another string in a SOQL Query.

Friday 27 January 2012

Filtering Opportunity records based on Multiple filters

Although this post is almost similiar to my previous post, it illustrates an another way of designing a search page. 

The search page was designed to display records/results based on the field filters namely
1. Lead Source
2. Type
3. Stage
from Opportunity object  as shown below,

Filtering Opportunity records based on Multiple filters


Hence, it supports multiple filter setting and displays the results accordingly on a click. 
You can try this from my developer site.

This post can be considered as an another example for proving the user friendly nature of salesforce.
Developers can develop pages in a variety of ways for the same requirement, which is the idea behind this post.


Thursday 26 January 2012

Dynamic Search Controller for Contacts

Hi All,

The ability of Apex and Visualforce was well depicted in this Dynamic Search Controller for Contacts post.


Dynamic Search Controller for Contacts


It makes use of FirstName, LastName, LeadSource fields from Contact Object for the purpose of seting filter criteria. Based on these filter criteria, the Search Results Page Block table displays the results. Note that, we don't need to click "Refresh" or "Search" button as it performs the action dynamically based on the changes in the filters.  

I have rendered three radio buttons, for the purpose of classifying the displayed results based on with or without account criteria as shown below.

VisualForce Page :

<apex:page controller="CustomContactSearchCont" sidebar="false">
<apex:outputLink value="http://www.salesforceforall.blogspot.com">
<apex:sectionHeader title="Contact" subtitle="Search"  help="http://www.salesforceforall.blogspot.com"/>
</apex:outputLink>
<apex:pageMessages />
<script type="text/javascript">
var buttonValue="withwithout";


    function doSearch()
    {
        searchServer(document.getElementById("firstName").value, 
        document.getElementById("lastName").value, 
        document.getElementById("leadSource").options[document.getElementById("leadSource").selectedIndex].value, buttonValue);   
    }
    function whichButton()
    {        
        for (i = 0; i < document.myForm.group.length; i++) 
        {
            if (document.myForm.group[i].checked) 
                buttonValue = document.myForm.group[i].value;
        }  
        doSearch();     
    }
    function doo(fname,lname,aa)
    {
        if(window.confirm("You are about to delete the Contact "+fname+" "+lname+'\n'+"Are you sure?"))       
        {
            delAcc(aa);
        }
    }
</script>
<apex:form >
    <apex:actionFunction name="searchServer" action="{!runSearch}" reRender="results">
        <apex:param name="firstName" value=""/>
        <apex:param name="lastName" value=""/>
        <apex:param name="leadSource" value=""/>
        <apex:param name="butVal" value="{!selectedacc}"/>        
    </apex:actionFunction>
    
    <apex:actionFunction name="delAcc" action="{!delTheAccount}" reRender="results">
        <apex:param name="identity" value="" assignTo="{!selectedacc}"/>
    </apex:actionFunction>


</apex:form>
<form name="myForm">
<table><tr><td valign="top" width="20%">
    <apex:pageBlock title="Search Criteria" mode="edit">
        <table>
            <tr><th>{!FirstNameLabel}</th></tr>
            <tr><td><input type="text" id="firstName" onkeyup="doSearch();"/></td></tr>
            <tr><th>{!LastNameLabel}</th></tr>
            <tr><td><input type="text" id="lastName" onkeyup="doSearch();"/></td></tr>
            <tr><th>{!LeadSourceLabel}</th></tr>
            <tr><td><select onchange="doSearch()" id="leadSource">
                       <option></option>
                       <apex:repeat value="{!LeadSourceVal}" var="values">
                            <option>{!values}</option>
                        </apex:repeat>
                    </select></td></tr>
            <tr><td><br/><br/></td></tr>
            <tr><th>with Account</th><th><input type="radio" name="group" value="with" id="with" onclick="whichButton()" /></th></tr>           
            <tr><th>without Account</th><th><input type="radio" name="group" value="without" id="without" onclick="whichButton()"/></th></tr>
            <tr><th>with/without Account</th><th><input type="radio" name="group" value="withwithout" id="withwithout" onclick="whichButton()" checked="true" /></th></tr>
        </table>
    </apex:pageBlock>
    </td>
    <td width="80%" valign="top">
    <apex:pageBlock title="Search Results" mode="detail">          
    <apex:outputPanel id="results">
        <apex:pageBlockTable value="{!cons}" var="con">
            <apex:column headerValue="Action">
               <apex:outputLink title="" value="/{!con.id}/e?retURL=/apex/{!$CurrentPage.Name}" style="font-weight:bold">Edit</apex:outputLink>&nbsp;|&nbsp;               
                <a href="#" onclick="doo('{!con.FirstName}','{!con.LastName}','{!con.Id}');"><b>Del</b></a>
            </apex:column>            
            <apex:column headerValue="{!FullNameLabel}">
            <apex:outputLink value="/{!con.Id}">
                {!con.Name} 
            </apex:outputLink> 
            </apex:column>
            <apex:column value="{!con.FirstName}" headerValue="{!FirstNameLabel}"/>
            <apex:column value="{!con.LastName}" headerValue="{!LastNameLabel}"/>
            <apex:column value="{!con.account.Name}" headerValue="Account Name"/>
            <apex:column value="{!con.LeadSource}" headerValue="{!LeadSourceLabel}"/>
        </apex:pageBlockTable>
    </apex:outputPanel>
    </apex:pageBlock>       
    </td></tr></table></form>
    <center><apex:outputLink value="http://www.salesforceforall.blogspot.com"><b>Love Your Technology</b></apex:outputLink></center>
</apex:page>


Controller :
public with sharing class CustomContactSearchCont 
{
    public List<Contact> cons{get;set;}
    public String strQuery;
    public String bv='';
    public String selectedacc{get;set;}
    
    public CustomContactSearchCont()    
    {    
        initstrQuery();
        cons=Database.Query(strQuery);
    }    
        
    public void initstrQuery()
    {
        if(bv==''||bv=='withwithout')
            strQuery='SELECT Name, FirstName, LastName, Account.Name, LeadSource from Contact WHERE Id!=null';
        if(bv=='with')
            strQuery='SELECT Name, FirstName, LastName, Account.Name, LeadSource from Contact WHERE Account.Name!=null';
        if(bv=='without')
            strQuery='SELECT Name, FirstName, LastName, Account.Name, LeadSource from Contact WHERE Account.Name=null';
    }    
    

    public PageReference runSearch()
    {

        String fn=ApexPages.currentPage().getParameters().get('firstName');        
        String ln=ApexPages.currentPage().getParameters().get('lastName');
        String ls=ApexPages.currentPage().getParameters().get('leadSource');   
        bv=ApexPages.currentPage().getParameters().get('butVal');    
        initstrQuery();       
        if(!fn.equals(''))
                strQuery+=' AND FirstName LIKE \''+fn+'%\'';
        if(!ln.equals(''))        
                strQuery+=' AND LastName LIKE \''+ln+'%\'';
        if(!ls.equals(''))
                strQuery+=' AND LeadSource=:ls';
        try 
        {
            cons = Database.query(strQuery);
        }
        catch (Exception e) 
        {
            ApexPages.addMessage(new ApexPages.Message(ApexPages.Severity.ERROR, 'Ooops!'));
        }             
        return null;
    }
    
    public PageReference delTheAccount()
    {
        if(selectedacc!=null)
        {
            Contact ct= Database.Query('SELECT Id, Name, FirstName from Contact WHERE Id=:selectedacc');
            Delete ct;  
            PageReference testPage = Page.CustomContactSearchPage;
            testPage.setRedirect(true);
            return testPage;           
        }
        return null;
    }
    
    /*Getting Field Labels and Picklist Values*/
    public string FullNameLabel
    {
        get
        {
            Schema.DescribeFieldResult f=Contact.Name.getDescribe();
            return (string) f.getLabel();
        }
        set;
    }
    
    public string FirstNameLabel
    {
        get
        {
            Schema.DescribeFieldResult f=Contact.FirstName.getDescribe();
            return (string) f.getLabel();
        }
        set;
    }
    
    public string LastNameLabel
    {
        get
        {
            Schema.DescribeFieldResult f=Contact.LastName.getDescribe();
            return (string) f.getLabel();
        }
    }
    
    public string LeadSourceLabel
    {
        get
        {
            Schema.DescribeFieldResult f=Contact.LeadSource.getDescribe();
            return (string) f.getLabel();
        }
        set;
    }
    
    public List<String> LeadSourceVal    
    {        
        get
        {
            if (LeadSourceVal  == null)
            { 
                LeadSourceVal= new List<String>();
                Schema.DescribeFieldResult field = Contact.LeadSource.getDescribe();
                for (Schema.PicklistEntry f : field.getPicklistValues())                  
                LeadSourceVal.add(f.getLabel());          
            }          
            return LeadSourceVal;                  
        }        
         set;    
    }      
}


Hope that, this post will be very useful for beginners. 

You can run the example from my developer site.