Putting Multi-Select Pick List Fields from Multiple Columns into One Cell

I have been working on this solution for a long time and google could not provide the answer, but I figured it out!

The problem is this, when I get data from a client sometimes multi-select pick list fields are seperated into different rows of the excel file, like this.

ID                  Pick List Value
ABC123        Cat
ABC123        Dog
ABC123        Bird
DEF456         Cat
DEF456         Bird
XYZ789         Cat
XYZ789         Dog
XYZ789         Bird

For data loader we need this to be in a forma like this:

ID              Pick List Value
ACB123    Cat;Dog;Bird
DEF456     Cat;Bird
XYZ789     Cat;Dog;Bird

The first things that needs to be done is the ID column needs to be sorted in alphabetical order. From there I wrote an Excel formula that says IF(the ID in this column and the one above it match, Concatenate the value in this column with the one in the cell directly above it separating them with a ';', otherwise just give me the value in the in this column).

The Excel formula looks like this and would go in the cell directly to the right of the pick list value field above.

=IF(A2=A1,CONCATENATE(B1,";",C2),B2)

Next things get a bit tricky. We need to somehow remove the columns that we are not going to be using (we could just import the list as it is because it would overwrite the field as many times over until it hit the largest one, but it would make sense to have a spreadsheet that just had the values we are using.)

Add a new column to the left of the formula field called 'Length'. add the formula =LEN(C2) and fill it to the end of the spreadsheet. Next sort this value largest to smallest, then resort the ID's into alphabetical order, this will put them in an order that groups them by ID number ordered by the full list of options first going down to just one option.

Next we need to remove duplicates based on the ID number. Select the entire spreadsheet and select 'Remove Duplicates' deselect all columns except for the ID column, then click OK.

This will leave you with a fully concatenated list of values that only displays one record for each ID!

Custom Settings Update Trigger

Finally getting back into some more development... We had a situation where in order to make the code dynamic it made sense to use Custom Settings. I have never used them before today, there are Hierarchy and List Custom Settings. Hierarchy can only have one set of values per Profile or User, while List can have multiple values for a single Custom Setting.

The problem is that we need a way to dynamically add a 'Level' to a custom object based on the record type of the object. If a new record type is added all we want the sys admin to do is add a new custom setting and have the code do the rest. So long as the new custom setting and the record type have the same name the code will work.

First the code looks to the Custom Setting and pulls all values out of it and stores them in a list. Next we determine the size of the list, then we run a for loop that loops through if statement no more than the size of the list. If it finds that the field called 'Record_Type__c' in our Custom Setting called Level_Setting__c matched the name of the record type of the territory. (I had to use a formula field that just pulls the record name because I could not find a field on the custom object that was the name of the record type.) Finally if it finds a match it sets the custom field on the Custom Setting called Level__c equal to the text field Level__c on the Territory.

So long as the Record Type and the Level in the Custom setting are both in the correct order numerically and the record type names match up, this code working alongside the Custom Setting will work nicely.


trigger BeforeInsertUpdateLevelUpdateTrigger on Territory__c (before insert, before update) {

for(Territory__c t: trigger.new){

List<Level_Setting__c> levelSet = Level_Setting__c.getall().values();
//Pull values from the Custom Setting 'Level_Setting__c'

integer size = levelSet.size();
for(integer i=0; i<size; i++){
                      //Loop through the list of Level_Settings__c to find a match to the record type
if(levelSet[i].Record_Type__c == t.RecordTypeName__c ){
t.Level__c = levelSet[i].Level__c;
}
}
}
}

SQL Query - BLOB Conversion to String

Recently all the data migrations that I have been doing I have had to try to learn some SQL on the fly. It has been a bit of a struggle, but with all the information out there on it Google has become my best friend.

A recent issue I ran into was the fact that some information is store in a SQL database in a Binary Large OBject for or BLOB. It will usually look something like this

UEHFNSDK56N36KI3N6KI3N3M46AFD8XCV78912HJKASUIY45LHSDA

Just a bunch of non-sense. I usually need it to be in a an excel spreadsheet in a reasonable format that can be read, the text above mean nothing. So I did a bit of research and putting a few thing that I learned together I a an SQL Query that looks like this to convert it to text.

select AccountNo, CONVERT(varchar(max),convert(varbinary(max),NOTES)) from CONTHIST

This gives me 2 columns in the query results, the first being the AccountNo that I would use to match the notes up to a given account and the second being a column  of converted BLOB text.

This is all well and good, but for the life of me I can not figure out how to get the query results out into a spreadsheet or even a manageable format that I could put into a spreadsheet. I can get standard query results into excel, but as I have been told, because the notes contain return chars that breaks up the copy and paste.

As soon as I figure this out I will be sure to post it so I don't forget it!





Here is a nice little replace function that removes carriage returns that can cause headaches going from SQL results to Excel. TaskItem would be the column name.

REPLACE(taskitem, Char(13) + Char(10), ' ') as TaskItem

Replace and convert at the same time:

REPLACE(convert(varchar(max),convert(ntext,TaskToDo)), Char(13) + Char(10), ' ')

Concatenate Date and Time into Data Loader Format

There are a number of ways to do this, here are two ways I have found that work well....

If you format something as TEXT in excel you can format it right in the formula. For example when working with dates and times that are separate:

          =CONCATENATE(TEXT(A2,"yyyy-mm-ddT"),TEXT(B2,"hh:mm:ss.000")," EST")

Would give you the proper Data loader import format when A2 is the date and B2 is the time.


Another way to do this is to just add the two columns and format them like this then format the results:

          =A2+B2

          Format -> Custom -> Type: yyyy-mm-ddThh:mm:ss.000 "EST"


I have found both ways work well, but it will depend on how the data is formatted to make the decision on which one works best.

Datetime Revisited

My first post was about Datetime fields used with Data Loader. I recently had to import these and noticed that I did not use the proper format in my example, below I have made the necessary changes.

Also I added in the timezone, this will cause headaches if you do not specify the timezone. The format below worked out perfectly for me.

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

Passed the Salesforce.com Developer 401 Exam

I am now a Certified Force.com Developer! I just passed the DEV 401 exam and am now a Certified Developer.

Trigger to Prevent Deleting a Record based on Record Type

Fresh off of a full week of Development Classes in Washington D.C. it was time to put that knowledge to work! Here's the scenario:

We need to prevent a user from deleting a contact when they are a particular record type. I put this together in my developer account so it is a rather contrived example but it can be altered to fit the needs of anyone...

Trigger:

/*
If a user attempts to delete under these conditions,
prevent the delete and display this message at the top of the Contact page layout:
“You are not permitted to delete a Contact with a recrod type of 'Other'.”.
*/

trigger TestContactBeforeDeleteTrigger on Contact (before delete) {


List<RecordType> recType = [select id from RecordType where name = 'Record Type A' AND sobjecttype = 'Contact' AND IsActive = TRUE limit 1];

 if(!recType.isempty()){
String RecordTypeAid = rectype[0].id;

  for(Contact con: trigger.old){

       if(con.RecordTypeId == RecordTypeAid){
         con.addError('You are not permitted to delete an account with a record type of "Other"');
             }
        }
}
}


Test Class:

/*
Test class for TestContactBeforeDeleteTrigger
*/

@isTest
private class TestBeforeDelete {

    static testMethod void myUnitTest() {
     
List<RecordType> recType = [select id from RecordType where name = 'Record Type A' AND sobjecttype = 'Contact' AND IsActive = TRUE limit 1];

  if(!recType.isempty()){
String RecordTypeAid = rectype[0].id;
   
Contact c1 = new ContCt();
c1.LastName = 'Test Last Name for Trigger';
c1.RecordTypeID = RecordTypeAid;

        insert c1;
   
        try{
         delete c1;
        }
        catch (dmlexception e){
       
         system.assert(e.getMessage().contains('You are not permitted to delete an account with a record type of "Other"'),
         e.getMessage() );
      }
    }
    }
}

A special thanks to Peter Gruhl who taught the DEV501 class. The class was quite a bit to handle and he taught it VERY well. Thanks Peter!

This code was updated on 6/26/2013 to remove any references to hard coded Record Type IDs. I have included a SOQL line that pulls the RecordTypeID from the system dynamically so this will work from Sandbox to Production where the record type ids are different.

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!