Friday, January 31, 2014

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.

Friday, January 24, 2014

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.

Tuesday, January 21, 2014

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.