Utilizing Dynamic Apex Feature in Winter 09 Release!


Well for some of us yet the developer edition is not supporting the all glorious Salesforce CRM winter 09 features such as Dynamic Apex.

However, those who have been able to get their hands on it already know that this release has opened up a lot of new possibilities for developing Visualforce pages. Even though here and there I still stumble over some glitches or nice-to-haves, I have decided to devote some of my time each day to learning the new stuff and incorporate them into my apps.

In this article we will see how we can build Dynamic SOQL queries, execute them, get exactly the records we want and moreover we will see how we can now easier than ever hook our selectLists to object's picklists.

For this example, consider filtering Account records based on Name, Type, Ownership and also we want the user to choose how many records they want to get back on the screen.

Points:
I have used a PanelGrid to put my filter Components in and PageBlockTable to view the results.
The page is pretty standrad, what Winter 09 features are about in this article are all in the Controller's Code.




<apex:page controller="myPageController" tabStyle="Account">
<apex:form >
<apex:sectionHeader title="Customer Search" />
<apex:PanelGrid columns="2" id="panel" cellpadding="2">

<apex:outputLabel value="Account Name: "></apex:outputLabel>
<apex:inputText id="textbox" value="{!searchText}" size="40" style="height:13px;font-size:11px;"></apex:inputText>

<apex:outputLabel value="Type: "></apex:outputLabel>
<apex:selectList size="1" id="accountType" value="{!accountType}">
<apex:selectOptions value="{!accountTypes}"></apex:selectOptions>
</apex:selectList>

<apex:outputLabel value="Ownership:"></apex:outputLabel>
<apex:selectList size="1" id="accountOwnership" value="{!accountOwnership}">
<apex:selectOptions value="{!accountOwnerships}"></apex:selectOptions>
</apex:selectList>

<apex:outputLabel value="Result:"></apex:outputLabel>
<apex:selectList size="1" id="resultNo" value="{!resultNo}">
<apex:selectOption itemValue="50" itemLabel="50"></apex:selectOption>
<apex:selectOption itemValue="100" itemLabel="100"></apex:selectOption>
<apex:selectOption itemValue="150" itemLabel="150"></apex:selectOption>
</apex:selectList>

<div></div>
<apex:commandButton action="{!search}" value="Search" rerender="pageblock"></apex:commandButton>
</apex:PanelGrid>



</apex:form>
<br/>
<apex:pageblock id="pageblock" >
<apex:messages></apex:messages>
<apex:pageBlockSection title="Results" id="results" columns="1" collapsible="true">
<apex:pageBlockTable value="{!accountResults}" var="a" rendered="{!NOT(ISNULL(accountResults))}">
<apex:column value="{!a.Name}"></apex:column>
<apex:column value="{!a.Type}"></apex:column>
<apex:column value="{!a.Ownership}"></apex:column>
<apex:column value="{!a.Industry}"></apex:column>
</apex:pageBlockTable>
</apex:pageBlockSection>
</apex:pageblock>
</apex:page>

In this Controller I have developed a procedure "getObjPicklistValues" which receives a DescribeFieldResult object as parameter, corresponding to the picklist I want to show in the filter components' area. DescribeFieldResult methods provide you with most reflection capabilities you require to be able to programmatically write reusable codes (as you can see in this example).

The other benefit that previously was not an option in Visualforce is Dynamic Apex. Now you can build your query based on user's choices on the fly, then run it against Salesforce database and view the customized data to the user.

In this example, if the user is not entering any value for Account Name or has not chosen any Account type, then simply those fields will not be queried and will be excluded from the SOQL statement.

And the controller's code:


public class myPageController {

private List accountResults;

public List getAccountResults() {

return accountResults;

}

public String searchText

{

get;

set;

}

public String accountType

{

get;

set;

}

public String accountOwnership

{

get;

set;

}

public String resultNo

{

get;

set;

}

public List getAccountTypes()

{

return getObjPicklistValues(Account.Type.getDescribe());

}

public List getAccountOwnerships()

{

return getObjPicklistValues(Account.Ownership.getDescribe());

}

private List getObjPicklistValues(Schema.DescribeFieldResult picklistDesc)

{

List pList = picklistDesc.getPicklistValues();

List items = new List();

items.add(new SelectOption('','--None--'));

for (Schema.PicklistEntry pl : pList) {

items.add(new SelectOption(pl.getValue(),pl.getLabel()));

}

return items;

}

public PageReference search()

{

String query = 'Select name, type, ownership, industry from account ';

String queryLimit = ' limit ' + resultNo;

String conditions = '';

if (searchText != null && searchText != '')

conditions += ' name LIKE \'' + searchText + '%\'';

if (accountType != null)

{

if (conditions != '') conditions+= ' and ';

conditions += ' Type = \'' + accountType + '\'';

}

if (accountOwnership != null)

{

if (conditions != '') conditions+= ' and ';

conditions += ' Ownership = \'' + accountOwnership + '\'';

}

if (conditions != '')

query += ' where ' + conditions;

query += ' order by CreatedDate DESC ';

query += queryLimit;

//apexPages.addmessage(new ApexPages.message(ApexPages.severity.INFO, query));

accountResults = Database.query(query);

return null;

}

}






6 comments:

  1. Sam,

    I get an error when I cut and paste the code to my dev account on line 3:

    private List accountResults;

    Compile Error: expecting a left angle bracket, found 'accountResults'

    Any ideas?

    ReplyDelete
  2. I also get an error stating:
    private List accountResults;
    Error: Compile Error: expecting a left angle bracket, found 'accountResults' at line 2 column 13

    Any solution for this?

    ReplyDelete
  3. This is great but it has errors
    private List accountResults;

    Compile Error: expecting a left angle bracket, found 'accountResults'

    And then the List for every method is incorrect...
    Any ideas of how to fix it

    ReplyDelete
    Replies
    1. Yes, i've got ideas to fix it :

      public class myPageController {
      private List accountResults;
      public List getAccountResults() {
      return accountResults;
      }
      public String searchText
      {
      get;
      set;
      }
      public String accountType
      {
      get;
      set;
      }
      public String accountOwnership
      {
      get;
      set;
      }
      public String resultNo
      {
      get;
      set;
      }
      public List getAccountTypes()
      {
      List Options = new List();
      Schema.DescribeFieldResult F = Account.Type.getDescribe();
      List pList = F.getPicklistValues();
      Options.add(new SelectOption('','--None--'));
      for (Schema.PicklistEntry pl : pList)
      {
      Options.add(new SelectOption(pl.getValue(),pl.getLabel()));
      }
      return Options;
      // instead of : return getObjPicklistValues(Account.Type.getDescribe());
      }
      public List getAccountOwnerships()
      {
      List Options = new List();
      Schema.DescribeFieldResult F = Account.Ownership.getDescribe();
      List pList = F.getPicklistValues();
      Options.add(new SelectOption('','--None--'));
      for (Schema.PicklistEntry pl : pList)
      {
      Options.add(new SelectOption(pl.getValue(),pl.getLabel()));
      }
      return Options;
      // instead of : return getObjPicklistValues(Account.Ownership.getDescribe());
      }

      public PageReference search()
      {
      String query = 'Select name, type, ownership, industry from account ';
      String queryLimit = ' limit ' + resultNo;
      String conditions = '';
      if (searchText != null && searchText != '')
      conditions += ' name LIKE \'' + searchText + '%\'';
      if (accountType != null)
      {
      if (conditions != '') conditions+= ' and ';
      conditions += ' Type = \'' + accountType + '\'';
      }
      if (accountOwnership != null)
      {
      if (conditions != '') conditions+= ' and ';
      conditions += ' Ownership = \'' + accountOwnership + '\'';
      }
      if (conditions != '')
      query += ' where ' + conditions;
      query += ' order by CreatedDate DESC ';
      query += queryLimit;
      //apexPages.addmessage(new ApexPages.message(ApexPages.severity.INFO, query));
      accountResults = Database.query(query);
      return null;
      }
      }

      Delete
  4. any idea of how to check for this condition
    [select xxx from yyy where id in :listids]
    using database.query();

    ReplyDelete
    Replies
    1. public class myPageController {
      private List accountResults;
      public List getAccountResults() {
      return accountResults;
      }
      public String searchText
      {
      get;
      set;
      }
      public String accountType
      {
      get;
      set;
      }
      public String accountOwnership
      {
      get;
      set;
      }
      public String resultNo
      {
      get;
      set;
      }
      public List getAccountTypes()
      {
      return getObjPicklistValues(Account.Type.getDescribe());
      }
      public List getAccountOwnerships()
      {
      return getObjPicklistValues(Account.Ownership.getDescribe());
      }
      private List getObjPicklistValues(Schema.DescribeFieldResult picklistDesc)
      {
      List pList = picklistDesc.getPicklistValues();
      List items = new List();
      items.add(new SelectOption('','--None--'));
      for (Schema.PicklistEntry pl : pList) {
      items.add(new SelectOption(pl.getValue(),pl.getLabel()));
      }
      return items;
      }
      public PageReference search()
      {
      String query = 'Select name, type, ownership, industry from account ';
      String queryLimit = ' limit ' + resultNo;
      String conditions = '';
      if (searchText != null && searchText != '')
      conditions += ' name LIKE \'' + searchText + '%\'';
      if (accountType != null)
      {
      if (conditions != '') conditions+= ' and ';
      conditions += ' Type = \'' + accountType + '\'';
      }
      if (accountOwnership != null)
      {
      if (conditions != '') conditions+= ' and ';
      conditions += ' Ownership = \'' + accountOwnership + '\'';
      }
      if (conditions != '')
      query += ' where ' + conditions;
      query += ' order by CreatedDate DESC ';
      query += queryLimit;
      //apexPages.addmessage(new ApexPages.message(ApexPages.severity.INFO, query));
      accountResults = Database.query(query);
      return null;
      }
      }

      Delete