Tuesday, May 11, 2010

Moving Salesforce Attachments Around

I had an interesting problem (from Portugal) early this morning. A series of attachments had been attached to the wrong account. We wanted to move the attachments to a new account -- NOT BY HAND.
It would be nice is Salesforce supported something like:
  • UPDATE Attachment SET parentId='new' WHERE parentId='old'
Unfortunately Salesforce does not support updating of the parentId field.

Fortunately, the jython SQLForce module made this problem easy to solve. Here is the complete script:

import SQLForce

def getAccountId( session, name ):
    records = session.selectRecords( "SELECT id FROM account where name='" + name + "'")
    if 1 == len(records):
        return records[0].id
    return None

if __name__ == '__main__':
    session = SQLForce.Session("sandbox")
    srcAccountId = getAccountId( session, "Biota Scientific Management Pty Ltd")
    destAccountId = getAccountId( session, "Biota Structural Biology Laboratory")
    print "Src Account", srcAccountId
    print "Dest Account", destAccountId
    for att in session.selectRecords( "SELECT id, body, contentType, isPrivate, name FROM attachment WHERE parentId='" + srcAccountId + "'"):
        session.insert( "attachment", ["body", "contentType", "isPrivate", "name", "parentId"],
                        [[att.body, att.contentType, att.isPrivate, att.name, destAccountId]])
        session.delete( "attachment", [att.id] )

Yeah, I should have cached both the updates and deletes into a python array and made two calls to Salesforce instead of two per attachment. What can I say -- I was lazy.