function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
JessBJessB 

Have Excel list of zips&lat/lngs; want to search for Accounts within "x" miles radius of zipcode

I want to be able to perform a search to find all Accounts within a, say, 50 mile radius of a specified zipcode.

 

I have an excel sheet that has all zipcodes, and the respective lat/lng degrees of the "center" of the zipcode. Also, the excel list includes the city, state, and state abbreviation.

 

We also have Find Nearby installed, but I am unfamiliar with this, and if it can search by a radius, and produce a LIST, not a map, of the results.

 

It would be great if we could run a report or something (custom object?) to produce this list. Any ideas? 

 

I'm not sure if this would be an Apex code or not.

Saikishore Reddy AengareddySaikishore Reddy Aengareddy

JessBess-

 

I have a solution specific to your problem... I will send out the complete listing of what needs to be done(with code and all steps) sometime tomorrow. 

 

It involves Google API, custom object, Apex code(Class and a trigger) data export/import. 

 

Thanks, Sam

JessBJessB
Sam, You are so considerate to help me out, I appreciate it! Thank you, -Jessica Besenfelder Jessica Besenfelder Associate Business Analyst SalesForce.com Administrator Ulthera, Inc.2150 S. Country Club Drive Suite 21 Mesa, Arizona 85210 USA Email: j.besenfelder@ulthera.com Phone: (480) 619-4069 Website: www.ultherapy.com [cid:image001.jpg@01CD6A42.5D5B4220] [cid:image002.jpg@01CD6A42.5D5B4220] [cid:image003.jpg@01CD6A42.5D5B4220] [cid:image004.png@01CD6A42.5D5B4220] [cid:image005.jpg@01CD6A42.5D5B4220]
Saikishore Reddy AengareddySaikishore Reddy Aengareddy
It involves some excel work and data loads...After creating the below object you will need to do something like... say for example you have 2 zipcodes in your spread sheets and 5 accounts in your org.
1. Extract all the Account Id's (all 5)
2. You will need to make another file in such a way that everyzipcode should be associated to every account...  eg: 2 zip codes...(63121 and 94118) and 5 accounts a1,a2,a3,a4,a5 then your file should contain
    Zipcode AccountId
    63121 a1.Id
    63121 a2.Id
    63121 a3.Id
    63121 a4.Id
    63121 a5.Id
    94118   a1.Id
    94118            a2.Id
    ..... ...... till a5.Id
Then Insert into below object.... In this way we can calculate distance between each zipcode and everyaccount... then you can runyour report by putting filter on distance and summarized by zipcode to find all the accounts in particular radius. 
3. After performing all the steps below and inserting the data as mentioned above.. extract Id's of the distance object and just make a dummy update on the above object on Id's.. and make sure you keep the batch size of maximum 10... and if it doesnt work then make the size as 1... this way it will populate the Distance field on the below object with the distance between zipcode and the associated account.
Let me know if you have any questions.
Step 1: Create a new Custom object called "Distance" (Api name Distance__c) and make Name field as Auto number...
Fields in Distance Object:
Field Name API Name Data Type          
AccountAccount__CLookup(Account)
AccountAddressAccount_Address__c Formula(Text) (Account__r.BillingStreet +" "+Account__r.BillingCity +" "+Account__r.BillingState +" "+ Account__r.BillingCountry +" "+ Account__r.BillingPostalCode)
CompletedCompleted__ccheckbox
DistanceDistance__cNumber
ZipcodeZipcode__cText(Max size)
Step 2: Setup->Security Controls-> Remote Site Settings Click new
Remote Site Name  : Google
Remote Site URL    : https://maps.googleapis.com
Active                    : True
Step 3: Create Apex Trigger and Apex Class on Custom object
Apex Class: 
Public class GoogleAPI {
  public static boolean isApexTest = false;  
@future (callout=true)
  public static void find(Id Id, string origin,String destination) {
   
   Integer distance  = 0;
     Distance__c d = [select distance__c from Distance__c where Id=:Id  limit 1];
     String start1= EncodingUtil.urlEncode(origin,'UTF-8'); 
     String destination1= EncodingUtil.urlEncode(destination,'UTF-8');
           
     HttpRequest req = new HttpRequest();
   
     req.setTimeout(12000);
     
     req.setMethod('GET');
        
     Http h = new HTTP();
     
     HttpResponse res;
     // Send the request, and return a response   
     try{
        
         system.debug('Request>>>>> '+req);
         if(!isApexTest)
            res = h.send(req);
         else
             res = new HttpResponse();   
         
         if(res.getStatusCode() == 200)
         {
          system.debug('*******Success********');
    
          Dom.Document doc = res.getBodyDocument();
       
          Dom.XMLNode response = doc.getRootElement();
          
          dom.XmlNode rowNode = response.getChildElement('row',response.getNamespace());
          
           for (Dom.XMLNode c: rowNode.getChildElements()) {
               
               if ('element'.equalsIgnoreCase(c.getName())){
                   
                   for(Dom.XMLNode c1: c.getchildElements()){
                       
                       if ('distance'.equalsIgnoreCase(c1.getName())){
                       
                           for(Dom.XMLNode c2: c1.getchildElements()){
                       
                               if('value'.equalsIgnoreCase(c2.getName())){
                       
                                   system.debug('Distance between two places@@@@>>>'+math.ceil(integer.valueOf(c2.getText())*0.000621371));
                                   d.distance__c = (math.ceil(integer.valueOf(c2.getText())*0.000621371)).intValue();
                                   d.completed__c = true;
                                   update d;
                               }
                           }
                           
                       }
                   }
                   
               }
               
           }
                       
          }else{
    //do something...
           System.debug('***Failed to make a callout***');   
         }
   
       }Catch(System.CalloutException e){
        //Failed to make a callout   
        System.debug('***Failed to make a callout***');
   
       }   
    }
  }
Apex Trigger: 
trigger distance on Distance__c (after update) {
    for(Distance__c d : trigger.new){
        if(!d.completed__c)
            GoogleAPI.find(d.Id, d.zipCode__c,d.Account_Address__c );
           
    }
}

 

Saikishore Reddy AengareddySaikishore Reddy Aengareddy
It involves some excel work and data loads...After creating the below object you will need to do something like... say for example you have 2 zipcodes in your spread sheets and 5 accounts in your org.
1. Extract all the Account Id's (all 5)
2. You will need to make another file in such a way that everyzipcode should be associated to every account...  eg: 2 zip codes...(63121 and 94118) and 5 accounts a1,a2,a3,a4,a5 then your file should contain
    Zipcode		AccountId
    63121		a1.Id
    63121		a2.Id
    63121		a3.Id
    63121 		a4.Id
    63121		a5.Id
    94118   		a1.Id
    94118	            a2.Id
    .....			...... till a5.Id
Then Insert into below object.... In this way we can calculate distance between each zipcode and everyaccount... then you can runyour report by putting filter on distance and summarized by zipcode to find all the accounts in particular radius. 
3. After performing all the steps below and inserting the data as mentioned above.. extract Id's of the distance object above and just make a dummy update on the above object on Id's.. and make sure you keep the batch size of maximum 10... and if it doesnt work then make the size as 1... this way it will populate the Distance field on the below object with the distance between zipcode and the associated account.

Let me know if you have any questions.

Step 1: Create a new Custom object called "Distance" (Api name Distance__c) and make Name field as Auto number...

Fields in Distance Object:

Field Name		API Name		Data Type           	
Account		Account__C		Lookup(Account)
Account Address	Account_Address__c	Formula(Text) (Account__r.BillingStreet +" "+Account__r.BillingCity +" "+Account__r.BillingState +" "+ Account__r.BillingCountry +" "+ Account__r.BillingPostalCode)
Completed		Completed__c		checkbox
Distance		Distance__c		Number
Zipcode			Zipcode__c		Text(Max size)

Step 2: Setup->Security Controls-> Remote Site Settings Click new
	Remote Site Name  : Google
	Remote Site URL    : https://maps.googleapis.com
	Active                    : True

Step 3: Create Apex Trigger and Apex Class on Custom object
Apex Class: 

Public class GoogleAPI {
  public static boolean isApexTest = false;  
@future (callout=true)
  public static void find(Id Id, string origin,String destination) {
   
   Integer distance  = 0;
     Distance__c d = [select distance__c from Distance__c where Id=:Id  limit 1];
     String start1= EncodingUtil.urlEncode(origin,'UTF-8'); 
     String destination1= EncodingUtil.urlEncode(destination,'UTF-8');
           
     //http://maps.googleapis.com/maps/api/distancematrix/xml?origins=Vancouver+BC&destinations=San+Francisco&sensor=false

     HttpRequest req = new HttpRequest();
   
     req.setTimeout(12000);
     
     req.setEndpoint('https://maps.googleapis.com/maps/api/distancematrix/xml?origins='+start1+'&destinations='+destination1+'&sensor=false');

     req.setMethod('GET');
        
     Http h = new HTTP();
     
     HttpResponse res;

     // Send the request, and return a response   
     try{
        
         system.debug('Request>>>>> '+req);
         if(!isApexTest)
            res = h.send(req);
         else
             res = new HttpResponse();   
         
         if(res.getStatusCode() == 200)
         {
          system.debug('*******Success********');
    
          Dom.Document doc = res.getBodyDocument();
       
          Dom.XMLNode response = doc.getRootElement();
          
          dom.XmlNode rowNode = response.getChildElement('row',response.getNamespace());
          
           for (Dom.XMLNode c: rowNode.getChildElements()) {
               
               if ('element'.equalsIgnoreCase(c.getName())){
                   
                   for(Dom.XMLNode c1: c.getchildElements()){
                       
                       if ('distance'.equalsIgnoreCase(c1.getName())){
                       
                           for(Dom.XMLNode c2: c1.getchildElements()){
                       
                               if('value'.equalsIgnoreCase(c2.getName())){
                       
                                   system.debug('Distance between two places@@@@>>>'+math.ceil(integer.valueOf(c2.getText())*0.000621371));
                                   d.distance__c = (math.ceil(integer.valueOf(c2.getText())*0.000621371)).intValue();
                                   d.completed__c = true;
                                   update d;
                               }
                           }
                           
                       }
                   }
                   
               }
               
           }
                       
          }else{
   	//do something...
           System.debug('***Failed to make a callout***');   
         }
   
       }Catch(System.CalloutException e){
        //Failed to make a callout   
        System.debug('***Failed to make a callout***');
   
       }   
    }
  }

Apex Trigger: 

trigger distance on Distance__c (after update) {
    for(Distance__c d : trigger.new){
        if(!d.completed__c)
            GoogleAPI.find(d.Id, d.zipCode__c,d.Account_Address__c );
           
    }
}

 

 

Saikishore Reddy AengareddySaikishore Reddy Aengareddy

req.setEndpoint('https://maps.googleapis.com/maps/api/distancematrix/xml?origins='+start1+

'&destinations='+destination1+'&sensor=false');

 

its not clear in the above code..