Query for Exporting from Goldmine CAL table to Excel

Below are two queries that can be used to extract from the CAL table in a Goldmine SQL database.

If the Notes and RFC822 fields are in a Blob format where their values are not standard text use this:


Select
c.USERID, c.ACCOUNTNO, c.RECTYPE, c.ONDATE, c.[ONTIME], c.[DURATION], c.REF as [Subject History - REF],
c.LINKRECID, c.recid,
CAST(CAST(c.NOTES AS varbinary(4000)) AS varchar(4000)) AS NOTES,
CAST(CAST(m.RFC822 AS varbinary(4000)) AS varchar(4000)) AS RFC822

From CAL as c
Left Join MAILBOX as m
ON c.LINKRECID = m.recid


If the Notes and RFC822 fields are in a standard text format you can use this query:



set textsize 4000

Select
c.USERID, c.ACCOUNTNO, c.RECTYPE, c.ONDATE, c.[ONTIME], c.[DURATION], c.REF as [Subject History - REF],
c.LINKRECID, c.recid, c.NOTES, m.RFC822

From CAL as c
Left Join MAILBOX as m
ON c.LINKRECID = m.recid

Salesforce.com Professional Edition Pain Points

The most common editions of Salesforce.com that businesses will sign up for are Professional Edition and Enterprise Edition. A quick way to tell what version you are on is when using google chrome as your browser if you go to the home tab in Salesforce, the chrome tab will say "Salesforce.com - Professional Edition" or "Salesforce.com - Enterprise Edition".

There are a number of items that are sorely missed when going from Enterprise to Professsional Edition, below is a list of items missing from Professional Edition that can cause some headaches.

• Workflow Rules
• Apex Triggers
• Apex Classes
• API
• Joined Reports
• Custom Profiles
• Record Types
• Grant Login Access

You can see a more detailed list of what is offered in all of Salesforce's editions here:
http://www2.sfdcstatic.com/assets/pdf/datasheets/DS_SalesCloud_EdCompare.pdf

Pull Record Type ID in Apex

This is just a quick query that grabs the record type id and assigns it to a string. I am always needing this so decided to put it up on the blog for reference. You need something like this when specifying a record type because if you would hard code the id into the trigger/class in production it would fail because everything has a different id from Sandbox to Production.

RecordType r = [select id from RecordType where name = ‘Account Recortype’ AND SobjectType = 'Account'];

String AcctRecordtypeID= r.id;

So the string AcctRecordtypeID will now have the record id of the record type pacesetter.

If there are multiple record types out there with the same name associated to different object, you can just adjust the where statement to be more specific about where to pull the RecordType from.

Alternatively the same query could be used with a list. This will prevent an error in the event that there is no recordtype with the name specified.

List<RecordType> recType = [select id from RecordType where name = 'Pharma' AND sobjecttype = 'Account' AND IsActive = TRUE limit 1];

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

}

Map Described

I have recently begun to understand the value of Maps when writing Apex triggers and classes, they can be very powerful. The most common Map used is the 'Map <ID, sObject>' this means that the Key of the Map is the ID and the value is the object related to the ID.

Typically you will see it initiated in this way:

Map<ID,sObject> sObMap = new Map<ID,sObject>([SELECT *fields* FROM sObject WHERE *conditions*]);

This is how I used it in a recent bit of code I wrote. This trigger copies the owner of the Account related to a case down to the case record on a custom field.




trigger CaseBeforeInsertTrigger on Case (before insert) {

    Set<Id> accountIds = new Set<Id>(); //set to hold list of Accounts

    for (Case c : Trigger.new) { //loop through all cases and pull the Account ID into the set above
        if(c.AccountId != null) {
        accountIds.add(c.AccountId);
        }      
    }
 
    Map<ID, Account> acct = new Map<ID, Account>([select id, ownerid from Account where id = : AccountIds]); //create a map where the ID is the Key and the Value is the sObject (Account)

    if(acct.size() > 0) {
        for(Case c: Trigger.new) { //if there are values in the map, loop through the new cases
      if (c.AccountId != null){
     
          Account acctUpdate= acct.get(c.AccountID); //look back into the map and using the Account ID grab the Account sObject
          c.Account_Owner__c = acctUpdate.OwnerId;
     
      }
        }
    }
}