Salesforce.com DEV 501 Class

Finishing up the Salesforce.com Advanced Developer class today. Here is a brief overview of everything that was covered in the class.

• Force.com Introduction
• APEX Overview
• Triggers 
• Classes
• Debugging
• Testing
• Deployment
• Databases
• SOQL (Salesforce Object Query Language)
• SOSL (Salesforce Object Search Language)
• DML (Data Manipulation Language)
• Web Services
• APEX and Email
• Visualforce Overview
• Standard and Custom Controllers
• Controller Extensions

Needless to say, my head is currently spinning and will most likely continue to spin for a while till it all sinks in.

A Couple Useful Excel Formatting Codes

There are a couple excel formulas that I find myself recreating over and over and over. When data comes from a client it is never in the perfect format to load into Salesforce with a tool like APEX Data Loader (as seen from the post below) here are a couple.

Date:

Typical format is something like

8/12/2009 9:30

All I want is the date without a time so here is what I use

=month()&"/"&day()&"/"&year()

In each of the parentheses put the cell you want to convert, like =month(B2)&...

Name Concatenation:

Sometimes I need to put names together or bring them apart or just change the spacing between them, you can use a combination of concatenations and the "Left", "Right", and "Mid" functions in SF as well.

=concatenate("left(B2,3)","mid(B2,5,2)","right(B2,4))

Then when you finish the first one just click on the cell, then scroll down to the end of the list that needs to be changed and highlight every line and press Fill then Down in excel. This will apply the formula to every field selected in the column. Next select all the fields that have just been created and copy them, then right click on the column they need to go and click Paste Special... then click Values then OK. This will copy the contents of the cell and not the formula so you will then be good to load!

Converting Datetime in Excel to a format Data Loader recognizes

I have had a number of clients that would like to move activities from their old CRM over to Salesforce. Bringing  these activities or calendar events over as Events into SF has given me all sorts of trouble recently. The problem lies in the Datetime fields that are imported into SF such as ActivityDateTime, StartDateTime and EndDateTime which the customer usually needs as this contains the not only the date, but the time of the event as well.

For these imports I always use the APEX Data Loader and the files I use to import are in Excel CSV format. So I have to be sure that the CSV file is formatted properly before importing everything into SF.

Most often the datetime will be exported out of the old CRM in a format similar to this

8/19/2005 9:30

I have found that Data Loader is very specific about how it receives a datetime. If you try to import the format above into a datetime field with Data Loader, it will not only not load, but it will not appear in the error file, it simply skips over the record all together.

The formats Data Loader needs in order to work properly as described in the Data Loader Developer's Guide are:

We recommend you specify dates in the format yyyy-MM-ddTHH:mm:ss.SSS+/-HHmm:

• yyyy is the four-digit year

• MM is the two-digit month (01-12)

• dd is the two-digit day (01-31)

• HH is the two-digit hour (00-23)

• mm is the two-digit minute (00-59)

• ss is the two-digit seconds (00-59)

• SSS is the three-digit milliseconds (000-999)

• +/-HHmm is the Zulu (UTC) time zone offse

• yyyy-MM-dd'T'HH:mm:ss.SSS'Z'

• yyyy-MM-dd'T'HH:mm:ss.SSS Pacific Standard Time

• yyyy-MM-dd'T'HH:mm:ss.SSSPacific Standard Time

• yyyy-MM-dd'T'HH:mm:ss.SSS PST

• yyyy-MM-dd'T'HH:mm:ss.SSSPST

• yyyy-MM-dd'T'HH:mm:ss.SSS GMT-08:00

• yyyy-MM-dd'T'HH:mm:ss.SSSGMT-08:00

• yyyy-MM-dd'T'HH:mm:ss.SSS -800

• yyyy-MM-dd'T'HH:mm:ss.SSS-800

• yyyy-MM-dd'T'HH:mm:s

• yyyy-MM-dd HH:mm:ss

• yyyyMMdd'T'HH:mm:ss

• yyyy-MM-dd

• MM/dd/yyyy HH:mm:ss

• MM/dd/yyyy

So, in order to accommodate this stringent formatting that is required for a Datetime, I had to create a custom format in Excel. Here is what I did...

• Right Click on the Datetime field and select Format Cells
• Under Category select Custom
• Under Type: paste this text " yyyy-mm-ddThh:mm:ss.SSS "EST" "
• Click OK

Alternatively if the date and time are in different columns in the spreadsheet you can use a formula like to to bring them together:

=TEXT(YEAR(D2),"0000")&"-"&TEXT(MONTH(D2),"00")&"-"&TEXT(DAY(D2),"00")&"T"&TEXT(HOUR(E2),"00")&":"&TEXT(MINUTE(E2),"00")&":00.000 EST"

This format will load cleanly into SF with Data Loader!

In order to apply that formatting to the other cells (I know there are probably 700 different ways to do it with excel) just right click and copy the cell that was just formatted and highlight all of the other cells that you need to apply the formatting to then right click and select Paste Special and select the radio button Formats then click OK. 

Then you are good to go!