SQLForce – ANSI SQL for SalesForce

There are great tools for SalesForce that we don’t hear much about:

SQLForce – Command line interpreter with ANSI SELECT/DELETE/MODIFY//INSERT commands that work with Salesforce.



How to resize the Apex/VisualForce Editor in the Salesforce UI

When using the Salesforce UI to edit apex/visualforce pages in a larger monitor, usually there is a lot of unused space on the screen.

Salesforce UI default editor

So if you like to use the Salesforce UI instead of Eclipse and have a big monitor, here is a little javascript URL to resize the editing box.

I have created a bookmark with it and use it everytime I need to edit something:

javascript:document.getElementById(“frame_ApexClassEditPage:theTemplate:theForm:thePageBlock:editor:codeeditor:buffer”).style.height=prompt(“What size in pixels?”,”750″)+”px”;return false;

It will ask how many pixels the height should be. 750 is usually a good size for a maximized window in a 20″ monitor.

Salesforce UI editor resized

My 1st little App Exchange: SOQL Query Tool

As a little exercise, I’ve created a small tool comprised of a VisualForce page/tab that allows system administrators to run ad-hoc SOQL (SOSL and aggregate queries) from within their applications and do mass update on every page of 100 records. Basically, it reproduces a little of the functionality of the DeveloperForce utility Mass Update Anything.

This tool is available at the AppExchange for free:   goo.gl/90V8f

(The listing is private because SalesForce seems to only allow SalesForce Pardners – that is, companies – to take their apps to security review and make them public.)

SOQL Query Tool screenshot

The results of an ad-hoc query executed in SOQL Query Tool (click to expand).

In one class I used ApexPages.StandardSetController with a Database.QueryLocator to implement pagination of the query results.

objStdSetCtrl = new ApexPages.StandardSetController( Database.getQueryLocator( SOQLText ) );

In the VisualForce page I used the tag apex:repeat to display a dynamic list of columns nested in an apex:pageBlockTable that iterates on the query results’ rows.

<apex:pageBlockTable id="ResultsTbl" value="{!QryResults}" var="row">
    <apex:repeat id="ColumnsRpt" value="{!LstColNames}" var="col">
         <apex:column >
                <apex:outputLink rendered="{!( col == 'Id' || col == 'Name' )}" value="/{!row.id}" target="new">
 <apex:outputText value="{!row[col]}" />

What is a Lead, Account, Contact, and Opportunity in salesforce.com?

Very concise definitions on the article linked below:


Another reason as to why an user’s SalesForce login may fail

This week I got an issue report from a user saying that the password was not working anymore.

I’ve had reset the password, which makes SalesForce email a link to the user to accept a new password and allow the user in.

Then the user reported that it had happened again.

I was starting to think that the user must have had an account with a different org but, fortunately, this time, the user volunteered to send a screenshot:

The relevant detail on the screen is the DOT between the names on the email address.

The user’s actual email has an UNDERSCORE so the wrong email was being used.

It is easy to miss such a minuscule detail and waste a lot of time looking for the problem elsewhere.

Fixing Word document printing from SalesForce

Some users will have a different configuration in their desktops that will prevent printing a Word document attached to a SalesForce contact.

Below is the typical error when the user attempts to open the attachment and then print:

A solution that worked for one of my users is from this Microsoft link:

A new application window opens when you try to view an Office 2010 document in Internet Explorer 7 or in Internet Explorer 8

This Microsoft page has a convenient button that allows the user to implement the fix with no technical knowledge:


Once the fix is applied, all Microsoft Office documents (including Word) will open within the browser and the user can then click on the browser’s Print icon on the upper right corner:

If users prefer to get to the print dialog first, they can click on the down arrow:

If however, this fix doesn’t work, another Microsoft link promises to reverse the change: http://support.microsoft.com/kb/162059

Another item to look for is whether printing from the internet is disabled on the user’s desktop.

This option is seen on the following registry setting: HKEY_CURRENT_USERSoftwarePoliciesMicrosoftInternet ExplorerRestrictions

There should be an entry named “NoPrinting” with value 1, which should be changed to 0.

Facilitating removal of duplicate contacts

Purpose: display a link to the Merge Contacts Wizard from the search screen and from the contact screen

Too many times users search for a contact but end up creating a duplicate anyway.

Most of the time is because they use the first contact that appears and they leave blank the fields you configured in DupeCatcher.

What follows is a way that – hopefully – will make it easy for them to remember to avoid and remove duplicates.

Even if it doesn’t help with that, at least it will make it more convenient to merge duplicates by giving a link to the Merge Contacts Wizard from the search screen and from the contact screen.

1) Create a field named Merge Duplicates (Merge_Contact_URL__c) as Formula(Text) and enter the formula as:

HYPERLINK(‘/merge/conmergewizard.jsp?id=’ & AccountId & ‘&srch=’ & FirstName & ‘+’ & LastName, ‘Merge Duplicates’, ‘_blank’)

2) Enter Description as: Link for opening this contact in the Merge Contact Wizard

3) Enter Help Text as: Click this link if you want to find and merge duplicates for this contact

4) Then add to the Contact Search layout:

5) The convenient link will appear on your search results like this:

6) You can also create a custom button that allows the same function from the Contact itself:

7) Just remember to add the custom button to the page layout:

Comparing SalesForce with MicroSoft Dynamics xRM

MicroSoft Dynamics (xRM)

SalesForce.com (SF)

Design * offers more options during entity creation* to make changes to a field, it has to be deleted then recreated and if there are forms/views with that field, such dependencies have to be deleted first * easier to create entities (objects)* fields can be changed/deleted and all the related forms are automatically updated

* Master-Detail relationships include cascaded deletes and detail roll-ups (sum, count, average, etc) but M-D relationships can’t be chained, which results in having to implement them as Lookups and lose the benefit of summarization offered by Master-Relationships

Data Manipulation * smarter data import tool with lookup of foreign keys by ID or description* doesn’t have the UpSert function (update existing records and insert new ones) that SalesForce has

* uses SQL Server

* smart data import (lookup of foreign keys)* requires external tool and has the little quirk of having to use UpSert instead of Insert

* the UpSert function updates existing records and insert new ones in one run

* the SF query language (SOQL) has limitations

Infrastructure * can be hosted on premises* no Governor limits if hosted in house

* on premises installation/configuration takes a long time

* there is no option to host SF on premises* subject to Apex Governor Limits because SF is a multi-tenant platform

* automatic upgrades twice a year, each release with a lot of new features

Bugs * error messages popup often although requiring only a retry from the same screen without closing/reopening * no bugs found
Speed * slow, not very responsive * very responsive
Usability * the user interface is familiar to MS Office users but it is very busy

* can be embedded in Outlook

* integrated with Sharepoint

* very basic/light-weight mobile web app (will likely require 3rd party for mobile access)

* offers a much cleaner/simpler interface* all users like it

* Outlook and Sharepoint integration requires a lot more effort

* SF is mobile ready

Customization /Programming * .net:  Visual Studio, C#, VB* seems very flexible * requires unit tests to publish triggers and classes* default filter screens can be customized only to a certain degree and beyond the basics, it requires coding

* requires creation of report types, then reports that use them

* Apex is very similar to Java and C#

* subject to Apex Governor Limits

* limited deployments to Production in a given period as per the contracted level of service

Workflows * offers more types of process steps and seems more cohesive than SF * there are 2 types:  approval process (triggered by the user) and workflow (triggered when a record changes in a specific way)
Forward thinking considerations * MS is one of the world’s largest R&D spenders * clearly the leader in CRM

* SF spends 8% of revenue in R&D while (2008 data) and 5x as much in sales and marketing

* SF buys many other companies and integrate their proven technologies into the platform

* customer base is very large

* the company leader is a visionary

Cost * measured roughly 1/3 to 1/2 of SF’s cost in 1, 3 and 10 years for a high number of users* can be paid monthly * entry price is cheaper (Group edition with 5 users)* annual upfront payment

The above is based on my experience with a proof of concept application and online research. Feel free to comment and add your corrections or a different point of view.

Pertinent links:

– MS Dynamics 2011 vs salesforce.com
Developer frustration
MicroSoft Dynamics CRM vs SalesForce – How do you choose?
Microsoft Dynamics CRM vs. Salesforce.com vs. Microsoft Office Business Contact Manager
– Comparing Microsoft Dynamics CRM To Salesforce
MicroSoft Dynamics CRM vs SalesForce (MS partner)
– Evaluating Software Vendors (from a MS shop)
– Microsoft Dynamics CRM: Much More Than Meets the Eye – Part 2 and Part 3
– Salesforce vs. Microsoft CRM: Relationship Management Applications
– Why We Chose to Work with Salesforce.com

How to disable/enable all validation rules for data loading

While working on a recruiting application, I found a solution for being able to load data into a SalesForce application without being blocked by validation rules.

Validation rules are usually intended to be applied only when a user creates a record and not when data is being imported from an external database. In this recruiting application, candidate records go several stages in a sequence (1-lead, 2-phone, 3-applicant, 4-interview, 5-contract negotiation, etc.) and this validation rule prevented the import process from loading candidate records in a stage higher than lead.

So the solution was to create a Custom Setting of type Hierarchy with a flag/checkbox in it that disables validation rules for a given user or profile. That is, all the validation rules will include this flag and only apply when the value of this flag is enabled.

To implement it:

1) click Setup, then on the left side, click App Setup/Develop/Custom Settings.

2) click New and create your settings as hierarchy/public

3) now create a custom field of type Checkbox:  click New, select Checkbox, click Next, type the name of the field as “Disable Validation Rules”, default to Unchecked, click Next, then click Save.

4) in the Custom Setting Definition/Your App Settings screen, click Manage to start configuring the setting you just created.

5) click the New button above “Default Organization Level Value”, leave the checkbox “Disable Validation Rules” unchecked and then click Save.

6) click “Back to List”, click the New button at the bottom, select Profile or User then choose the user id or profile that will perform the data loading, then click “Disable Validation Rules” and click Save.

7) now edit the validation rule appending the expression (highlighted below)
&& NOT( $Setup.Your_App_Settings__c.Disable_Validation_Rules__c )

  8 ) click Save

9) now the validation rule will only apply if the setting checkbox
Disable Validation Rules is unchecked for your profile/user

10) you can now load data freely and then, later, re-enable all
validation rules for your profile/user by changing the custom

11) you can use this way of implementing Custom Settings on
triggers too, just use the syntax below:

 Your_App_Settings__c s = 
 Your_App_Settings__c.getInstance( UserInfo.GetUserID() ); //or Profile
 if( s.Disable_Validation_Rules__c ) return; // skip trigger...

Pertinent Articles:
– Accessing Custom Settings
Let’s make some magic…

Upserting into SalesForce using .net and SOAP API

I’ve been experimenting with the classes created by Darren Terrell in the article “SalesForce integration with .net web services SOAP API” and found that there is a limit of 200 rows for upserts, so I’ve changed the Upsert() method as below to divide and submit the upserts in batches of 200 items at a time.

public UpsertResult[] Upsert(string externalID, sObject[] items)
   // send in batches of 200 updates
   int iIndex = 0;
   List objResults = new List(items.Count());
   while (iIndex < items.Count())
      sObject[] obj200Items = items.SubArray(iIndex, 200);
      objResults.AddRange( salesforceService.upsert(externalID, obj200Items) );
      iIndex += 200;
   return objResults.ToArray();

The SubArray method is defined as an extension below:

public static class ArrayExtensions
 /// <summary>
 /// Extracts a subarray of a given number of items (count) from an array starting from a given position (startIndex)
 /// </summary>
 /// <typeparam name="T"></typeparam>
 /// <param name="arr">Array from which to extract a subarray</param>
 /// <param name="startIndex">Position from where to obtain the array items</param>
 /// <param name="count">Number of items</param>
 /// <returns></returns>
 public static T[] SubArray(this T[] arr, int startIndex, int count)
    var sub = new T[count];
    int iActualCount = count;
    if( arr.Count() - startIndex < count )
       iActualCount = arr.Count() - startIndex;
    Array.Copy(arr, startIndex, sub, 0, iActualCount);
    return sub;