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