Printing a Salesforce Record to a DYMO Printer

Sometimes you might need to print records in Salesforce onto label. You have a few options. You could print them in batches using a custom Visualforce page, or a merge tool such as CongaMerge or Drawloop. For my use case, though, I needed to be able to print one-off labels on demand. DYMO has a JavaScript library that can interact directly with DYMO printers installed on the computer. To use, it, you must first install the DYMO software onto your computer. After installing it, make sure to close your browser completely and then open it again – watch out for background processes – they caused me a lot of grief because I couldn’t get it to work until I found all the background Chrome processes running.

You can get the latest version of the DYMO JavaScript Library here: http://labelwriter.com/software/dls/sdk/js/DYMO.Label.Framework.latest.js. DYMO doesn’t recommend using that URL for production use. Instead, you should download it and upload it as a static resource in Salesforce.

I wanted to create a custom button on a Case that when clicked would print a label with the case number in a barcode format. Accessing static resources from custom JavaScript button is a little tricky, but I found this hack to get it to work: http://jevonearth.blogspot.com/2011/08/using-static-resources-with-salesforce.html.

The code for the JavaScript is fairly simple. It builds the label xml, then prints it to the first DYMO label printer that it finds. You can change what is printed by modifying the label xml. There are a lot of examples on the DYMO blog.

{!REQUIRESCRIPT('/resource/' & LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXT(NOW()),':',''),'-',''),' ',''),10) & '000/DymoLib')}
 
try
{
// open label
 
var labelXml = '<?xml version="1.0" encoding="utf-8"?>' +
'<DieCutLabel Version="8.0" Units="twips">' +
'<PaperOrientation>Landscape</PaperOrientation>' +
'<Id>Address</Id>' +
'<PaperName>30252 Address</PaperName>' +
'<DrawCommands>' +
'<RoundRectangle X="0" Y="0" Width="1581" Height="5040" Rx="270" Ry="270" />' +
'</DrawCommands>' +
'<ObjectInfo>' +
'<BarcodeObject>' +
'<Name>Barcode</Name>' +
'<ForeColor Alpha="255" Red="0" Green="0" Blue="0" />' +
'<BackColor Alpha="0" Red="255" Green="255" Blue="255" />' +
'<LinkedObjectName></LinkedObjectName>' +
'<Rotation>Rotation0</Rotation>' +
'<IsMirrored>False</IsMirrored>' +
'<IsVariable>True</IsVariable>' +
'<Text>' + '{!Case.CaseNumber}' + '</Text>' +
'<Type>Code39</Type>' +
'<Size>Medium</Size>' +
'<TextPosition>Bottom</TextPosition>' +
'<TextFont Family="Arial" Size="7.3125" Bold="False" Italic="False" Underline="False" Strikeout="False" />' +
'<CheckSumFont Family="Arial" Size="7.3125" Bold="False" Italic="False" Underline="False" Strikeout="False" />' +
'<TextEmbedding>None</TextEmbedding>' +
'<ECLevel>0</ECLevel>' +
'<HorizontalAlignment>Center</HorizontalAlignment>' +
'<QuietZonesPadding Left="0" Top="0" Right="0" Bottom="0" />' +
'</BarcodeObject>' +
'<Bounds X="331" Y="345.600006103516" Width="4386.5" Height="720" />' +
'</ObjectInfo>' +
'</DieCutLabel>';
var label = dymo.label.framework.openLabelXml(labelXml);
 
// select printer to print on
// for simplicity sake just use the first LabelWriter printer
var printers = dymo.label.framework.getPrinters();
if (printers.length == 0)
throw "No DYMO printers are installed. Install DYMO printers.";
 
var printerName = "";
for (var i = 0; i < printers.length; ++i)
{
var printer = printers[i];
if (printer.printerType == "LabelWriterPrinter")
{
printerName = printer.name;
break;
}
}
 
if (printerName == "")
throw "No LabelWriter printers found. Install LabelWriter printer";
 
// finally print the label
label.print(printerName);
}
catch(e)
{
alert(e.message || e);
}

Basic JavaScript Debugging with Firebug

A lot of my posts have been JavaScript heavy and I get a lot of questions from readers trying to adapt the code. The most frequent problems are with errors in the JavaScript syntax. Unlike Apex and Visualforce markup, you can save pages with embedded JavaScript with syntax errors and have no save errors. It is then maddening when you try to view your fresh new page and nothing happens. The JavaScript fails silently in the background and you have no idea what happened. This is when I turn to one of the developer tools such as the one built into Chrome or my personal favorite, Firebug.

To use Firebug, you first need Firefox installed. If you don’t have it, go get it now: http://www.mozilla.org/en-US/firefox/new/. I’ll wait. OK, next you need to install Firebug: http://getfirebug.com/. There are a million sites you can use to learn about Firebug, so I’ll just go over the basics. In the upper right of Firefox, you should a little bug. If it is gray, then Firebug isn’t active for the page; just click on the bug to give it some color and make it active. A panel will probably open at the bottom of your browser. This is the console and where you can see all the messages.

Now let’s debug some code! Here is a little Visualforce page that will cause you some problems:

[code]
<apex:page >
<script src="/soap/ajax/19.0/connection.js" type="text/javascript" />

<script type = "text/javascript">
function tryConsole() {
console.log("Link clicked");
sforce.connection.sessionId = ‘{!$Api.Session_ID}’;
var result = sforce.connection.query("Select id, name" +
"from Account limit 1");
var it = new sforce.QueryResultIterator(result);
while(itt.hasNext()) {
var record = it.next();
console.log(record);
}
}
</script>
<a href="javascript:tryConsole()">click here</a>
</apex:page>

[/code]

Make a new Visualforce page and copy the code above into it. When you click the “click here” link, the tryConsole JavaScript function will run. Give it a try so you can look at the Firebug console. It should look something like this:

So what happened? First it logged a message to the console using the code console.log(“Link clicked”). This is more preferable to doing an alert with a message in it because it doesn’t interrupt the flow of the page. Then we see the big whammy. There was something wrong with my SOQL query. There was a malformed query because I’m concatenating two strings together and forgot to leave a space between the two strings. I can see the error message right in the console and make some changes to the code to fix it. To fix this problem, just add a space between name and the closing double quote. Make the change, save the page and try clicking on the link again.

Oops, we still have an error:

This time, it looks like a typo in the code. The variable itt isn’t defined. Notice that there is a link in the console that takes you directly to the offending line of JavaScript. We can see that the variable is actually defined as it, so let’s change the code to say “while(it.hasNext())” and save the page again. Third time’s the charm – let’s try clicking the link one more time and taking a look at the console:

Ah, much better. Now we can see that the console is showing us the record that was selected by the SOQL. This is because of the line of code “console.log(record)”. It allows us to inspect the values and see if they are what we expect and could help us with other trouble shooting.

This is by no means an exhaustive tutorial on debugging JavaScript, but I hope it gets you on your way to finding errors and squashing bugs!

Deleting Salesforce Contacts You Don’t Own

If you have a private sharing model for Accounts and Contacts in Salesforce, you might have run into this problem. You can only delete records you don’t own in Salesforce if you have been given Modify all Data permissions for the object or if you are in a Role above the owner. Our business requirements are a bit different from this. We wanted accounts to be private and shared by sharing rules to groups of other users. All of those users expect to be able to make changes to all contacts related to the account, including deleting contacts.

Let me digress here a minute: in my opinion, you shouldn’t delete contacts if they have any activities associated with them – they should be mark “Inactive” so we don’t lose all that history. We’ve added a before delete trigger to contacts to prevent them from being deleted in that case. In some cases, you might really need to delete a contact: it is a duplicate or there is no activity and it is just cluttering things up.

OK, back to the task at hand. Try as I might, I couldn’t figure out any way to accomplish deleting of contacts that were associated with accounts I could view and edit. I finally decided to roll my own solution with a custom button and some apex. It turned out to be pretty simple, but there is some room for improvement.

First the Apex code (complete with test!). In this code, I create a webservice that can be called by a custom button. It passes in the Contact Id and then deletes it. In the test, I create a contact and then try deleting the contact as a different user. Note that you might need to change the profile to get a valid user.

global without sharing class ContactUtil {
  webService static Boolean deleteContact(Id id) {
    Contact c = new Contact(id = id);
    try {
      delete c;
      return true;
    } catch (Exception e) {
      return false;
  }
}
 
  static testMethod void testDeleteContact() {
    Contact c = new Contact(LastName = 'Test');
    insert c;
    User u = [select id from user where Profile.Name = 'Sales User' and IsActive = true  limit 1];
    System.runAs(u) {
      deleteContact(c.Id);
    }
 
    Contact[] testContact = [select Id from Contact where Id = :c.Id];
    System.assert(testContact.isEmpty());
  }
}

Now that we have the webservice, we can create a button to add to the contact layout page. It will be a Detail Page Button that Executes JavaScript. Here’s what the javascript looks like. It prompts the user and if successful, redirects to the Contact tab.

{!REQUIRESCRIPT(&quot;/soap/ajax/22.0/connection.js&quot;)}
{!REQUIRESCRIPT(&quot;/soap/ajax/22.0/apex.js&quot;)}
 
if (confirm(&quot;Are you sure?&quot;)) {
  if (sforce.apex.execute(&quot;ContactUtil&quot;,&quot;deleteContact&quot;, {id:&quot;{!Contact.Id}&quot;})) {
    window.location.replace('/003/o');
  }
}

Add this button to your page layout and remove the standard delete button. Now your users will be able to delete any contacts that they can view on the detail page.

A couple of words of caution. You might have noticed that there is no validation that the user should be able to delete the contact. Ideally, I’d like to test to see if the user has write access to that contact record. If so, then allow the delete. I’ve been unable to find a way to test record level access, so I’ve left it wide open. [UPDATE: I found a way! http://verticalcode.wordpress.com/2012/04/03/deleting-salesforce-contacts-you-dont-own-part-2/] Because of this, make sure that you only give permissions to the the ContactUtil class who you want to be able to delete all contacts.

Using jQuery to Validate User Input

This week on Twitter, @lesteb posted a question about the best way to validate if a user had checked one of two checkboxes on a Visualforce page. We went back and forth for a while about his requirements. Since Salesforce doesn’t have radio buttons and it is impossible to make a checkbox field required, we needed some other way to make sure his requirements were met. I had a couple of ideas.

My first thought was to use Validation Rules – this is a great button click feature of Salesforce and, as Nick Hamm so eloquently pointed out recently on his blog, we should try to use configuration first and code second. In this case Bryan couldn’t use validation rules because he only wanted it to validate on one page and wanted to have more control of the error message. Our next option to look at was putting in some logic in the controller extension save method. Again, Bryan ruled this out because he wanted more control over the error message.

So, I then suggested using Javascript to do some validation with help from jQuery. In Visualforce, we can use onClick to run some JavaScript before the Apex method assigned to the button is executed. In the example below, the beforeSave() function is called first and only if that function returns true will it go to the Apex method mySave.

[code]<apex:commandbutton value="Save" id="saveBtn" action="{!mysave}" onClick="if(!beforeSave()) return false;"/>[/code]

The JavaScript function beforeSave is below. It uses jQuery to check the values of the the two checkboxes. At least one must be checked for the function to return true. If neither one is checked, it opens a jQuery UI dialog box telling the user to check a box. I’ll get into that in a minute.

[code]
function beforeSave() {
if (!j$(‘[id$=checkbox1]’).attr(‘checked’) && !j$(‘[id$=checkbox2]’).attr(‘checked’)) {
j$( "#dialog-message" ).dialog(‘open’);
return false;
} else
return true;
}
[/code]

Now for the dialog box. First we need a div on the page that will be used for the message:

[code]
<div id="dialog-message" title="Info Needed">
<p>
<span class="ui-icon ui-icon-alert" style="float:left; margin:0 7px 50px 0;"></span>
You should check a box!
</p>
</div>
[/code]

Once we have the div, we need to turn it into a jQuery UI Dialog. Here’s the way to do that:

[code]
j$(document).ready(function() {
j$("#dialog-message").dialog({
autoOpen: false,
modal: true,
buttons: {
Ok: function() {
j$( this ).dialog( "close" );
}
}
});
});
[/code]

Bryan had one more requirement. Only one of the two checkboxes could be checked. I turned to a little more JavaScript to make this work. Here’s the visualforce markup where I added an onClick call to a JavaScript function to the input field.

[code]
<apex:inputField value="{!account.Checkbox1__c}" id="checkbox1" onClick="deselectOther(this);"/>
[/code]

The JavaScript function to deselect one checkbox when the other gets checked is pretty simple. It gets the element clicked passed in, compares the id of that element and then use jQuery to find and uncheck the other one.

[code]
function deselectOther(e) {
if (e.id.match(/checkbox1$/))
j$(‘[id$=checkbox2]’).attr(‘checked’, false);
if (e.id.match(/checkbox2$/))
j$(‘[id$=checkbox1]’).attr(‘checked’, false);
}
[/code]

Bryan did a nice little demo video of his nearly finished page. Note that this video was done before the final trick of deselecting one checkbox when the other was selected. He was handling this case with an error message.

I’ve created an unmanaged package that you can install into your org to see the entire sample code. It doesn’t match Bryan’s video entirely as he did a bunch of other messages and validations, but I hope it points you in the right direction. As always, I’d love to hear thoughts on how I could improve this, or maybe you have a completely different way to do it!

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.

Experience with the iPad 2

It seemed like all the cool kids were getting one, so I jumped on the bandwagon a little over a month ago. So, far it has been a great addition to my work experience.

Wyse PocketCloud (free/$14.99) RDP/VNC/View client for accessing servers and computers. I can either manually connect to any server with VNC or RDP or I can install their companion software on a computer – I installed it on my Mac at home and my work Windows 7 laptop. As long as the computers are online, I can remote into them – even if they are behind firewalls. It has a really good onscreen pointer that allows you to scroll, right click, etc. I also needed a client that had all the Function keys – this one has them and many other keys for the onscreen keyboard. The paid version gives you a few extra features, but I think the free one is pretty good too.

Quickoffice ($14.99) I use the Microsoft Office suite at work, so I need to be able to view and edit Word, Excel and Powerpoint docs. Combine this with Dropbox (free) and you’ve got a really slick way to work on your docs wherever you are. You can also connect it to Google Docs and edit those in QuickOffice. Great for working offline, but I found formatting on my spreadsheet was a bit odd sometimes. My one gripe with QuickOffice is that it doesn’t support the Open In functionality, so if I wanted to open a document in another iPad app (GoodReader or FileBrowser, for example), I’ve got to use an intermediary such as Dropbox to get the document into the other application.

GoodReader ($4.99) This app can read many file formats, but I like it best for annotating PDFs.

GoToMeeting (free) The latest version of GoToMeeting for the iPad supports webinars! I love this feature as I can pull up a webinar or meeting on the iPad and still working on my laptop. You can’t host meetings on the iPad, however.

Join.Me (free) I’ve recently become a big fan of join.me because it gives you quick screen sharing for free. I still use GoToMeeting and GoToWebinar for trainings and such, but if I need a quick meeting, this is often easier, especially for the meeting guests. The iPad app works great as well.

Chatter (free) As a Salesforce user, my list wouldn’t be complete without including Chatter. The iPad client is really well executed. Now if only I could get push notifications, then it would be awesome.

AppShopper (free) I’ve found some great deals on apps using this app the sorts Apps in the App Store. Boy that sounds confusing. Just download it and see for yourself.

FileBrowser ($3.99) If you have a need to access files on a network share, then this app works great. I’ve even used it through our VPN to get files. Open them in QuickOffice, GoodReader, or whatever document app you’ve got, and you are good to go.

These are just a few apps that I’ve found useful for using my iPad at work. I hope you find it helpful. If I left one out, please comment as I’m always looking for new ways to use my iPad!

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>

Salesforce.com Tips and Tricks