Syntax Highlighter JS

Saturday, March 31, 2012

Caching data from Google's Geocoding service

UPDATE: Please note that the code in this post no longer works due to changes in the Google maps API.  Please see my latest post about this.

So you have looked at the free Find Nearby app and decided it was cool but didn't fit your needs.   Maybe you even looked at someone's google maps mashup with salesforce and decided you want to develop your own integration with google maps.   This is the road I recently went down.

To show items on google maps, you need the Longitude and Latitude coordinates.  You can get these for a given address by using the google geocoding service.  The thing is you are only allowed a total of 2500 requests per day (per IP address) and you must space them out.  I haven't found any documentation on the limit per minute but it appears you need to space requests out by at least 200ms each.   This can make for a really slow painting map if you have alot of points.

Google recommends that you cache out your geocoding results so that you can quickly render maps on the fly.

This post is going to look at how you can do this (for billing addresses on accounts) in apex on

First you will need to make the following fields on the account object:

These fields will hold the results of the geocode request.

Please note that the text fields are longer than needed given the current resolution of google's geocoding service.   This was done to leave room for greater detail in the future but you can limit them if you need to save space.  Google's current length is 18 characters all inclusive.

These fields (again on the account object) will be used to insure previous attempts don't get repeated until after the initial process (i.e. untouched entries will always get priority).

 The formula field will need to be set to the following formula:
If(LastModifiedDate > GeocodeLastUpdated__c, "True", "False")

Next, you will need to setup a remote site because the salesforce documentation indicates that "Before you can access external servers from an endpoint or redirect endpoint using Apex or any other feature, you must add the remote site to a list of authorized remote sites in the Salesforce user interface. To do this, log in to Salesforce and select Your Name | Setup | Security Controls | Remote Site Settings."

Here is how those settings should look:

And now you are ready to create this apex class:

public with sharing class GoogleGeoCodeUpdater { // used to build address to geocode (from database fields in SFDC) public string Address {get;set;} public string City {get;set;} public string State {get;set;} public string Zip {get;set;} public static List <Account> AccountsToGeoCode = new List <Account>(); // When you specify future, the method executes when Salesforce has available resources. @future(callout=true) public static void ProcessUpdates(){ if (PopulateAccountList() == true ) { for(integer i=0; i<AccountsToGeoCode.size(); i++) { GeocodeAccountAddress(AccountsToGeoCode[i]); } // loop thru accounts list // Update all changed records in one DML statement update AccountsToGeoCode; } // check for PopulateAccountList = true } // ProcessUpdates private static string BuildAddressString(Account AccountToBuildAddress) { String Address = ''; // urlEncode = obtain the bytes for unsafe characters if (AccountToBuildAddress != null) { if(AccountToBuildAddress.BillingStreet != null) { Address = EncodingUtil.urlEncode(AccountToBuildAddress.BillingStreet,'UTF-8')+ ',+'; } // Street != null if(AccountToBuildAddress.BillingCity != null && AccountToBuildAddress.BillingState != null) { Address = Address + EncodingUtil.urlEncode(AccountToBuildAddress.BillingCity,'UTF-8')+ ',+' + EncodingUtil.urlEncode(AccountToBuildAddress.BillingState,'UTF-8'); } // City != null && State != null if(AccountToBuildAddress.BillingPostalCode != null) { Address = Address + ',+' + EncodingUtil.urlEncode(AccountToBuildAddress.BillingPostalCode,'UTF-8'); } // zip ! = null } // AccountToBuildAddress != null Return Address; } // BuildAddressString private static void GeocodeAccountAddress(Account AccountToGeoCode) { String GeoCodeRequestURL = ''; GeoCodePoint CurrentGeoCodeResult = new GeoCodePoint(); HttpRequest GeoCodeRequest = new HttpRequest(); Http GeoCodeHttp = new Http(); GeoCodeRequest.setMethod('GET'); GeoCodeRequestURL = ''+ BuildAddressString(AccountToGeoCode) + '&output=json&sensor=false'; GeoCodeRequest.setEndpoint(GeoCodeRequestURL); // no HTTP call outs from test methods ... if (test.IsRunningTest() == false) { HttpResponse GeoCodeResponse = GeoCodeHttp.send(GeoCodeRequest); CurrentGeoCodeResult = ParseGeoCodeJSONReponse(GeoCodeResponse.getBody()); } else { String GeoCodeResponse = '{"Point": { "coordinates": [ -86.8142376, 33.5195411, 0 ] } } '; CurrentGeoCodeResult = ParseGeoCodeJSONReponse(GeoCodeResponse); } // Istest == false if (CurrentGeoCodeResult != null) { if (CurrentGeoCodeResult.Latitude != '' && CurrentGeoCodeResult.Longitude != '') { AccountToGeoCode.BillingLat__c = CurrentGeoCodeResult.Latitude; AccountToGeoCode.BillingLong__c = CurrentGeoCodeResult.Longitude; AccountToGeoCode.GeocodeLastUpdated__c = System.Now(); } // check for null coords } else { system.debug('CurrentGeoCodeResult was null! Skipping this address due to bad JSON parse or response!'); } // update the date even if it failed to prevent reupdating and looping over bad records during inital caching // and to give new records priority over reruns AccountToGeoCode.GeocodeLastUpdated__c = System.Now(); } // GeocodeAddress private static GeoCodePoint ParseGeoCodeJSONReponse(String ResponseBody) { GeoCodePoint ReturnGeoCodeResult = null; JSONParser parser = JSON.createParser(ResponseBody); while (parser.nextToken() != null) { if ((parser.getCurrentToken() == JSONToken.FIELD_NAME) && (parser.getText() == 'coordinates')) { ReturnGeoCodeResult = new GeoCodePoint(); parser.nextToken(); parser.nextToken(); ReturnGeoCodeResult.Longitude = parser.getText(); system.debug('Longitude: ' + ReturnGeoCodeResult.Longitude); parser.nextToken(); ReturnGeoCodeResult.Latitude = parser.getText(); system.debug('Latitude: ' + ReturnGeoCodeResult.Latitude); return ReturnGeoCodeResult; } // if point } // while (parser.nextToken() != null) return ReturnGeoCodeResult; } // GeoCodeResult private static boolean PopulateAccountList() { boolean success = false; // first we look for accounts that haven't been geocoded // 2500 is the max daily limit we can process on the free google service // but 10 is the max number of call outs per execute on SFDC as of 3/22/12 // Notice how entries without city state combinations or postal codes are filtered up front for faster processing AccountsToGeoCode = [ SELECT Id, BillingState, BillingCity, BillingStreet, BillingPostalCode, BillingLat__c, BillingLong__c, LastModifiedDate, GeocodeLastUpdated__c, GeocodeNeedsUpdated__c FROM Account WHERE GeocodeLastUpdated__c = null AND ( (BillingCity != null AND BillingState != null) OR (BillingPostalCode!=null) ) LIMIT 10 ]; if (AccountsToGeoCode.size()==0) { // Next look for geocodes that are out of date AccountsToGeoCode = [ SELECT Id, BillingState, BillingCity, BillingStreet, BillingPostalCode, BillingLat__c, BillingLong__c, LastModifiedDate, GeocodeLastUpdated__c, GeocodeNeedsUpdated__c FROM Account WHERE GeocodeNeedsUpdated__c = 'True' LIMIT 10 ]; } // check for size = 0 if (AccountsToGeoCode.size()>0) { success = true; } // check for size > 0 return success; } // PopulateAccountList public class GeoCodePoint { String Longitude {get;set;} String Latitude {get;set;} } // GeoCodePoint class static testMethod void TestGoogleGeoCodeUpdater() { // create an account Account MyAccount = new Account(Name='Test GeoCode Account'); MyAccount.BillingStreet = '251 South Donahue Drive'; MyAccount.BillingCity = 'Auburn'; MyAccount.BillingState = 'AL'; insert MyAccount; GoogleGeoCodeUpdater.ProcessUpdates(); } } // end GoogleGeoCodeUpdater class Here is a wrapper class used for scheduling the geocoding. During initial caching, I recommend you schedule this 10 times per hour as you get 10 callouts per run. This would give you a maximum effective rate of 2400 per day. Once the initial caching is done however, you probably want to drop this down to once or twice an hour depending on how often new accounts get added. This way you won't be using all of your allowed @future methods and/or allowed scheduled apex jobs. global class ScheduleGoogleGeoCodeUpdater implements Schedulable { // Run the job every day at 1 am public static String CRON_EXP = '0 0 1 * * ?'; global void execute(SchedulableContext ctx) { GoogleGeoCodeUpdater.ProcessUpdates(); } // execute static testmethod void TestScheduleGoogleGeoCodeUpdater() { Test.startTest(); // Schedule the test job String jobId = System.schedule('TestScheduleGoogleGeoCodeUpdater', ScheduleGoogleGeoCodeUpdater.CRON_EXP, new ScheduleGoogleGeoCodeUpdater()); // Get the information from the CronTrigger API object CronTrigger ct = [SELECT id, CronExpression, TimesTriggered, NextFireTime FROM CronTrigger WHERE id = :jobId]; // Verify the expressions are the same System.assertEquals(ScheduleGoogleGeoCodeUpdater.CRON_EXP, ct.CronExpression); // Verify the job has not run System.assertEquals(0, ct.TimesTriggered); Test.stopTest(); } // test method } // class ScheduleGoogleGeoCodeUpdater 

Per the documentation, you can then use execute anonymous to to schedule the class to run on forever.  For example:

 ScheduleGoogleGeoCodeUpdater  g = new ScheduleGoogleGeoCodeUpdater (); String sch = '0 0 * * * ?'; system.schedule('GoogleGeoCodeUpdater at top of the hour', sch, g);

Sunday, March 18, 2012

The case of the missing zero's - Part 2

EDIT: I now recommend the Jitterbit Data Loader which can be found here, over the salesforce data loader. At the time of this edit, Jitterbit had a free version.

In part 1, we looked at where and why the zero's are missing in salesforce's database.

Now, we will turn our attention towards how to put them back by creating a modified version of the data loader.

Please note that this will use regular expressions to change any field ending in .0 to .00 (.00, .000, etc will be left alone).  This is not the idea method but it works.

In a perfect world, we would call the DescribeSObject method in the metadata API and look at the DescribeSObjectResult's fieldtype and scale fields to only fix this problem on given field types.  I have been hoping that salesforce would update the dataloader to do so but as of version 23 - no such luck.  Maybe I will get bored one day and do it myself.

In the mean time, notice how are modifying an existing function.   Sales force is doing a similar thing (i.e. not using the meta data API but reformatting a field) in this same method for dates.

Since this is a rather technical post lets go over some prerequisite things you will need:
  1. A working Eclipse Java environment.  If you have the IDE installed, then you should be good to go.
  2. Install ANT to your machine (this requires the JDK not to be confused with the JRE).
  3. Install Perl to your machine.
  4. Download the latest copy of the ApexDataLoader source code and extract it to C:\apexdataloader\
Once you have these installed, you are now ready to use ANT to generate lib\partnerwsdl.jar

You can generate this file by issuing running ant.bat in C:\apexdataloader\build\

Once completed, it should create C:\apexdataloader\lib\partnerwsdl.jar.

Now you can create a new java project in eclipse, import the filesystem into the project, fix your JDK and partnerwsdl.jar references in the java build window by locating these on the file system..

Once you have the project loaded:
  1. Open the PartnerQueryVisitor.Java file
  2. Change import java.util.regex.pattern; to read import java.util.regex.*;
  3.  Locate the convertFieldValuefunction and append this code to the bottom (before the return line)
            if (fieldVal != null) {
                // create a pattern to use regular expressions
                Pattern ptrnEndsInDotZero = Pattern.compile("\\.[0-9]");
                Pattern ptrnEndsInDotTwoNums = Pattern.compile("\\.[0-9][0-9]");
                // cast our number to a string
                String strDecimalAmount = fieldVal.toString();
                // create a matcher from our pattern and string
                Matcher mtchEndsInDotZero = ptrnEndsInDotZero.matcher(strDecimalAmount);
                Matcher mtchEndsInDotTwoNums = ptrnEndsInDotTwoNums.matcher(strDecimalAmount);
                boolean blnDotZeroSearchResult = mtchEndsInDotZero.find(); 
                boolean blnDotTwoNumsSearchResult = mtchEndsInDotTwoNums.find();
                if (blnDotZeroSearchResult == true && blnDotTwoNumsSearchResult == false) {
                    // Has tenths but not hundredths
                    // found an entry with missing Decimal
                    // add the zero back.
                    fieldVal = fieldVal + "0";
            }   // check for null field 
  4. Use C:\apexdataloader\build\ant.bat to build your jar file in C:\apexdataloader\JAR\
  5.  If your version of java wasn't compatible with the salesforce exe, you can now call the jar file from the command line (or a bat file) like this:
    java.exe -cp ..\MyDataLoader.jar -Dsalesforce.config.dir=..\ConfigDIRGoesHERE com.salesforce.dataloader.process.ProcessRunner
Or you can avoid the hard work and us this jar file which has been modified in the above fashion.

Monday, March 5, 2012

The case of the missing zero's - Part 1

EDIT: I now recommend the Jitterbit Data Loader which can be found here, over the salesforce data loader. At the time of this edit, Jitterbit had a free version.

So is fast but how do they do it?  Of course they are using big iron in their data centers but they have many tricks built into their software to increase speed and/or decrease size.

One of these tricks is to strip 'extra' zeros from your numbers.  This violates data integrity but they put the extra zero's back in the web GUI.

Don't believe me?  Lets walk thru an example.  I have created an object called Item__c and on that object I have created the following fields:
Notice how each field is named for its type and each one is asking for 2 digits after the decimal.

In the web GUI, we enter 1.00 for currency, 2.00 for number and 30.00 for percent.

Everything looks great right?  Now try to export that data.  I dare you.  If you do an SOQL query in explorer you get this:

Savvy explorer users are now saying something along the lines of "explorer is just dropping the insignificant data".   Right you are, so lets look in Eclipse at the same query.
In the database itself, is dropping the 2nd zero after the decimal (when in 16/2 format and ending in double zero).  Salesforce has hidden this by having the web GUI and reports in your browser (not in exports to excel) by reformatting and adding the zero back.

They didn't however fix this in the data loader (as of version 23 anyway).  This can be a major problem if you are wanting to integrate a system with Salesforce using the Apex dataloader and the other system chokes on files that don't match the defined scale setting

In part 2, I will look at how to create your own customized version of the dataloader to put this zero back using regular expressions.

Thursday, March 1, 2012

How to get a list of picklist values enabled for a given record type.

What if you want to product a list of picklist values that are enabled for a certain record type?

Without major development (i.e. changing the WSDL), you can't access the Meta API in apex code and you can't use SOQL to query this kind of information. 
You could always use visualforce and the Ajax Tool kit or write your own program (in say java) to access the API but this is over kill for this kind of task.

There is an easier way to use the force to get this information out of the system. This post assumes you have the Eclipse IDE configured already for your system as I will use the Metadata logic built into it to pull this data.  Be sure to save any uncommitted changes and refresh your project before starting.

In this simple example, we have a custom picklist field called "State__c" and record type called "Western" on the Opportunity object and we want to get the values that are displayed on the screen to the left.

Open eclipse then right click on the root parent object in the packages tree and select properties.  Then expand the child and select project Contents.  Then Click the Add/Remove button.

When the "Choose Metadata Components" box comes up, click the refresh button as seen in the image on the right.  Once the refresh has been completed, go to either objects - standard or objects - custom.  In this example, we are using Opportunity which a standard object and state__c which is a custom field.  

Then select them as pictured on the left. Then hit Ok, then Ok again and finally yes to refresh the project from the server.

Once the refresh has been completed, locate the object from the tree.  By default it will open in the XML editor.  Right click on it and select open with -> text editor.

When the file opens, scroll down and locate the <recordTypes> section that has the <fullName> you are looking for (Western in this case).  Then Locate the <picklistValues> section that has the <picklist> with the field you want (State__c in this case). 

There you will find a <values> section that will contain *just* the picklist values enabled for that record type.   

Now you can copy that section out and use it as you will.  Most likely you will want to remove the XML tags with a mass find/replace to get a simple list.

Debugging Public Sites in your sand box instance

Under Setup->Monitoring->Debug Logs you can configure debug logs for any user.

When click the magnifying glass in the screen shot to the left (in production) and search for site* or *user to locate any public site profile.  If you have recently viewed said public site profile, then it will appear in the recently viewed user list as well.

As of today, you can't do either of those things in your sandbox instance.

If you go to the site and click "Public Access Settings" to view the sites profile, you can see the name of the profile.  You would think you might be able to copy this name into the search box and add the user to the debug logs this way.  That is not the case as the name displayed on that page isn't the actual name of the profile used by this screen.

So how do you add a debug log to the public site?  Type in "Site" or "Site*", hit enter and it will either select the user (if you only have one site). If you have multiple sites defined, you will get an error that says "Multiple items found. Select from drop down or click icon to refine search" with a drop down to select the site.

The moral of the story is that the look up box you bring up by clicking the magnifying glass doesn't have the same behavior in the sand box as in production in that it won't search the public sites profiles.  However using the search box on the main screen will.

I share this in case someone wants to debug code in the sandbox instead of their production system.