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.