+ Start a Discussion
Eric BlaxtonEric Blaxton 

Reporting on GeoLocation

Hello and thanks in advance.

I want to report on Accounts within certain miles of a big city.  Let's say Accounts within 50 miles of Dallas.  What i did was set up geolocation fields, then i was able to run SOQL queries on Workbench. The problem now is being able to export to Excel or some other format.  

Any ideas or suggestions?
Shawn Reichner 29Shawn Reichner 29
Seems like a stretch but I achieved this type of scenario by doing the following. 

Do a google search for US Zip codes where the Longitude and Latitude is also listed. 
Get those into an CSV file ready for upload into Salesforce. 
Create a Custom Zip Code object in Salesforce and add the needed fields such as Zip Code name, Long and Lat and Zip code and lastly Large City Checkbox field. 
Create a custom field on the Account Object (Name it whatever you wish) I called mine Distance from Large City
Create another custom text field (Again whatever namign you wish) I called mine City
Create one last field for Account which is a lookup field (Again name as you wish) I named mine Closest City Zip Code
Create a Apex trigger that when an Account is Saved or if the Billing Zip code is updated the trigger fires to SOQL the Zip code custom object and finds the closest "Large City" zip code to your Account's billing Zip code and returns that Zip Code ID into the City Zip code field and also fills in the other two new fields with its appropriate data (City Name) and also you trigger will calculate the difference between the Billing zip and the chosen Big City Zip code record.  This will populate the distance from Large city field which then you can report on dynamically. 

I know a very big process to achieve what you are lookign for and it is just what I did as then I could empower my users to run thier own reports and since zip codes change very seldomly this solution would stand th etest of time.   The trigger is the hardest part. 

Good luck,

Eric BlaxtonEric Blaxton

Thanks for the input.