Category Archives: Salesforce

Displaying Salesforce Data with Google Charts and AJAX Toolkit

In this second post about using Google Charts to visualize Salesforce data, I’ll show how to use the AJAX Toolkit to query data in related objects and display a summary in a chart. In the first example, I created a column chart with data from custom fields on the Account. In this next example, let’s assume we have an object called AccountHistory which has the sales by Product and Account. On the Account page layout, we want to be able to see a summary of sales by Product Family.

In order to show this, we need to run a SOQL query summarizing sales by Product Family. I’ll take advantage of the AJAX Toolkit to query the data without having to use a custom controller. Using the AJAX toolkit is pretty simple to query data. First we query data using SOQL and then iterate over the results, adding each record returned to the chart.

<apex:page standardController="Account">
  <head>
  <script type='text/javascript' src='https://www.google.com/jsapi' />
  <script src="/soap/ajax/19.0/connection.js" type="text/javascript" />
 
  <script type="text/javascript">
 
  // use this function to format the label
  formatCurrencyLabel = function( value )
    {
      return "$" + String(value);
    }
 
    google.load("visualization", "1", {packages:["corechart"]});
    google.setOnLoadCallback(drawChart);
 
    function drawChart() {
        // Create a new data table with two columns: the label and the value
        var data = new google.visualization.DataTable();
        data.addColumn('string', 'Product Family');
        data.addColumn('number', 'Sales');
 
        // We need the sessionId to be able to query data
        sforce.connection.sessionId = '{!$Api.Session_ID}';
        // Query data using SOQL.
        var result = sforce.connection.query("Select ah.Product__r.Family productFamily, sum(ah.Sales__c) sales " +
                      "from AccountHistory__c ah where ah.Account__c = '{!account.Id}' group by ah.Product__r.Family " +
                      "order by sum(ah.Sales__c) desc");
        // Iterate over the result
        var it = new sforce.QueryResultIterator(result);
        while(it.hasNext()) {
            var record = it.next();
            // Add the data to the table
            data.addRow([record.productFamily, {v:parseFloat(record.sales), f: formatCurrencyLabel(record.sales)}]);
        }
        var chart = new google.visualization.PieChart(document.getElementById('chart_div'));
        chart.draw(data, {width: 800, height: 300, pieSliceText:'label'});
    };
 
  </script>
  </head>
  <div id="chart_div" />
</apex:page>

Once we have the Visualforce page created, it can be added to an Account page layout. Remember to make the height the same as defined on the page (in this case 300 px). The result should look something like this:

I’ve updated my unmanaged package to include this example. It includes the custom object and Visualforce page as well as the example from my previous post. You can install it into your org by clicking on the following link: https://login.salesforce.com/packaging/installPackage.apexp?p0=04t80000000xviT. Make sure you have some products with Product Families assigned and then populate some Account History records. Add the new Visualforce page to an existing page layout and you should be up and running.

Obviously, this is a very simple example, but I hope it illustrates how this can be a great addition to any page layout.

Updating Salesforce Data with SQLForce

Recently, Jeff Douglas posted about an ANSI SQL command line tool on his blog. The tool, called SQLForce, fills in some holes in SOQL. Specifically, it allows you to run commands such as UPDATE, DELETE, INSERT. You can also output data to txt files.

Jeff posted a great video of how to use it on his blog, but I prefer written steps, so here they are. CAUTION: you can really screw up your data with this tool, so make sure the command you are running is what you want.

  1. Download the most recent version in a zip file (sqlforce_1.20.5.zip) from http://code.google.com/p/sqlforce/downloads/list.
  2. Extract the zip file to an easy to access folder.
  3. Open a command prompt in the extracted sqlforce folder and then run the command java -jar sqlforce.jar
  4. Type in “HELP” to see all available commands.
  5. Connect to your Salesforce Org (I’d recommend trying it out in your sandbox first): CONNECT [PRODUCTION:SANDBOX] username password [activationKey]
  6. Wait for the connection to complete – it takes a while.
  7. Run SQL commands on your Salesforce data!

There are some good code snippets to see some of the ways to use the tool at http://code.google.com/p/sqlforce/wiki/SQLForce. One of my uses is for quick updates or deletes of certain data. For example, I had a problem with a picklist value and needed to update a bunch of records that had a certain picklist value to change it.

Displaying Salesforce Data with Google Chart Tools

Sometimes the standard text only detail pages in Salesforce get a bit dull and you need to spice things up with a chart. One trick I use is to add custom VisualForce pages to my page layouts to display inline charts. I’ll be using the Google Chart Tools library to make this happen. This is an all Javascript approach and none of your data goes to another server to render the chart – it also avoids the use of Flash so you can view these charts on an iPad or other iOS device.

To get started, I created some custom fields on the account to show revenue from previous years. I’ll call these fields Revenue 2008, 2009, 2010, etc. They will be the revenue for the account for each year. What I want is a column chart showing the revenue for each year so I can visualize whether sales for this account are trending up or down. To do this, I need to create a new VisualForce page. The page uses the Account standard controller and just accesses the fields it needs to display on the chart, puts the data in a Google data table and then draws the chart.

[sourcecode]
<apex:page standardController="Account">

<head>

<script type=’text/javascript’ src=’https://www.google.com/jsapi’ />

<script type="text/javascript">

//use this function to format the hover text
formatCurrencyLabel = function ( value )
{
return "$" + String(value);
}

google.load("visualization", "1", {packages:["corechart"]});
google.setOnLoadCallback(drawChart);

function drawChart() {
// we need a table to act as the datasource for the chart
var data = new google.visualization.DataTable();

// the table needs two columns
data.addColumn(‘string’, ‘Period’);
data.addColumn(‘number’, ‘Annual Revenue’);

// add the rows of data which consist of a value and a formatted label
data.addRow([‘2008’, {v:{!Account.Revenue_2008__c}, f: formatCurrencyLabel({!Account.Revenue_2008__c})}]);
data.addRow([‘2009’, {v:{!Account.Revenue_2009__c}, f: formatCurrencyLabel({!Account.Revenue_2009__c})}]);
data.addRow([‘2010’, {v:{!Account.Revenue_2010__c}, f: formatCurrencyLabel({!Account.Revenue_2010__c})}]);
data.addRow([‘2011’, {v:{!Account.Revenue_2011__c}, f: formatCurrencyLabel({!Account.Revenue_2011__c})}]);

//assign the chart to the appropriate div and draw the chart
var chart = new google.visualization.ColumnChart(document.getElementById(‘chart_div’));
chart.draw(data, {width: 300, height: 200, title:’Annual Revenue Trend’, legend:’none’,
vAxis: {minValue:0}, hAxis: {slantedTextAngle:45}});
};

</script>

</head>

<body>
<div id="chart_div" />
</body>
</apex:page>

[/sourcecode]

With the page created, I can now edit the Account page layout and add the new page to it. Remember to set the width of it to 100% and the height to be the same as what you set in the javascript (in this case 200px). You should get something that looks kind of like this. Note the hover text.

Google Chart Example

I’ve created an unmanaged package with the custom fields and the VisualForce page. You can install it here: https://login.salesforce.com/packaging/installPackage.apexp?p0=04t80000000xviP. Once you install it, add the page to a page layout, populate the custom fields and you should be good to go.

My next post will show how to use this technique with the AJAX toolkit to query data and display it in a chart – really useful for summarizing related objects or creating your own dashboards.

Using apex-lang to build Soql statements

In my last post, I showed how to use the new Javascript Remoting functionality that was just released in Spring ’11. After reading a bit about the apex-lang project’s Soql Builder classes, I thought that I would try to rewrite my controller. While I wasn’t able to reduce the number of lines of code as I hoped I would, I think it made it more readable. The Soql Builder let me not worry about escaping single quotes to avoid Soql injection.

One area where I felt that I had to do more work than was necessary was working around filtering by additional fields. In my design, I wanted to be able to always filter by Name, and then by any optional additional fields passed into the controller. This created quite a bit more code than I was hoping to use, but it works!

I also took the time to do a little bit more error handling in the controller. I realized that any exceptions thrown could be seen in the calling Javascript. The controller now checks that all the additional fields passed in are actually fields in the object. If they aren’t it throws an error.

Without further ado, here’s my new controller:

[sourcecode]

global class autoCompleteController {

public class applicationException extends Exception{}

@RemoteAction
global static SObject[] findSObjects(string obj, string qry, string addFields) {

// check to see if the object passed is valid
Map<String, Schema.SObjectType> gd = Schema.getGlobalDescribe();
Schema.SObjectType sot = gd.get(obj);
if (sot == null) {
throw new applicationException(‘Object name ‘ + obj + ‘ not valid’);
return null;
}

al.SoqlBuilder soqlB = new al.SoqlBuilder()
.selectx(‘ID’)
.selectx(‘Name’)
.fromx(obj)
.orderbyx(new al.OrderBy(‘name’))
.limitx(20);

//add the where statement. If no additional fields were sent, it is easy
if (addFields == ”) {
soqlB.wherex(new al.FieldCondition(‘Name’).likex(qry));
}
else
{
// Split the list of additional fields passed
List<String> fieldList = addFields.split(‘,’);

// get a map of all fields in the object
Map<String, Schema.SObjectField> gf = sot.getDescribe().fields.getMap();

// Create the condition for the soql
al.OrCondition orC = new al.OrCondition();

// always search the Name field
orC.add(new al.FieldCondition(‘Name’).likex(qry));

// loop through the list of fields to validate and add them to the condition
for (String s : fieldList) {
Schema.SObjectField sof = gf.get(s);
if (sof == null) {
throw new applicationException(‘Additional Field ‘ + s + ‘ not valid for object ‘ + obj);
return null;
}
orC.add(new al.FieldCondition(s).likex(qry));
}
// add the list of additional fields to the soql
soqlB.selectx(fieldList);
// add the where clause
soqlB.wherex(orC);
}

String soql = soqlB.toSoql(new al.SoqlOptions().wildcardStringsInLikeOperators());

List<sObject> L = new List<sObject>();
try {
L = Database.query(soql);
}
catch (QueryException e) {
throw e;
return null;
}
return L;
}
}

[/sourcecode]

So, there you have it. If you need to build dynamic Soql in apex, then take a look at apex-lang. It definitely helps avoid common mistakes when building Soql strings and makes the code more readable.

Salesforce Javascript Remoting, jQuery and Autocomplete

I was very excited when I found out that Salesforce was releasing Javascript remoting with Spring 11. There have been several cases where having access to Apex classes and logic in Javascript would make my development much easier. One area that I wanted to try out was in an autocomplete component that I built a while ago. I had hacked together a component that used a jQuery autocomplete plugin. While it worked, I had a couple of problems: it didn’t work in IE8 (everybody uses Chrome or Firefox, right?) and the autocomplete consumed a separate Visualforce page, so when I had developer mode turned on, it would choke on the wrapper that developer mode uses.

Using Josh Birk’s blog post and the Salesforce documentation as a reference, I got to work.

First I created the controller. I made this controller as flexible as possible. You can pass it the API object name (Account, Contact, etc), the search string and any additional fields you want to include in the search. It will always search the Name field for the search string.

[Edit: I have created a new controller that uses apex-lang to build the Soql. You can find it in my blog post: http://verticalcode.wordpress.com/2011/02/21/using-apex-lang-to-build-soql-statements/.]

global class autoCompleteController {
    @RemoteAction
    global static SObject[] findSObjects(string obj, string qry, string addFields) {
        // more than one field can be passed in the addFields parameter
        // split it into an array for later use
        List<String> fieldList;
        if (addFields != null) fieldList = addFields.split(',');
       // check to see if the object passed is valid
        Map<String, Schema.SObjectType> gd = Schema.getGlobalDescribe();
        Schema.SObjectType sot = gd.get(obj);
        if (sot == null) {
            // Object name not valid
            return null;
        }
        // create the filter text
        String filter = ' like '%' + String.escapeSingleQuotes(qry) + '%'';
        //begin building the dynamic soql query
        String soql = 'select id, Name';
        // if an additional field was passed in add it to the soql
        if (fieldList != null) {
            for (String s : fieldList) {
                soql += ', ' + s;
            }
        }
        // add the object and filter by name to the soql
        soql += ' from ' + obj + ' where name' + filter;
        // add the filter by additional fields to the soql
        if (fieldList != null) {
            for (String s : fieldList) {
                soql += ' or ' + s + filter;
            }
        }
        soql += ' order by Name limit 20';
        List<sObject> L = new List<sObject>();
        try {
            L = Database.query(soql);
        }
        catch (QueryException e) {
            return null;
        }
        return L;
   }
}

Next I created the component. Note that the component requires some jQuery resources which are being loaded from Google’s CDN for the jquery files.

I also chose to use a spinner type indicator to queue the user that the autocomplete is working, which I uploaded as a resource. You can generate your own gif spinner at http://ajaxload.info/.

<apex:component controller='autoCompleteController'>
  <!-- JQuery Files -->
  <script src='https://ajax.googleapis.com/ajax/libs/jquery/1.6.2/jquery.min.js'/>
<script src='https://ajax.googleapis.com/ajax/libs/jqueryui/1.8.12/jquery-ui.min.js'/>
<apex:stylesheet value='http://ajax.googleapis.com/ajax/libs/jqueryui/1.8.12/themes/ui-smoothness/jquery-ui.css'/>
  <!-- Attributes Required For Component -->
  <apex:attribute name='objectname' description='The object name you want to look for.'     type='String' required='true'/>
  <apex:attribute name='additionalfield' description='Any additional fields you'd like to search and include in the display.'     type='String' required='false'/>
  <apex:attribute name='autocomplete_textbox' description='The ID for the Autocomplete List Textbox.'     type='String' required='true'/>
  <style>
    .ui-autocomplete-loading { background: white url({!$Resource.circleIndicator}) right center no-repeat; }
  </style>
  <script type='text/javascript'>
    var j$ = jQuery.noConflict();
    j$(document).ready(function() {
 
        var sObjects;
        var queryTerm;
 
        j$(esc('{!autocomplete_textbox}')).autocomplete({
            minLength: 2,
            source: function(request, response) {
                        queryTerm = request.term;
                        autoCompleteController.findSObjects('{!objectname}', request.term, '{!additionalfield}', function(result, event){
                            if(event.type == 'exception') {
                                  alert(event.message);
                            } else {
                                 sObjects = result;
                                 response(sObjects);
                            }
                        });
                   },
            focus: function( event, ui ) {
                    j$(esc('{!autocomplete_textbox}')).val( ui.item.Name );
                    return false;
                    },
            select: function( event, ui ) {
                        j$(esc('{!autocomplete_textbox}')).val( ui.item.Name );
                        j$(esc('{!autocomplete_textbox}_lkid')).val( ui.item.Id );
                        j$(esc('{!autocomplete_textbox}_lkold')).val( ui.item.Name );
                        return false;
                    },
         })
         .data( 'autocomplete' )._renderItem = function( ul, item ) {
            var entry = '<a>' + item.Name;
            j$.each('{!additionalfield}'.split(',') , function(key, value) {
                entry = entry + ' ' + item[value];
            });
            entry = entry + '</a>';
            entry = entry.replace(queryTerm, '<b>' + queryTerm + '</b>');
            return j$( '<li></li>' )
                .data( 'item.autocomplete', item )
                .append( entry )
                .appendTo( ul );
        };
    });
 
    function esc(myid) {
           return '#' + myid.replace(/(:|.)/g,'\\$1');
    }
 
  </script>
</apex:component>

Finally, I can use the component in any Visualforce page to autocomplete on any field. In the below example, I’m looking for account name or ticker symbol:

    <apex:page>
    <apex:form><br />
        <apex:inputText id='account'>
            <c:AutoComplete2 objectname='Account' additionalfield='TickerSymbol' autocomplete_textbox='{!$Component.account}' />
        <apex:inputText>
    </apex:form>
</apex:page>