Initialize New Fields Fast

Since it is raining today riding my motorcycle is not a good idea. Instead, I solved a simple problem for our Tokyo office.

We already keep both an English and Kanji version of contact first and last names. My Japanese friends wanted new fields for Kanji Title and Department.  They also wanted the new fields initialized based on the current Title and Department fields (but just for accounts in Japan). The new fields are called LocalTitle__c and LocalDepartment__c.

How would you solve this data initialization problem? DataLoader? My Python/SQLForce solution took me 6 minutes and that included testing the script on a sandbox.

import SQLForce 

session = SQLForce.Session("production")  

contactSQL = "SELECT id, title, department FROM Contact WHERE  account.marketCountry__c='Japan' AND ((title<>null and localTitle__c=null) or (department<>null and localDepartment__c=null)) 

updateRecs = [] 

for rec in session.selectRecords(contactSQL):

updateRecs.append( [ rec.id, rec.title, rec.department ]

 if 0 != len(updateRecs):

session.update("Contact", ["localTitle__c", "localDepartment__c" ], updateRecs)

In my case the script updated around 3000 records and took 15 seconds to run. Naturally, the SQLForce library took care of buffering updates into the required packet sizes.

SQLForce Gave Me Time to Ride My Motorcycle.

This morning I was asked to change the "Type" of around 400 Salesforce Campaigns from one type to another.  The type was simply wrong.

No sweat. The following short Python did the job with just a few minutes work on my part, saving my next few hours for a bit of motorcycle riding.
========================================

import SQLForce

if __name__ == '__main__':
    session = SQLForce.Session("production")
   
    updateRecs = []
    campaignSOQL = "SELECT id, owner.alias FROM Campaign WHERE owner.alias='mnakano' AND type='Event-Tradeshow/Conference'"
    for rec in session.selectRecords( campaignSOQL ):
        updateRecs.append( [rec.id, 'Training'])
   
    if len(updateRecs) > 0:
        print("Updating " + str(len(updateRecs)) + " campaign records")
        session.update("Campaign", ["Type"], updateRecs)
    else:
        print("No records to update")

==========================================
I will never understand why so many people put up with DataLoader to do mass updates. Python with SQLForce is free and is so much faster.

You can get the SQLForce Python module for free at www.capstorm.com

How to Delete a Lot of Price Book Entries

This week a marketing person cloned a number of products to create new product records. A nifty side effect of the clone was a clone of the prices. This created price book entries for in lots of price books and in currencies in which the new product is not even sold. In other words, a lot of garbage data.

I fixed the problem with a tiny Python program using the SQLForce module (free from Capstorm).
Want to see it?

============================================
import SQLForce

session = SQLForce.Session("production")

soql = "DELETE FROM PricebookEntry WHERE productCode LIKE 'MUSI0%' AND product2.name = 'NameHidden' AND pricebook2.name<>'Standard Price Book'

session.runCommands(soql)

=============================================

Naturally I used a SOQL statement first to make sure my WHERE clause was selecting the proper records. Since the prices had not been used in an Opporunity this was all it took to fix my data.

SQLForce for Python -- I love it!

Populate a Sandbox in 5 minutes and learn some graph theory.

Every time I populate a Developer Sandbox with a snapshot of production data I smile. Today, in tracking down an interesting bug, I populated a sandbox with all won Opportunities since 1-Dec-2013 plus their associated

• Accounts
• Contacts,
• Opportunity Line Items
• Quotes & Quote Line Items
• Quote PDF Documents 
• plus a handful of other tables. 

How long did this take: 4 minutes, 27 seconds.

I restored: + all the records + all the links between the records + the attachments & notes
The tool I used was CopyStorm/Restore -- this is not a product promo but a discussion of the graph theory problem faced in restoring Salesforce data.

Anyone who has use data loaders (Salesforce or Jitterbit) knows that the routine for importing Account and Contact records is:

• Import Accounts
• Take the generated Ids from importing the Accounts and patch the Contact import file. 
• Import the Contacts 

Of course, if there are Account->Account relationships or Contact->ReportsTo relationships then the problem is quite a bit more difficult.

The problem is inherently difficult because Salesforce supports almost arbitrary relationships between records.

Finding and restoring all of the relationships is fundamentally a well known graph theory problem (minimal spanning subtree) and has the unfortunate property of being an NP-complete problem.

Roughly, NP-complete means that the complexity of the problem grows at a rate faster than any polynomial as more records and relationships are added.

An exhaustive search is rarely possible for this class of problem if the # of records and relationships is very large -- an algorithm has to "know" something about the problem domain and efficiently "prune" the search space.

Oddly enough the "Salesforce Restore" problem is similar to a problem in computational chemistry. The problem: Given part of a molecular structure, find all molecules that match the pattern. Ring system make this a difficult problem in chemistry -- knowledge about possible ring systems make it do-able.

SOQL's Seven Deadly Sins

This article is not about how to be a better SOQL programmer. It is about pitfalls in SOQL that are obscure and often difficult to diagnose and understand when encountered.

1. The SOQL WHERE clause cannot be trusted.
   
2. The SOQL LIMIT parameter is not always optional.
   
3. Timestamps returned by SOQL believe in time travel.
   
4. SOQL can return field data that is invalid based on the field definition.
   
5. SOQL has a love/hate relationship with tables containing lots of custom fields.
   
6. SOQL does not treat all table equally – not knowing the exceptions can take down your org.
   
7. SOQL chokes on tables with lots of functional columns.
   

Sin 1: The SOQL WHERE clause cannot be trusted.

Here is a trick question. Will the following SOQL statement return any records that were modified today?

SELECT id, name FROM Account WHERE systemModStamp < LAST_YEAR

The answer is......usually not. However it will return a record modified today when:

• The record fit the WHERE criteria when the record set to return was determined.
  
• The record is modified before it was fetched from Salesforce.
  

This “sin” is easy to reproduce with a Python script and a web browser.

1. In the web browser open the Salesforce GUI on the record that will be the most recent one to match the LAST_YEAR criteria.
   
2. Start a Python script that fetches records using:
   

SELECT id,name,systemModStamp
FROM Account
WHERE systemModStamp < LAST_YEAR
ORDER BY systemModStamp ASC

3. Modify the record in the Salesforce GUI after the Python script starts fetching records.
   
4. One of the last records you read from Salesforce will have a modification date of today
   

Sin 2: The SOQL LIMIT parameter is not always optional.

Though the SOQL documentation says that the LIMIT parameter is optional there are a handful of Salesforce tables where it is nearly impossible to run a large query without the dreaded Query Timeout error. If you are not a believer then try running these queries on a mature Salesforce instance.

1. SELECT id, accountId, description FROM Task ORDER BY SystemModStamp ASC
   
2. SELECT application,browser,loginTime FROM LoginHistory ORDER BY loginTime ASC
   

Short of begging Salesforce for a max timeout value longer than the default, a solution has to “know” good limit values for problem tables. Here are good values for a handful of the more troublesome table.

Table Name (or name pattern)	Good LIMIT Value
.*History	5000
.*History__c	5000
.*Feed	5000
.*Share	5000
FeedComment	5000
Task	5000
EmailMessage	5000
CaseComment	50
FieldPermissions	2000
LoginHistory	2000

Sin 3: Timestamps returned by SOQL believe in time travel.

Here's another SOQL quiz. In the following code snippet, is it possible for the Exception to be thrown? For the quiz assume that all Account records have unique timestamp values and no records are deleted.

Account accts1[] = [SELECT id, systemmodstamp FROM Account ORDER BY systemmodstamp ASC;

Account maxAcct = accts1[acct1.size() - 1]

Account accts2[] = [SELECT id, systemmodstamp FROM Account WHERE systemmodstamp <=:maxAcct.systemmodstamp ORDER BY systemmodstamp ASC;

if( accts2.size() > accts2.size()) {

throw new Exception('Timewarp – new account created in the past!');

}

The answer....usually no. However, Salesforce does not guarantee that records are committed in timestamp order.

It is possible for a record with a SystemModStamp of 2014-12-31T00:12:12.000Z to be committed to Salesforce after a record in the same table with a SystemModStamp of 2014-12-31T00:13:12.000Z.

This is a hard “sin” to reproduce because it depends on the current load of the Salesforce servers. We discovered the problem by monitoring a high volume production instance for three weeks – the problem occurred just a couple of times during this period. It was sort of like searching for a Higgs particle.

The moral: Do not assume that the MAX(SystemModStamp) or MAX(LastModifiedDate) value is a good constraint to find all values that are older.

The solution: Do not rely on timestamps that are fresh. Give Salesforce a little while to commit all recent transactions. In practice this odd state seems to settle out on Salesforce servers after 10-120 seconds.

Sin 4: SOQL can return invalid field data.

This quiz should be easy for experienced SOQL users.

1. Is it possible for an invalid email address to be stored and returned in a Salesforce Email field?
   
2. Is is possible to store a 132 character string in a 128 character Salesforce field?
   

The answer to #1 is, of course, possible. Check out contact Ms. Barbara Levy in any developer force instance. Her email is: b.levy@expressl&t.net Try saving the record and Salesforce will tell you that the email format is invalid (using the API will throw the same error).

The answer to #2 is also “possible.” In fact Salesforce creates an email template with an invalid length subject when Sites is enabled.

There is other “invalid” data (related to currency values) lurking in the developer force instance – you will have to find this one yourself.

The morals:

1. Just because Salesforce meta data says a field is 80 characters long does not mean it may have a longer value.
   
2. Just because Salesforce meta data says a number is DECIMAL(5,2) do not trust it. An API program may have inserted data that does not match the spec.
   
3. Do not be surprised if you read data from one Salesforce instance and it fails to upsert into another instance – the source data may be bad.
   

Sin 5: SOQL has a love/hate relationship with custom fields.

Though Salesforce allows many custom fields to be created on a table, using too many columns in a SOQL statement will cause a QUERY_TOO_COMPLICATED error. At least this “sin” is one Salesforce documents but after 10 years one would assume they would have a work-around in the back-end.

If you really need to do the equivalent of “SELECT * FROM Account” and SOQL will not allow the read in a single statement what needs to be done? There are two problem to solve.

1. Figure out the minimum number of SOQL queries to run. If none of the columns are formulas then this can usually be done by using 500 columns for each query. If formula columns are involved the problem is much harder – in this case see Sin #7.[
   
2. Join the results of multiple queries together.
   

Realistically, grab the data from a Salesforce dump file or use a tool like Capstorm's CopyStorm to extract the table into a relational database. Doing it yourself is hard.

Sin 6: SOQL does not treat all tables equally.

The transaction cost of querying a table with SOQL varies a lot. The first time you query a high cost table and cause your production org to run out of transactions will probably be the last time.

Here a SOQL quiz. How many Salesforce transactions does it take to run the following SOQL statement?

SELECT id, name FROM TableName LIMIT 2000

The answer, of course, depends on TableName and is either 2000, 200, 40, 20, 10, 4, 2, or 1.

The “1 transaction per record” rule for Attachment records is documented. The same rule applies to QuoteDocument and ContentVersion but we have learned this from experience rather than documentation. In general 2000 and 1000 are the common number of records per transaction but the only way to know for certain is the write a bit of code and see how many come back in a bunch.

Sin 7: SOQL chokes on tables with many functional columns.

When Salesforce runs a SOQL query it turns it into SQL first. Once in SQL there is a 64K character query limit. If the 64K SQL query limit is passed then SOQL will return a QUERY_TOO_COMPLICATED exception.

Here a SOQL quiz: Given a SOQL statement how can you accurately (or inaccurately even) guess that it will generated a SQL statement less than 64 characters?

The answer is......if table has a lot of formula columns you probably cannot guess accurately.

In practice, the addition of formula columns in a SOQL statement can cause the size of the resultant SQL to explode – sometimes. It all depends on the complexity and number of tables referenced by the formula.

• The complexity of the formula seems to matter. My guess is that Saleforce is in-lining the formula calculation into the generated SQL.
  
• A formula that references other tables likely causes the table expression and predicate portion of the generated SQL to grow rapidly.
  

The solution to this problem is nearly the same as Sin #5 but the first step, determining the minimum number of SOQL query to run can difficult. The only solution we have found that works reliably is iterative.

1. Sort column in ascending order by the length of their name or length of their corresponding formula.
   
2. Set maxColumn = total # of columns
   
3. Run a query run using columns 1 thru maxColumn with a LIMIT 1.
   

• If the query succeeds then save the query, remove the query's columns from the list of available columns.
  
• If the query fails then set maxColumn = maxColumn – 1 and start step 3 again.
  

4. Set maxColumn = # remaining columns
   
5. If maxColumn > 0 goto Step #3, otherwise all queries have been determined.
   

Credits

The “SOQL Sins” described in this article were accumulated over a number years while developing:

• CopyStorm – keep a hot backup of your Salesforce instance in SQL/Server, Oracle, MySQL, PostgreSQL, and H2
  
• CopyStorm/Restore – restore a backup to any production, development, or sandbox instance. This is a restore tool even your manager could use.
  
• SQLForce – a standard Python module for Salesforce. Works with Python 2.8. 3.3, and 3.4. Use SQLForce when Apex is just too hard.
  

For more information see www.capstorm.com

Load Baseball Teams into SFDC without DataLoader

It's spring and I wanted to populate a sandbox Salesforce with major league basedball teams, their rosters, and a few opportunities and cases (made up of course).  Given the hassle of managing all the links between related objects with DataLoader, I knew I want something a lot simpler.

My approach: With a short Python script and an Excel workbook (one sheet per SFDC table) I managed to import teams and rosters as quickly as I could type them into Excel.

My source data in Excel was simple:

• the first row contained SFDC column names EXCEPT for column 1.
• Column #1 contained a synthetic primary key that I used to refer to the row from other spreadsheets.

For example: The St. Louis Cardinals Account row looked like:

Key	Name	Site	Industry	etc...
Cards	St. Louis Cardinals	St Louis	Baseball

Contact records referred to their associated team using synthetic keys. For example, here is a player from the Cardinals.

Key	LastName	FirstName	AccountId->Account
LouBrock	Brock	Lou	Cards

The special syntax AccountId->Account tells the Python import script to look up the parent record id at run time based on the SFDC id given to the synthetic key.

Is this clever? Probably not so much. But it did make it possible to import a lot of interrelated data into Salesforce with the hassle of importing CSV's one at a time and keeping track of SFDC ids.

For the record, the Python script is in Python 3 and uses two extension modules:

• xlrd for reading Excel workbooks
• SQLForce for talking to Salesforce.
• 
  

How to Populate a Developer Sandbox with Real Data in 5 Minutes

Anyone that has ever tried to populate a Salesforce database with a lot of tables using Dataloader knows the level of pain and patience required. I am not suffering that pain anymore when I need to build a test environment.

Take a guess: How long does it take to populate a developer sandbox with a subset of your production Salesforce data when you want:

• The top 500 revenue Opportunities in the past year.
• All related Account, Contact, Line Items, Quotes, Quote Line Items, and a few other records.

Would you believe 3 minutes and 38 seconds? Here's the screen shot of the stuff I just restored to a blank developer sandbox.

My formerly empty sandbox now has 518 Opportunities, 451 Accounts, 5072 Contacts, and lots of other stuff. I hardly had time to get a coffee. Perhaps the coolest thing is that all the difficult stuff of making sure the the records reference each other property with their newly assigned Salesforce Ids happened.

Salesforce Email Templates & Attachments

This morning I needed to understand what attachments are included with each Email Template. I looked at answering the problem using the Salesforce GUI -- no luck.  I though about doing via SOQL using https://workbench.developerforce.com but knew that SOQL could not represent the query directly.

In the end I answered the question with a short SQL query against my hot backup of Salesforce in SQL/Server.

SELECT EMAIL.name, FOLDER.name, ATT.name
FROM  EmailTemplate EMAIL JOIN Folder FOLDER ON EMAIL.folderId=FOLDER.id
JOIN Attachment ATT ON EMAIL.id=ATT.parentId
WHERE FOLDER.name = 'License Keys' and isActive=1 and ATT.name LIKE 'Confidential%'

It gave me exactly what I wanted.

Email Template Name	Folder	Attachment Name
Confidential API Float	License Keys	Confidential Float API Activation.pdf
Confidential JMS Float	License Keys	Confidential Float JMS Activation.pdf
Confidential NLME Float	License Keys	Confidential Float Software Activation.pdf
Legacy Confidential Float	License Keys	Confidential Float Software Activation.pdf
etc.

A backup is always a good idea...Especially when it save me time.

How to Delete 100,000 Salesforce Tasks While Watching TV

Do you subscribe to one of those pesky mass marketing providers that clutter your Salesforce Tasks with 1000s of new entries every day? I discovered that our Salesforce org had 100,000's of obsolete tasks created by a "Unnamed Company starting with Genius" They were taking up a lot of expensive Salesforce data space and I needed to delete them. Where did I turn? Not to the Salesforce mass delete tools -- I had 100,000s to delete. Instead I wrote the following short Python script and solved the problem in about 10 minutes.

import SQLForce

session = SQLForce.Session("production" )
session.setenv('QUERY_ALL', "true" ) # Force archived records to be read.

##
## SOQL that determines what records to delete. BE VERY CAREFUL HERE!
##s
taskSOQL = """SELECT id, subject FROM Task WHERE isDeleted=false AND subject LIKE 'Genius e-mail %' order by systemmodstamp DESC"""

nDeleted = 0
maxPerBatch = 1000 # This is a good size to prevent the Task query from timing out.
maxPerRun = 50000   # quit once this number of deletes has happened.

while True:
    idsToDelete = []
    soql = taskSOQL + " LIMIT " + str(maxPerBatch)
    print(soql)
    for rec in session.selectRecords(soql):
        idsToDelete.append( rec.id )

    if not idsToDelete:
        break
    

    nThisTime = session.delete('Task', idsToDelete )
 
    nDeleted += nThisTime
    print("Deleted " + str(nDeleted) + " records so far")
    if nDeleted >= maxPerRun:
        break

print("Finished")

The only tricky part is the SOQL that selects tasks to delete. Get this one wrong and you can do major damage to your Salesforce instance quickly.

The Python I used, SQLForce, is freely available to anyone. There's nothing but good news with SQLForce/Python.

• It works on Python 2.3 and 3.3
• It's free and under the EPL..
• Download it here directly from an Amazon/S3 server.
• In addition to the sample scripts there is decent documentation.

Upload an Attachment to Salesforce in One Line of Code

I do a lot of Salesforce work using Python. The experimental SQLForce/Jython work I did a few years ago has been my toolkit of choice until my recent migration of the code to Python (2.7 and 3.3). In the migration I added a couple of packages to make reading/writing attachments dirt simple. Here's an example for writing an attachment:

import SQLForce
from SQLForce import AttachmentWriter

session = SQLForce.Session("MyConnectionParameters")

salesforceParentId = '001A0000014fia3'  # The unique id of the Salesforce object to own the attachment
filename = "/mydir/salesforcecast.docx"   # The file to add as an attachment.

attachmentId = AttachmentWriter( session, salesforceParentId, filename )

The code that uploads the attachment is the last line -- The only tough part is determining the attachment parent. For a more realistic example that reads the attachment data to upload from a spreadsheet see this longer example.

There's nothing but good news with SQLForce/Python.

• It works on Python 2.3 and 3.3
• It's free and under the EPL..
• Download it here directly from an Amazon/S3 server.
• In addition to the sample scripts there is decent documentation.

How to Destroy a Salesforce Instance in 1 minute.

In the past few months I've been working on a tool that restores all or part of a Salesforce sandbox from a backup. Naturally I needed to restore the same sandbox over and over to debug the tool.

Hum...sandbox refreshes can occur at most once per day (developer sandbox). This note is about how I refreshed my sandbox dozens of times per day.

Here's the script:

#!/bin/sh
java -jar sqlforce.jar <<!
connect production gsmithfarmer2@gmail.com mypassword
echo Clearing gmsmithfarmer2 production
DELETE FROM Case WHERE id <> null;
DELETE FROM Opportunity WHERE id <> null;
DELETE FROM Lead WHERE id <> null;
DELETE FROM Campaign WHERE id <> null;
DELETE FROM Account WHERE id <> null;
echo Finished clearing gsmithfarmer2 production
!

The script uses the free SQL/Force tool from www.capstorm .com to do a lot of damage to my salesforce instance in just a few lines of code. Dangerous - yes. Convenient -- yes.

The SQL/Force tools also includes jython integration -- with this you do lots of wacky things to Salesforce data in just few lines of code.

Here's an important bit of SOQL "SELECT * FROM Lead" trivia I bet you do not know...

Quick quiz: If you run the SOQL statement:

•  SELECT id, LastModifiedDate FROM Lead WHERE LastModifiedDate

will it ever return records whose LastModifiedDate is TODAY?

If your answer is "No" then you are wrong. In fact it is pretty simple to make the SOQL return lots of records from TODAY. Here's how.

1. Start the SOQL statement
2. Modified one or more Lead records that match the SOQL criteria BUT have not yet been returned by the query.
3. The query will return the modified records WITH LastModifiedDate value outside the range of the SOQL statement.

I discovered this "feature" working with a customer who has a highly active Lead table (upserts every couple of seconds). Once in a blue moon a lead record would be copied to SQL/Server even though it did not meet the criteria of the SOQL (the app was CopyStorm -- www.capstorm.com). Though it took a while to figure out the problem (since it appeared to be random and rare), reproducing it was easy once the root cause was understood.

How to Extract Attachments from Salesforce

This week the "Salesforce Power User" board had a question about how to easily extract attachments so I took 10 minutes and coded up a solution. The solution uses www.capstorm.com's free SQLForce/Jython tool that has been around for years. I use it with clients every day to write one-off data manipulation scripts and for scheduled jobs that scrub and report on SFDC data. Every admin with scripting skills should have this tool in their back pocket.

Enough self-promotion...here's the solution...
-----------------------------------------------------------------------------------------------------

"""
Extract attachments from Salesforce and put them into regular files.
"""

import SQLForce
import os
import base64

##
## Put all attachments into this directory. A subdirectory will be created for each
## unique parent id. Attachments will be named the same as the "name" field in Salesforce.
##
outputDir="C:/tmp/attachments"

##
## SOQL that find the parent record ids where we want the attachments.
## Change this variable to find the owner's of attachments you want to extract.
##
attachmentParentSOQL="""
    SELECT id FROM Account WHERE name LIKE 'Z%' LIMIT 5
"""

if not os.path.exists(outputDir):
    os.makedirs(outputDir)

def extractAttachments( session, parentIds, outputDir):
 
 
    extractSOQL = "SELECT id, parentId, body, contentType, isPrivate, name FROM attachment WHERE parentId IN ('" + "','".join(parentIds) + "')"
    for rec in session.selectRecords(extractSOQL):
        myDir = os.path.join(outputDir, rec.parentId)
        if not os.path.exists(myDir):
            os.makedirs(myDir)
         
        fh = file(os.path.join(myDir, rec.name), 'wb')
        fh.write( base64.b64decode(rec.body))
        fh.close()
        print rec.name

 
session = SQLForce.Session("sandbox")

parentIds = []
for rec in session.selectRecords(attachmentParentSOQL ):
    parentIds.append( rec.id )
    if len(parentIds) > 200 :
        extractAttachments( session, parentIds, outputDir )
        parentIds = []

if len(parentIds )>0:
    extractAttachments( session, parentIds, outputDir )
 

Quick Opportunity Change Owner Script

As sales people come and go I am often asked to change opportunity ownership status using a variety of criteria. Often I could do the change with the Salesforce UI but a find running a simple SQLForce a lot quicker (and way more adaptable).

I have used the following core script for years -- all I have to do modify the "sqlWHERE" definition in the code when a new situation arises.  Believe me, typing "jython

import SQLForce

def _getOwnerId( session, alias ):
    return session.select("select id from user where alias='" + alias + "'")[0][0];
               
if __name__ == '__main__':
    session = SQLForce.Session("production")
   
    newOwnerAlias = "newSalesperson"
    oldOwnerAlias = 'oldSalesperson"

   
    newOwnerId = _getOwnerId( session, newOwnerAlias )

    sqlWHERE = "owner.alias='" + oldOwnerAlias + """'
                AND isClosed=false
                AND closeDate >= 2012-01-01
                """
               
    updateRecords = []
    for record in session.selectRecords( "SELECT id from Opportunity WHERE " + sqlWHERE ):
        updateRecords.append( [ record.id, newOwnerId ])

       
    if len(updateRecords) > 0:
        print "Time to Update ", len(updateRecords)
        session.update( "Opportunity", ["ownerId"], updateRecords )
    else:
print "No records modified"


Note: SQLForce is freeware at www.capstorm.com

Deep Account Profiles -- Beyond Apex

Sales gave  me an interesting problem lat week:

• Given a pattern that matches one or more account names show the matching accounts, all child accounts, and asset/contact/case/opportunity status/history.
• Give me the results in Salesforce.

Wow! Given the SFDC query governors I knew there would be no way of delivering a solution using APEX.  The query limits would be blown before I was halfway thru some of the multi-nationals we serve.

First the results:

The user can enter a number of parameters at the top of the screen. In the example a query of "*Queensland" as entered as the account. The tool found all accounts containing "Queensland" and all children of the accounts. They are arranged in in a hierarchy in the top table (along with some summary data).  The links in the top table jump to the details for each account. Other links on the page link to the corresponding Salesforce record.

How Was It Done?

The first step was easy. Get the data out of Salesforce into a RDBMS so the queries will run fast without Salesforce governor limits. We have used Capstorm's CopyStorm product to maintain an "almost live" copy of Salesforce in SQL/Server for several years (it runs as a cron job and keeps our SQL/Server up to date).

The second step was to develop the SQL queries.This step was quick because the tools were developed in SQL/Server -- fast turn around time. Here's the query that finds active assets for an account.

SELECT PROD.name as Product, ASSET.number_of_users__c as NumUsers, PROD.licenseType__c as LicenseType

    , PROD.licenseKeyMethod__c as KeyMethod, PROD.industry__c as Industry

    ,  ASSET.purchaseDate as PurchaseDate, ASSET.usageEndDate as UsageEndDate, ASSET.licenseKeyIssueDate__c AS KeyIssueDate

    , LIC.name as License

    , ASSET.id as assetId, LIC.id as licenseId

    FROM salesforce.Asset AS ASSET JOIN salesforce.Product2 as PROD ON ASSET.product2id=PROD.id

    JOIN salesforce.Software_License__c AS LIC ON ASSET.licenseId__c=LIC.id

    WHERE (ASSET.usageEndDate >= GETDATE() )

ORDER BY ASSET.usageEndDate DESC, PROD.name ASC

The third step was to develop a simple servlet that returned the results of a query in JSON format. This part took the most time -- a couple of hours.

The fouth step was to consume the servlet JSON in a bit of html/javascript/JQuery and create the web page you see a the top of this post.  Fiddling with the css took the most time.

The final step was to create a Visual force page to hold the HTML page from the previous step. This is the step that makes the tool look like it is part of salesforce. This step was surprisingly easy (the greater/less than  characters have been replaced with [] to make the code easier to paste in the doc).


[apex:page ]
[!--
Access to the account profile tool that lives inside the firewall.
Note: It works on salesforce data that is a day old.

 --]
[script src="//ajax.googleapis.com/ajax/libs/jquery/1.8.0/jquery.min.js"][/script]
[script language="javascript"]
$(document).ready( function() {
var height = $("body").height();
$("#dmzReport").css('height', height + "px" );
});


[/script]
[iframe id="dmzReport" height="600px" width="100%" scrolling="true"
src="http://mysecret.com/superman/SalesforceReportServer/AccountProfile.html"
][/iframe]
[/apex:page]

The Moral of the Story

Having a "hot" copy of your Salesforce data in a RDBMS opens up all kinds of  options that are just not possible (or affordable) with straight Salesforce/Apex.  Rather than spending hours working around SFDC governor limits or dealing with SFDC batch jobs a RDBMS copy of your data let's you concentrate on solving the business problem at hand.

The cost of maintaining our "hot" copies of SFDC is cheap (we maintain both MySQL and SQL/Server copies). In our case the work is done by a simple cron job -- our effort is zero.

Here's an interesting problem. Sales wanted an excel spreadsheet that would help them call Salesforce contacts that have purchased products. That's easy enough -- Contacts JOIN Assets on AccountId.
Here's the hard part: Sales wanted the products purchased as:
- 1 column per product
- Put the # of seats in the row for each contact (or blank if not purchased).

If any RDBMS this is a fairly simple task using the RDBMS specific PIVOT operation. Excel could be used to create the pivot from raw data. In either approach the solution is not simple enough (especially since the resultant spreadsheet had around 100,000 rows and 80 columns).

I solved this problem in 3 minutes with a single MS/SQL query. How? I maintain a mirror of my Salesforce production instance using the CopyStorm product.  My mirror is within 24 hours of production salesforce data (my choice -- I could have selected within 15 minutes).

Here's the query (I masked some of the data in the query).


use salesforce

SELECT * FROM
(
SELECT
ACCT.name AS accountName, CONTACT.name as ContactName
,PROD.name AS productName, ASSET.number_of_users__c AS nUsers
,ASSET.purchaseDate

FROM
Account AS ACCT
JOIN Contact AS CONTACT
ON CONTACT.accountId=ACCT.id

JOIN Asset as ASSET
ON ASSET.accountId = ACCT.id

JOIN Product2 AS PROD
ON PROD.id = ASSET.product2id
WHERE
ASSET.purchaseDate IS NOT NULL
AND PROD.family LIKE 'FamilyName%'

) AS RAWDATA
PIVOT(
SUM(nUsers) FOR productName IN ([Product A], [Product B], [Product C], [Product D])
) pvt

SQLForce Saves Marketing Many Hours

Marketing had an interesting problem last week.

Marketing: We want to create a price book just like the "2012 Standard Price Book" but only include USD prices. Can we just clone the "2012 Standard Price Book" and have you fix it.

Any guesses about how much work it was to "fix" the cloned price book?  In sqlforce it took a single line:

SQLForce> DELETE FROM pricebookentry WHERE pricebook2.name='Distributor Price Book - 2012' and currencyIsoCode<>'USD';

Yes, using the DELETE statement is dangerous. The wrong WHERE clause (or no WHERE clause) can quickly destroy a lot of data. However, judiciously used, DELETE can save a lot of time.

Salesforce Rich Text Fields -- Problem Solved

I had an interesting problem this morning. An account rep had a small table in MS/Word that he wanted to pasted into a Salesforce rich text field.

Big Problem: Though the table was small all of the style crap added by MS/Word exceed the columns' 32,000 character limit for rich text fields. You think this would be an easy problem to solve?

Here is what I had to do:

First attempt: 
I saved the MS/Word as filtered HTML – too big.

Second attempt: 
I used CKEditor to create new html – too big.

Third attempt: 
I hand crafted the HTML, visualized it in Chrome, and then did a copy/paste from Chrome to SFDC – still too big. The SFDC gadget transformed my short HTML into something bigger than 32K. Odd behavior – SFDC sandbox worked; production SFDC did not. They must be updating the SFDC gadget in Winter’11.

 Final attempt:
 I wrote a one-line sqlforce UPDATE statement and patched the field. Finally, success.

• UPDATE Quote SET SpecialInstructions__c='....' WHERE id='00a88789a7s89af'

I love sqlforce. 

Upgrade to Summer'11

The SQLForce, CopyForce, and JavaForce updates to the Summer'11 protocol (22.0) have been posted to code.google. Other than support for a new SFDC column type and new proxy command line args there is nothing new to report.

I've been having problems getting CopyForce to work consistently thru proxy servers on Linux.
Are there any guru's reading that can throw me some same sforce connection code? On Windows it was easy to make the app use the system proxy and certificate store. I am having no such luck with Linux.

The next upgrade to CopyForce will dynamically alter the RDMBS schema when new columns are added (or existing columns are altered) in force.

Thanks for all of the downloads and feedback. Feedback is the only $$ for this job. I appreciate it a lot.

It's too hot to code. MySQL Anyone?

CopyForce (and CopyStorm -- the commercial variant) does not support MySQL (I have not needed it on a project). This morning I took a look at the effort....the effort looks small. In a couple of hours I was exporting Salesforce data.

A question: Is there enough MySQL interest to make it worth my effort? Please drop me a line if you would be interested in trying it out. If few people respond, I probably will not launch a MySQL version.

Since it is summer and time for outdoor fun, progress on a WAR that provides browser access to CopyForce data has slowed. Though the individual record pages and list of record pages are in place, I will need some concentrated time to bring it to beta state. Maybe this fall.

It's time to take a swim (it's hot outside).