Friday, December 3, 2010

A Busy SQLForce week in Flyover Land

There was a lot of SQLForce activity this week. New distros were published that fix all reported problems.

The jython module saved me a quite a bit of work today. Ages ago someone create a free form string column on Account that was supposed to hold the beginning month of an accounts fiscal year. Naturally, people had entered the data in a variety of forms. I wanted to changed the type of a picklist and convert the existing data to consistent month names. How long did this take? About 10 minutes.

  1. Find all unique ways that people had expressed the data.

    SELECT DISTINCT FiscalYearEnd__c FROM Account WHERE FiscalYearEnd!=null
  2. Write a tiny bit of Jython that maps all of the "expressed" ways to normal ways.

    myMap = { "Jan":["1/1", "January", "janary", "01-01", ], "Feb":[...] }
  3. Write a tiny bit of Jython that creates SQL statements that look like:

    UPDATE Account SET FiscalYearEnd__c="Jan" WHERE FiscalYearEnd__c="1/1"
  4. Run each SQL command (in the Jython).

    session.runCommands( "UPDATE Account ..." )
Try doing steps 1 and 3 using base Apex tools. There is no way I could finish the job in 10 minutes.

Time to pick up some pizza. Have a great weekend.

No comments: