Salesforce Plugin

The openBD Salesforce plugin lets you quickly and easily send queries to the Salesforce platform. You can also create, update, get descriptions of and delete objects, including custom ones through this API.

The following information and code snippets are derived from the updating of a custom web application to use this plugin over another Salesforce driver. In fact it was this application which spawned the development of the OpenBD Salesforce Plugin in the first place.

Overview of functions

Function Name	Description
SalesforceCreate	Inserts a new Object into Salesforce, returning the new ID that was created
SalesforceDelete	Deletes the object within Salesforce at the given Id
SalesforceDescribe	Retrieves the information about the object
SalesforceQuery	Executes a query against Salesforce, caching the result if necessary
SalesforceUpdate	Updates the Object type with the given Id to the values in the fields. If the fields already exist they are overwritten

To use any of the Salesforce plugin functions, you'll need the account Username, Password, and Salesforce Security Token. A handy tip would be to set these as variables in the application scope, as the security token expires after 90 days.

Please note: all the following CFML snippets will assumed to be used inside CFSCRIPT unless otherwise stated.

SalesforceCreate

SalesforceCreate( username, password, type, fields );

Creating records is really straightforward, just specify the Object (type) you wish to insert into, and the Fields you wish to populate in the form of a structure. It's worth re-iterating that case matters, so if the Object name is Account, write it as such, don't convert case or your insert might not work.

Here's some sample code for creating a record in the Salesforce 'Task' object, making the assumption you'll be passing variables into the field structure.

username = "yourSFusername";
password = "yourSFpasswordAndSFSecurityTokenCombined";
type = "Task";
fields = {
  RecordTypeId : RecordTypeIdValue,
  Subject : SubjectValue,
  Description : DescriptionValue,
  ActivityDate : ActivityDateValue,
  Status : StatusValue,
  Priority : PriorityValue,
  OwnerId : OwnerIdValue,
  Type : TypeValue,	
  WhatId : WhatIdValue,
  WhoId : WhoIdValue
 };
createRecord = SalesforceCreate(username, password, type, fields);
return createRecord;  // the function returns a string containing the id of the new record

SalesforceDelete

SalesforceDelete( username, password, id );

Quite self explanatory this one, all you need is the id of the record you wish to delete. Use with caution, once it's gone it's gone!

username = "yourSFusername";
password = "yourSFpasswordAndSFSecurityTokenCombined";
id = "objectIdValue";
deleteObject = SalesforceDelete(username, password, id);

SalesforceDescribe

SalesforceDescribe( username, password, type );

Finding out details about a particular object can be useful, particularly when it comes to creating and updating records. SalesforceDescribe() will get you the information on all the columns in the object quickly and easily, just pass in the Object name (type) and you're there.

username = "yourSFusername";
password = "yourSFpasswordAndSFSecurityTokenCombined";
type = "Task";
describeObject = SalesforceDescribe(username, password, type);

writeDump(describeObject); 

SalesforceQuery

SalesforceQuery( username, password, soql, cache );

Querying Salesforce is easy, especially if you use force.com explorer to build the queries first. This is a good thing to do if you're not too familiar with the column names you're dealing with.

Salesforce uses SOQL, Salesforce Object Query Language, which is similar to but perhaps not quite as comprehensive as SQL.

SOQL does not support all advanced features of the SQL SELECT command. For example, you cannot use SOQL to perform arbitrary join operations, use wildcards in field lists, or use calculation expressions.

Salesforce.com

It's worth noting the way queries are returned by the plugin differs from cfquery in that what is returned depends on the content in the object.

For example, if a particular column in your query contains no data, the column itself won't be returned, so any checking you do at the presentation end has to be modified accordingly to account for this, i.e. use StructKeyExists(qry, "data") to check the data is there rather than len(qry.data) != 0 to avoid errors.

Query cache is set by adding the time you want the cache to last in seconds, the default is 0. As with the username and password, you may want to define a standard cache value in the application scope.

The SalesforceQuery() function does not support the use of cfqueryparam.

Here's an example of a simple Salesforce query of the 'Contact' object.

username = "yourSFusername";
password = "yourSFpasswordAndSFSecurityTokenCombined";
cache = "3600";

soql = "SELECT AccountId, Company_Name__c, Id, Email, FirstName, Formal_Title__c, Full_Name__c, Group_Primary__c, LastName, City__c, RecordTypeId FROM Contact";

query = SalesforceQuery( username, password, soql, cache );

// in a cfc or function context 
return query; 

// or if you just want to see it
writeDump(query); 

This next example shows a SOQL query of the 'Account' object with a simple join to the 'Contact' object. This will most likely return duplicate records, and is merely here to illustrate the syntax. Queries like this are referred to as Relationship Queries.

soql = "SELECT Id, Name, Industry, (SELECT AccountId, Id, Bio__c, Email, Formal_Title__c, Full_Name__c FROM Contact__r WHERE AccountId = Account.Id) FROM Account";

Alternatively you can make use of more sophisticated SQL syntax by joining your SalesforceQuery results using Query of Queries, this is useful to cut down the number of calls to Salesforce, and if the data you're querying is appropriate for cacheing.

newQuery = queryOfQueryRun( sql="SELECT * FROM query, anotherQuery WHERE query.AccountId = anotherQuery.Id" );

Find more information on SOQL in the Salesforce developer manual

SalesforceUpdate

SalesforceUpdate( username, password, type, id, fields );

Updating records is easy, too. All you need in addition to the Object name (type) and the struct of Fields is the ID of the object you wish to update.

Some fields are updatable, some are not. If your update fails, it's worth checking this using SalesforceDescribe(). You can also check the properties of an object and its fields easily in force.com explorer.

Tip: if you need to overwrite data with a blank (null) value, pass in a space i.e. fieldVal : " "; because fieldVal : ""; won't overwrite what's already in the cell.

username = "yourSFusername";
password = "yourSFpasswordAndSFSecurityTokenCombined";
type = "Task";
id = objectIdValue;
fields = {
  RecordTypeId : RecordTypeIdValue,
  Subject : SubjectValue,
  Description : DescriptionValue,
  ActivityDate : ActivityDateValue,
  Status : StatusValue,
  Priority : PriorityValue,
  OwnerId : OwnerIdValue,
  Type : TypeValue,	
  WhatId : WhatIdValue,
  WhoId : WhoIdValue
 };
updateRecord = SalesforceCreate(username, password, type, id, fields);

Resources

An invaluable tool to get to grips with SOQL and the data you are dealing with is force.com explorer.

"This handy tool lets you browse your database schema, custom objects and fields, and build and test SOQL queries.

developerforce.com

There is a full developer manual for all things Salesforce.

We also have detailed information on Web Services using Salesforce with OpenBD in the Advanced section of the manual.

As with all OpenBD plugins, download the openbdplugin-salesforce.jar and drop it into your applications' /WEB-INF/lib folder and you're good to go.

All comments and suggestions for improvement of this page are warmly invited and can be posted in our OpenBD Google Discussion Group