Entries in coding (29)


SOQL Offset

Hi guys, sorry - it's been a while. Lots going on lately but I wanted to catch everyone up on something I found handy the other day - the OFFSET clause on a SOQL query. It was added as part of API 24.

OFFSET can be used as an efficient way to handle large result sets


Use Case

 You want to sit down with your significant other and watch a marathon of Walking Dead. I'm talking all the episodes from the pilot to the most recent episode. There is just one catch - you watched the first four epidoes last weekend and you want to skip straight to episode 5 "Wildfire" and you're using SOQL. Okay, I realize this is far fetched but we've made it this far so let's stay with it a little longer and see how Offset can help us write a SOQL statement that will help us out...

Offset is like saying "give me the first X records but skip some records before you start the counter"


Select, Id, Name, Synopsis, OriginalAirDate from Walking_Dead__c OrderBy OriginalAirDate Limit 100 Offset 4

This will give you records 5 through 100.

Best Practices

  • Use an ORDER BY clause when you use OFFSET to keep your results consistent. The row order of a result set that has no ORDER BY clause isn't going to be stable.
  • It's also smart to use a LIMIT clause in combination


Things to keep in mind with SOQL OFFSET

  1. The maximum offset is 2,000 rows (as of Spring 15'). If you try to pass in a number larger than 2,000, you'll get a nice little NUMBER_OUTSIDE_VALID_RANGE  in return.
  2. Offset is intended to used as part of a top-level query. It's not meant (and not allowed) in most sub-queries. If you try to add an Offset in a sub-query you will often get a  MALFORMED_QUERY.
  3. Offsets ARE NOT A REPLACEMENT for using queryMore()



Explore the Different Messaging Options in VisualForce (Part 2)

This post begins where Part 1 left off; continuing to explore the different messaging options in VisualForce.

  • apex:message allows for field-specific errors. Let's look the code below and see how it renders:
<apex:page controller="TestMessageController">
    <apex:form >
        <apex:outputLabel value="Test String" /><apex:inputField value="{!test.Name}" id="test"/>
        <br/><apex:message for="test"/>
public class TestMessageController{
    public Account test {get;set;}
    public TestMessageController(){
        test = new Account();
  • The last option we have is apex:messages, which is similar to apex:message, except that it displays all errors in a list. Let's incorporate the apex:messages in the previous code and see how it renders:
<apex:page controller="TestMessageController">
    <apex:messages />
    <apex:form >
      <apex:outputLabel value="Test String" /><apex:inputField value="{!test.Name}" id="test"/>
      <br/><apex:message for="test"/>
public class TestMessageController{
    public Account test{get;set;}
    public TestMessageController(){
        test = new Account();

Configuring State & Country Picklists

in Salesforce Spring ‘13

Well, Salesforce finally did it. They’ve introduced State and Country picklists to their 2013 Spring release. Phew! No more messy (and often erroneous) text-entry fields. These picklists will allow you to choose a state and country based on a selection in a dropdown menu.

Hold onto your party hats though because it’s not as easy to implement as you might think. You’ll need to do a little behind-the-scenes work to configure state and country picklists so that they work in your organization.

First things first. You’ll need to configure the picklists in the Metadata API and then scan your organization to see where text-based state and country data is used. Next you’ll convert your existing data and fix the existing customizations so that they play nice with the new, standardized values. Finally, you can enable the lists for your users.

The State and Country Picklists page in the Data Management area of Setup is where you’ll spend most of your time preparing your organization to use the picklists. Thankfully, the process is outlined and most of the steps can be executed right there. You can also click “Help for this Page” to get the full documentation for the feature.

The most obvious step for administrators is configuring the picklists in the Metadata API. Using the IDE, you’ll need to edit the AddressSettings metadata component, which is new in Metadata API 27.0. AddressSettings allows you to control which picklist values appear in the Salesforce UI and how to map existing, text-based state and country values to new the picklist values, making the convert process using the address conversion tool in Setup a breeze. The AddressSettings component has four fields for each state and country you enable, and it will look pretty similar to this abbreviated example when you’ve configured them:


<?xml version="1.0" encoding="UTF-8"?>


<AddressSettings xmlns="">








<integrationValue>United States</integrationValue>




<label>United States</label>













































Have fun with the new picklists feature and check back with us for more news soon!


Cross Object Formula Fields

Salesforce allows for cross-object referencing

In many Salesforce orgs, it's become a great need to be able to reference fields of an object to a field in another object. Fortunately, Salesforce allows for cross-object referencing and in this tutorial I'll show you a greate way to map a field to both Accounts and Contacts on convert:


This code allows a Cross Object formula field to get the value of the field on contact and account:





Validate Salesforce Account


Today, I'll show you a great way to validate an Account based on the following conditions:


  1. If Budget (Number Field)exceeds 10,000
  2. If Decision (Picklist Field) equals Yes
  3. If Timeline (Date field) is not blank


All above conditions must be true for the Custom/Picklist field to be set to "Yes". Here is how to do it: 


  • First, create a custom field (is Account Qualified?)
  • Set the Custom Field type to Formula with Text is output type
  • Copy and paste  the code below:


IF( AND( Budget__c >9999, ISPICKVAL(Decision_c,"Yes"), ISBLANK(Timeline__c)), "Yes", "No")





Apex Unit Test



I’ve been developing code on various platforms for years, but lately I’ve been working on Saleforce’s proprietary language apex.  If you know java then apex should not be that strange to you at all.  That is pretty much where the similarities end.  Being a cloud based company, Salesforce enforces all sorts of rules and limits on your code. You’ll quickly learn how to be a smarter developer when you have to think about these limits. 

 Salesforce requires that you have 75% coverage on all code you write.  If you fall below this number then you cannot deploy any of the code you have written.  Coverage just means that if you have piece of code that says “hello” you must test it and verify that it says hello.  If not, every line is counted against your overall number.  You cover your code by writing the infamous unit tests.

Rewind to last month and it is really easy to forget or ignore writing test cases.  You can only get away with this for so long before your coverage starts gradually depleting.  After much effort I did a good job addressing a lot of missing coverage.  One thing that became really evident is that I was failing to do a negative test.  A negative test is just what it sounds like.  You pass in junk data and expect a failure.  This proves that your code not only works, but it handles exceptions correctly.  Below I have some sample code that illustrates both a positive and a negative test case.


           static testMethod void testremoveFormulaFieldsSchema()



              //Negative Test of formula field schema

              Map<String, Schema.SObjectField> targetSchemaFieldMap = null;

              targetSchemaFieldMap = removeFormulaFieldsSchema(targetSchemaFieldMap);

              system.assert(targetSchemaFieldMap == null, 'We did not get a null schema ');


              //Positive Test of formula field schema

              targetSchemaFieldMap = Schema.SObjectType.Account.fields.getMap();

              targetSchemaFieldMap = removeFormulaFieldsSchema(targetSchemaFieldMap);

              system.assert(targetSchemaFieldMap != null, 'We failed to get the account schema ');



As you can see above I call the same method “removeFormulaFieldsSchema” in both scenarios but in the first test I am passing null as a parameter.  This will test our code on how I handle exceptions and I expect nothing back after the code is executed.  In the second example I would expect data to be returned to me.  I also included the actual method I was testing for clarity below.  These two tests gave me 100% coverage in my new method and I was a happy person.


       public static Map<String, Schema.SObjectField> removeFormulaFieldsSchema(Map<String, Schema.SObjectField> schemaMap)


         Map<String, Schema.SObjectField>  resultschema = new Map<String, Schema.SObjectField>();




                for(String field : schemaMap.keyset())



                  Schema.DescribeFieldResult desribeResult = schemaMap.get(field).getDescribe();


                  if (!desribeResult.isCalculated())


                    //Field is not a formula field so it is added to the schema.

                    Schema.SObjectField fieldS = schemaMap.get(field); 




                 return resultschema;

              }catch(exception e)


                     return schemaMap;



I hope this helps some apex coders out there to understand the importance of getting your unit test written with your new code.  It was a lot of effort to go back and fix all the omissions but it paid off.  I even found some bugs in the code that I didn’t expect.  Who would have thought that unit testing actually works and isn’t just some evil chore Salesforce imposes on us.


photo credit: <a href="">epospisil</a> via <a href="">photopin</a> <a href="">cc</a>


Turn a Salesforce Hyperlink into a Button:


If you're using Salesforce and have a Hyperlink that uses formula fields, you can make it stand out as an action by converting it into a Salesforce button. Here is what you need to do: 


  1. Create an image of the button and store it as a document
  2. Create a Hyperlink field 
  3. Use the following code to convert the Hyperlink into a button

 HYPERLINK("/servlet/servlet.Integration?lid=01N300000008kgN&eid=" & {!Id} , IMAGE("/servlet/servlet.Images?oid=00D304011111xYG&id=0153111110400F5uW", "Create a New Opportunity"))

Here is how the code functions:


  • The first half of the code builds the link
  • The second half of the code displays the image you created for the button



That's it!


Tell me in the comments how this could be helpful as an admin!





Fix System Query Exception Error


What the Heck is This? "System.QueryException: List Has No Rows for Assignment to SObject"

So I was quering Account a = [Select name from account where id=: strld];

and an exception was thrown: System.QueryExceptoin: List has no rows for assignment to SObject.


Here is the mistake I made and how to fix it:

List<Account>lst = [Select name from account where id=: strld];


Return State Region based on Billing State

This code will return reagions of states based on teh billing state in your salesforce. 

IF(AND(CONTAINS("AR:AZ:UT:LA:CO:HI:NM:TX:KS:OK:MS", billingState),(Tier__c= "CORPORATE")),              "West / South", IF(AND(CONTAINS("NV:NE:OR:MN:WA:IA:ID:IL:MT:WI:WY:AK:ND:SN", billingState), (Tier__c= "CORPORATE")), "West /North", IF(AND(CONTAINS("ME:NH:VT:MA:RI:CT:NY", billingState), (Tier__c= "CORPORATE")), "East/North", IF(AND(CONTAINS("PA:NJ:MD:DE:DC:IN:VA:WV:KY:OH:MI", billingState), (Tier__c= "CORPORATE")), "East /Mid", IF(AND(CONTAINS("TN:AL:NC:SC:MS:GA:FL", billingState), (Tier__c= "CORPORATE")), "East /South", IF(AND(Region__c = "East", Tier__c= "Enterprise"), "East", IF(AND(Region__c = "West", Tier__c= "Enterprise"), "West", IF(Region__c = "Canada", "Canada", "Other"))))))))