Executing Joins in SOQL - Introduction to Relationship Queries in SOQL.

A common requirement people need to execute on the Force.com platform is to perform a JOIN in SOQL. This creates a unique challenge to many new developers as JOIN is not supported the same way it is in SOQL. In fact, JOIN is not even a valid keyword for SOQL syntax! Frustrating for new folks, but fear not. You can get some JOIN functionality in SOQL queries using what is called SOQL Relationship Queries.

First, a little explanation why you can't perform JOIN function in SOQL. Force.com platform is cloud based and uses a Multi-Tenant architecture. A multi-tenant architecture means that multiple clients are all using the same computing resources. You are sharing the computing resources with other clients, and to prevent developers from impacting the performance of other clients there are some important rules or governor limits put in place. These are the Governor Limits so many people hear about when they get introduced to the platform.

Since JOIN queries can get resource intensive, SOQL puts some limits around them. Instead of being able to do a JOIN you can use a Relationship query to pull in related records. If you have a lookup field on a object, you can perform a Relationship Query on that field to execute one SOQL query and retreive the related records.

For example, Accounts and Contacts have a relationship. Contacts have a one to many relationship to accounts. To perform a Relationship Query to retrieve all Contacts for an Account, you can perform the following SOQL method:

List<Account> accounts = [Select Id, Name, (Select Id, FirstName, LastName from Account.Contacts) from Account limit 10];

Notice the Bold section I have highlighted in the query. That is the relationship query. Notice that is uses the Account.Contacts relationship to perform the query. You can use the Force.com IDE to browse the relationships on your parent objects to retrieve the names to use in the queries.

After executing this, you can iterate over the retrieved children records by accessing a List<SObject> on the parent records. For example, this full Apex code will execute the query, print each accounts name in the system log, and print each contact under each account:

List<Account> accounts = [Select Id, Name, (Select Id, FirstName, LastName from Account.Contacts) from Account limit 10];
For(Account account : accounts)
{
      System.debug('Account Name: ' + account.Name);
      For(Contact contact : account.Contacts)
      {
           System.debug('Contact Name is ' + contact.FirstName + ' ' + contact.LastName);
      }
}

And now the output:

14:01:16.041 (41930000)|USER_DEBUG|[4]|DEBUG|Account Name: Acme
14:01:16.042 (42154000)|USER_DEBUG|[7]|DEBUG|Contact Name is Edward Stamos
14:01:16.042 (42272000)|USER_DEBUG|[7]|DEBUG|Contact Name is Howard Jones
14:01:16.042 (42433000)|USER_DEBUG|[7]|DEBUG|Contact Name is Leanne Tomlin
14:01:16.042 (42674000)|USER_DEBUG|[4]|DEBUG|Account Name: salesforce.com
14:01:16.042 (42845000)|USER_DEBUG|[7]|DEBUG|Contact Name is Marc Benioff
14:01:16.043 (43007000)|USER_DEBUG|[4]|DEBUG|Account Name: Global Media
14:01:16.043 (43121000)|USER_DEBUG|[7]|DEBUG|Contact Name is Geoff Minor

Pretty simple, right? You can execute several relationship queries in one query.

If you want more information, checkout the full documentation on the Force.com support website: http://www.salesforce.com/us/developer/docs/api/Content/sforce_api_calls_soql_relationships.htm

Passing the Salesforce Certified Administrator Exam

I just got home from the testing center here in Chicago and I'm happy to announce that I just passed the Salesforce Certified Administrator Exam (201). I decided to take this exam because its the prerequisite for all the implementation certifications including the Sales Cloud Consultant exam.

If you're already a certified Force.com developer, you'll find there is alot of overlap between the two exams. There were about 10 - 15 questions covering security and sharing rules which overlap between the 201 and 401 exams, which is no surprise since it's fundamental to working with the platform.

The Admin 201 exam had several questions about setting up Sales and Marketing processes, so you should be familiar with those pieces of Salesforce. You also need to know the Setup menu of Salesforce inside and out. How do you setup new users? Where and how can you restrict users login access?

You need to know how Role Hierarchies, Sales Teams, and Account Teams work. There were questions covering Assignment Rules, Escalation Rules for Cases, and how Campaign Members table works (You can assign Contact and Leads to Campaign Members). There are a few reporting questions also for Summary Fields and such.

 Overall, not too bad of an exam. I'd recommend anyone who is thinking about it to dive in. If you follow the study guide that is on the certification site of Salesforce.com you should be golden.

Salesforce Mobile - Formula Fields with Line Breaks Work Around

If you are an admin for Salesforce Mobile you are probably familiar with how easy it is to get setup and running for simple out of the box implementations. However, you might see issues with line breaks in formula fields. Let me explain:

Formula Fields are rendered at the Salesforce Server for Salesforce Mobile and pushed down. So when you have Formula Field which is a text field with line breaks (BR() Function) the field won't look correct in Salesforce Mobile. Instead of seeing a line break like this:

In Salesforce Mobile you will see

This was giving me quite a headache. Here is the simplest solution I could find.

1. Create a new TextArea field in Salesforce to replace your existing Formula Field.

2. Create a Workflow Rule on the Object. Have the Workflow rule execute every time a record is updated or created. Make sure the formula always evaluates to true.

3. Create a new Workflow Field update for the new TextArea field from step 1. Copy your existing Formula Field's Formula into the new value for the Field.

4. Activate the Workflow.

That's it. Now the formula will be evaluated and stored in Salesforce as a String. It will now be displayed properly in both Salesforce and Salesforce Mobile.

It's too bad it doesn't render properly in Mobile out of the box, but this is a liveable work around.

If anyone has a better solution please share!