Importing Zip Codes with a Leading Zero into Salesforce.com

When importing Zip Codes into Salesforce because the file must be in CSV format you can run into problems you didnt know were there. For example when you open up a data file in CSV format if there are any Zip codes with leading zeroes like 01752 they will appear with the leading zero chopped off and will display like 1752.

You can follow these steps below to be sure to load those leading zeroes.

1. Select the entire Zip Code column from within Excel. 

2. Select the menu option Format -> Cells...

3. Select the Number tab, and click on the category called "Special".

4. You can then select the type Zip Code or Zip Code + 4, and then click OK.

Be sure to save the changes to your .CSV file. 

NOTE: If you reopen the saved .CSV file in Excel, the formatting is not preserved. Opening in Notepad preserves the formatting. Save the formatting of the zip code for your last step when importing records. If you do not reopen the .CSV file in Excel before importing, the zip codes are imported with the appropriate leading zeroes.




These steps came from the Salesforce custom community:

https://success.salesforce.com/questionDetail?qId=a1X30000000HZDMEA4

How to Quickly Check the Old Value in an APEX Trigger

Using the Old Map context variable in a trigger can quickly look back at the old value of a field in a trigger. This can be helpful when you need to write a trigger that is checking to see if a value has been changed...

for ( lead l_new: Trigger.new ){

     if ( l_new.Status != Trigger.oldMap.get ( l_new.id ).Status ) {
   
     }

}

Automating Salesforce Approval Processes with Apex Triggers

Ran across this the other day and wanted to re-post it, looks easy enough to implement...

http://blog.jeffdouglas.com/2010/01/04/automating-salesforce-approval-processes-with-apex-triggers/

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

trigger OpportunitySubmitForApproval on Opportunity (after update) {
 
 for (Integer i = 0; i < Trigger.new.size(); i++) {
 
  if (Trigger.old[i].Probability < 30 && Trigger.new[i].Probability >= 30) {
 
   // create the new approval request to submit
   Approval.ProcessSubmitRequest req = new Approval.ProcessSubmitRequest();
   req.setComments('Submitted for approval. Please approve.');
   req.setObjectId(Trigger.new[i].Id);
   // submit the approval request for processing
   Approval.ProcessResult result = Approval.process(req);
   // display if the reqeust was successful
   System.debug('Submitted for approval successfully: '+result.isSuccess());
 
  }
 
 }
 
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

@isTest
private class TestOpportunitySubmitForApproval {
 
    static testMethod void testApprovalSuccess() {
 
        Opportunity opp = new Opportunity();
        opp.Name = 'Test Opp';
        opp.Amount = 100;
        opp.CloseDate = Date.today();
        opp.Probability = 10;
        opp.StageName = 'Prospecting';
        // insert the new opp
        insert opp;
        // change the probability of the opp so the trigger submits it for approval
 opp.Probability = 40;
 // update the opp which should submit it for approval
 update opp;
 
        // ensure that the opp was submitted for approval
        List<ProcessInstance> processInstances = [select Id, Status from ProcessInstance where TargetObjectId = :opp.id];
 System.assertEquals(processInstances.size(),1);
 
    }
 
}

White List IP Address for Data Loader access with no Security Token

Just found out that if you white list your IP address in a Salesforce org you do not need a security token to use APEX Data Loader. If a login is shared and the email address associated with the login is someone hard to reach, you can just do this and bypass getting a security token to load data!

Setup > Your Name > Security Controls > Network Access > New

Make the Start and End IP address your personal PC's IP address.

Note: You can do a google search of 'Whats my ip' to get your computer's ip address.

Once this is done when you login to data loader still use your username, but rather than using passwordSECURITYTOKEN as the password, all you need is the password.

Strip HTML Tags in SQL

At the bottom are a couple links that helped me to strip HTML tags from columns in SQL.

First create the UDF using the following code:

CREATE FUNCTION [dbo].[udf_StripHTML] (@HTMLText VARCHAR(MAX))
RETURNS VARCHAR(MAX) AS
BEGIN
    DECLARE @Start INT
    DECLARE @End INT
    DECLARE @Length INT
    SET @Start = CHARINDEX('<',@HTMLText)
    SET @End = CHARINDEX('>',@HTMLText,CHARINDEX('<',@HTMLText))
    SET @Length = (@End - @Start) + 1
    WHILE @Start > 0 AND @End > 0 AND @Length > 0
    BEGIN
        SET @HTMLText = STUFF(@HTMLText,@Start,@Length,'')
        SET @Start = CHARINDEX('<',@HTMLText)
        SET @End = CHARINDEX('>',@HTMLText,CHARINDEX('<',@HTMLText))
        SET @Length = (@End - @Start) + 1
    END
    RETURN LTRIM(RTRIM(@HTMLText))
END
GO

Then to run the UDF you simply would include it in the query like this:

select USERID, ACCOUNTNO, RECTYPE, ONDATE, ONTIME, REF, dbo.udf_StripHTML(NOTES), LINKRECID, recid, RFC822 from HISTandMAILtrimmed


http://stackoverflow.com/questions/457701/best-way-to-strip-html-tags-from-a-string-in-sql-server

http://ask.sqlservercentral.com/questions/2022/how-to-execute-sql-udf-function.html

Try Catch Statement Used When Inserting Records in APEX

try{
    insert pcList;
}
catch(DmlException e){
     System.debug('AgencyCommissionTrigger: DML Exception: Error during AgencyCommissionTrigger updates in the AgencyCommissionTrigger trigger.');
     for (Integer i = 0; i < e.getNumDml(); i++) {
          System.debug(e.getDmlMessage(i));
     }//end for
}//end catch

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