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.