January 30, 2011

Possibility of getting wrong results with SOQL “IN” filter !

In Apex, we all create SOQL queries with “IN” clause for filtering on a set/list of data. An example SOQL fragment could be to filter out all Accounts matching a bunch of Fax numbers, i.e.

// Load contacts on the basis of your biz logic
Contact [] contacts = [Select Id, Fax from Contact WHERE Name like '%Xyz'......];
Set<String> faxes = new Set<String>();
// Populate a collection to further filter on Contact faxes
for (Contact c :  contacts) {
// Find all matching accounts against those contacts
Account[] accounts = [Select Id, Name, Fax from Account where Fax IN =: faxes];

As expected the above code snippet will give you all matching Accounts with same fax numbers as of Contacts. But this will not be the case always, you might get extra non-matching Accounts!

Wondering how ? This is possible if any of the Contact’s fax is NULL or EMPTY, so it will also match all the Accounts with NO fax numbers. Its possible because both Set<> and List<> can validly hold NULL objects and empty strings.

So to avoid any last minute surprises its always recommended to carefully construct collections passing in to SOQL “IN” filter. Above code can be fixed by adding a simple null & empty string check, as shown below

Contact [] contacts = [Select Id, Fax from Contact ......];
Set<String> faxes = new Set<String>();
for (Contact c :  contacts) {
    // FIX 1. Check fax for both NULL and EMPTY string
    if (c.Fax != null && c.Fax.trim().length() > 0)
// FIX 2. Query only if you have any fax numbers
if (!faxes.isEmpty())
    Account[] accounts = [Select Id, Name, Fax from Account where Fax IN =: faxes];

Seems trivial fix Open-mouthed smile? yes it is ! But its usually ignored by many of us.


Would be glad to hear back on your views on this.

Apex API String.format(); Exploring good & bad parts !

I recently came across useful and under documented Apex String API i.e.

String formattedString = String.format(String template, List<String> arguments)

String.format(..) is again based on Java MessageFormat class, its documentation says :

Treat the current string as a pattern that should be used for substitution in the same manner as apex:outputText.

Nice part of this String API is that it supports basic text substitution for ex.

// Create a Template String, that has tokens of form {index} 
String templateString = 'Hello {0}, Good to see you in {1}';
// String argument list or array matching each {index} in Template String.
String[] arguments = new String[] {'Ram' , 'India'};
// Call String.format() to get the token replaced
String formattedString = String.format(templateString, arguments);
// output : Hello Ram, Good to see you in India

Bad part about this String API is it only supports String arguments for token replacement. Though both Java’s MessageFormat and Visuaforce apex:outputText tag accept non-string arguments like Date, Datetime & Currency and are capable of doing very smart formatting with them. For ex. in visualforce one can format date using outputText as follows.

<apex:outputText value="The formatted time right now is: 
    {0,date,yyyy.MM.dd G 'at' HH:mm:ss z}">
   <apex:param value="{!NOW()}" />

output: The formatted time right now is: 2004.11.20 AD at 23:49:02 GMT  

Similar Java example with more options would be

Object[] arguments = {
     new Integer(7),
     new Date(System.currentTimeMillis()),
     "a disturbance in the Force"

 String result = MessageFormat.format(
     "At {1,time} on {1,date}, there was {2} on planet {0,number,integer}.",

 output: At 12:30 PM on Jul 3, 2053, there was a disturbance
           in the Force on planet 7.

Sadly, if you try to do something similar with Apex it always fails for StringException.

Expected change in Apex String.format () API

Apex String.format (String pattern, String[] arguments) API, should take Object[] as argument instead of String[], this will help supporting more primitive data types i.e. datatime, date, currency and number etc, just like visualforce apex:outputText tag. This change would be safe and backward compatible, as all existing Apex code using String[] should work with Object[] too Smile

I am also, posting this as an Idea on SFDC Idea Exchange. Please vote this idea, if you agree with my views.

January 23, 2011

No such column on entity error and connections with FLS !

One friend (lets call him Ram) of mine was developing an SFDC integration using Java API. His integration authorizes multiple sfdc users and performs some SOQL calls for business logic on Sobjects like Event.

Application was going pretty well, until one day it crashed for this error

Description,Subject,IsAllDayEvent,DurationInMinutes ^ ERROR at Row:1:Column:48 No such column 'IsAllDayEvent' on entity 'Event'. If you are attempting to use a custom field, be sure to append the '__c' after the custom field name. Please reference your WSDL or the describe call for the appropriate names. - InvalidFieldFault

Ram was surprised to see this ! Because

  • “IsAllDayEvent” is a standard Event object field
  • He can see the field in SOQL Browser and
  • Of course on Event field details page(Setup > Customize > Activities > Event Fields > Field Detail page). 
  • He can also created all day events in Salesforce.

So, why this error came ?

This error came because of “Field Level Security” issue. The user hitting the Java application was on a different SFDC Profile from Ram. Ram was system administrator with super powerful set of permissions. But the user troubled with this error was on a different profile. This profile was not having the required access permissions for this event field, so Salesforce throwed this “No Such Column…” error on making a SOQL call.

Its wired and confusing error message isn’t, the column was actually there, but its just a permission issue. It should be changed to more insightful to say “Field X you are trying to query is not visible to your profile


Its easy, just ensure visibility on FLS(Field Level Security) page for all queried fields. You can see visibility of a Sobject field per profile, by visiting its FLS page.

For this Event case, Goto 

"Setup > Customize > Activities > Event Fields > Field Detail page >  IsAllDayEvent > Set Field-Level Security Page"

On this page, ensure that visible checkbox is turned on for all required SFDC Profiles. For more details, please check screen shot below


Suggestions !

One should always consider to detail about field level security in release notes or user manual for the SFDC App. This FLS detail should include, what at minimum FLS access is required for your application to run smoothly. So identify all Sobject/Fields/Metadata you are accessing and all the target user profiles, ensure mention about the required level of access edit/read for each.


”No Such column error “ might come because of polymorphic keys, so if above story doesn’t solves your problem check this post for more details on troubleshooting the same error for polymorphic keys.

Custom Component for iterating over 1000 items in Visualforce !

This post tells how to get around, a common problem faced in visualforce, i.e. iterating over more than 1000 items. Though after recent releases Apex collections can hold upto 10,000 items, but visualforce can iterate only upto 1000 items, this is a strange limitation Smile

Solution – Custom Visualforce Component !

Though this problem can be cracked using an Visualforce component. This component will give you almost same functionality as of <apex:repeat  … /> tag in visualforce.

This component is not doing anything really too smart. It addresses this 1000 limitation using the pretty common fix i.e. splitting the huge collection into a collection of collection(List<List<Object>>) and repeating in nested manner on both parent and child lists.

Component – “repeat10k”

“repeat10k” is the name of component shown below to iterate upto 10,000 items. Component code comprises of some visualforce markup and apex controller code. 

Component Visualforce markup - Repeat10K

<apex:component controller="Repeat10KController">
 <apex:attribute name="var" type="Object" description="The variable to represent a single var in the iteration." required="true"/>
 <apex:attribute name="collection" description="collection to iterate" type="object[]" required="true"
     assignTo="{!userCollection}" />

<apex:repeat var="lstOfObjs" value="{!wrappedCollection}">
    <apex:repeat value="{!lstOfObjs}" var="rec">
        <apex:componentBody >
            <apex:variable var="{!var}" value="{!rec}"/>     


Component apex controller - Repeat10KController

public class Repeat10KController {

    // Binds with the user provided value
    public Object[] userCollection {get;set {
       wrappedCollection = toCollectionOfCollection(value);       

    // derived collection created, from user provided collection 
    // of upto 10000 items
    transient public List<List<Object>> wrappedCollection  {get; private set;}
        Converts a collection of Object to 
        collection of collection with parent collection holding upto 1000 items
    static List<List<Object>> toCollectionOfCollection(List<Object> coll){
        List<List<Object>> mainList = new List<List<Object>>();
        List<Object> innerList = null;
        Integer idx = 0;
        for(Object obj:coll){
            if (Math.mod(idx++, 1000) == 0 ) {
                innerList = new List<Object>();
        return mainList;

Testing the Component !

To test this component, I created a visualforce page backed by Apex controller. This page feeds a collection of 10,000 integers to the repeat10k component, and in turn prints out the loop variable in each iteration.

Visualforce Page !

<apex:page controller="Repeat10kTestController" sidebar="false">
  <apex:sectionHeader title="Iterating 10,000 items in Visualforce"/>

  <c:repeat10k collection="{!integers}" var="val" >
     <apex:outputText value="{!val}"/> &nbsp;

Component apex controller

public class Repeat10kTestController {
// Collection of intergers to hold 10000 items
public List<Integer> integers {get;set;}

    // Populate the collection in initalizer block
    integers = new List<Integer>();
    for (Integer idx = 1; idx <= 10000; idx++) integers.add(idx);


Test Page Screen shot !

For sake of demo purposes, on executing above page, prints all the 10,000 items like this.


Known Issues

I tried to make this component generic enough to fit in any type of Collection. But unfortunately this component code will gel with all primitive data types only. This is because visualforce supports static binding for page variables. So we can’t generalize this component to work for any type/class/sobject.

To make it work with other types like Sobjects or UDT(User Defined Types) you need to copy this component and replace all occurrences of Object to the class/sobject name.


Let me know, if you have any more ideas and improvements. For sure one can add support for missing attributes available in <apex:repeat  … /> tag. I kept the code simple for sake of demoing the core purpose.

January 4, 2011

Migrating from Apex XMLDOM Parser to Fast XMLDOM !

I recently helped a friend of mine migrating from Apex classic XMLDOM library to “Fast XML DOM”. The reason he migrated was

  1. Heavy script statement usage for big XML response. Operations like DOM creation, eats a huge chunk of script statement quota. Even after operations like getElementTagName are costlier.
  2. Spring’10 Dom API differences: To solve the above script statements problem, he can migrate to new Dom.Document and Dom.XmlNode classes released in Spring’10. Unfortunately, this API is not following standard W3C DOM API’s, all the method signatures and approach of DOM handling are way different for smooth transition. So a learning curve, plus more testing is required for migrating to Spring’10 Dom classes. More details about this in previous blog post here.
  3. Unamanged Package Issues : Issues in installing unmanaged package, having XmlDom. XmlDom Apex class is available by default in many SFDC Orgs. His plan was to move to managed package later on, but want to hold off that decision, until his force.com app stabilizes for changes.

So I suggested him moving to Fast Xml Dom library, this library solves all the above problems.

What is Fast Xml DOM ?

Fast XML DOM is wrapper over new Apex DOM classes Dom.Document and Dom.XmlNode. Full details on Google Code Project page here.

How Fast XML DOM helped ?

  • As both XMLDOM and FastXmlDOM follow W3C DOM specifications, learning curve is minimal. So one doesn’t needs to learn a new API for DOM operations with XML. All standard methods are available in both API’s like
    • getElementByTagName(..)
    • getElementsByTagName(..)
    • toXmlString()
  • For XMLDOM to FastXmlDOM migration, mostly requires simple token replacement, no logical code changes. One mostly needs two token replacements for these classes [and a few minor changes] i.e.
    • Xmldom > TG_XmlDom
    • XMLdom.Element > TG_XmlNode
  • Eats very less script statements as compared to XMLDOM, as its using native Apex Dom.Document and Dom.XmlNode for Dom operations. Full bench marks of XMLDOM vs FastXmlDOM, available in my previous blog post here.
  • As FastXMLDOM classes are not included by default in new SFDC orgs, chances of unmanaged package installation failure will be less.

Want to start with FastXmlDom, here is the quick start guide.