MK Partners Archive for April, 2009

Some of our recent projects have had very complex requirements that forced us to delve into unchartered terrority: Dynamic Apex. These requirements included changing the filter criteria for a query based on user input, custom merge functionality, and custom cloning a record to a different object. All of these functions are best written using Dynamic Apex. There is little documentation out there on using Dynamic Apex so we’ve decided to share our experiences with you. This is the first in a series of posts which will explain Dynamic Apex and how we used it for these projects.

Dynamic SOQL
With our partners Arrowpointe and Spark3, we recently integrated a php website with salesforce.com. Visitors to the website can browse through a list of contacts filtering on various attributes. In the past, this functionality would be written solely using the PHP Toolkit. However, to ensure consistency and reduce the amount of labor to transition to Force.com Sites, we used the PHP Toolkit to connect to salesforce and then call an Apex Web Service. Apex code does all the work to ensure that the data is queried with the correct criteria and then returned properly. Since the Where clause varies in our query we had to either use Dynamic SOQL or a large number of If/Then statements, we chose the former.

Below is a scaled down sample of code you can use to perform the same dynamic query. We’ve also provided you with the syntax for calling the function.

public class website_WebServices
{
    //Function to perform Dynamic SOQL query on Contacts
    public static List<Contact> getContacts(String whereClause)
    {
        //Sample list of Fields
        String fieldList = 'id, FirstName, LastName, Phone, Email ';

        //Define an Order By clause to sort your query
        String orderBy = ' Order By MailingState ';

        //Define your entire Query as a string
        String qryString = 'SELECT ' + fieldList + ' FROM Contact ' + whereClause + orderBy + ' limit 10';

        //Query salesforce with your Query string
        List<Contact> queryResults = Database.query(qryString);

        return queryResults;
    }

    //Sample Where Clause
    public static String whereClause = 'WHERE Lastname != null ';

    //Sample function to get a list of Contacts by passing the Where Clause to the getContacts function
    public static list<contact> contacts = getContacts(whereClause);

}

Apex has a String method to change all characters to lower case String.toLowerCase and a method to change all characters to upper case String.toUpperCase, but it doesn’t have a method to properly capitalize just the first letter of a name. This can be extremely frustrating when getting data from 3rd party sources that don’t handle it for you. Advanced coders can solve this with a regular expression but for the rest of us, we can use our existing tools like so:

	public static String proper(String s)
	{
		String result = '';
		if (s != null)
		{
			List<String> words = s.split(' ');
			for (String w : words)
			{
				String letter1 = w.substring(0,1).toUpperCase();
				result += letter1;
				if (w.length() > 1)
				{
					String letterx = w.substring(1,w.length()).toLowerCase();
					result += letterx;
				}
				result += ' ';
			}
			result.substring(0,result.length()-1);
		}
		return result;
	}

Several airlines are rolling out wifi on their long-haul flights via vendors like GoGo. I’m happy to report that salesforce.com works perfectly on GoGo. I am even able to use eclipse to refresh my project and and write code. Twitter and GoogleTalk work as well, but YahooIM does not seem to.

BTW, this was written from 32,000 feet over Indiana.