Wednesday, July 8, 2015

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.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.

No comments: