Using Variables In Dynamic SOQL - Uncovering

Monday, September 10, 2012

In Earlier post "Using Variables In Dynamic SOQL",  I wrote about how to use variables in a Dynamic SOQL. Well after playing with Dynamic SOQL, I found few interesting thing like:

·         You CAN directly use primitive data types in SOQL.

So what does this means?

This means you can directly use any Integer, String , Date, Datetime,Double, Id variable along with Collection of this variable within the query.

For Example: 

a) You are allowed to do this

//initialize the Datetime with current time

DateTime now = System.now();

//query accounts by merging the variable name inside the query string

List<Account> accountList = Database.query('SELECT Id FROM Account WHERE CreatedDate =:now');

b)You can also include LISTs(Collections) in your queries

List<String> accNameList = new List<String>{'Acc1','Acc2'}

//query accounts by merging the variable name inside the query string

List<Account> accountList = Database.query('SELECT Id FROM Account WHERE Name IN:accNameList');

·         You CANNOT use complex types in a Dynamic SOQL directly.

This means that you cannot use any Sobject, Apex Class or Any other user defined data type inside the Dynamic Query. In short you cannot use a dot (".") operator to specify a field value in a Dynamic query.

For Example :

                a)  Using an Sobject inside the query String will not work

           //initialize a Account with a Name value for demo

Account acc = new Account(Name='MyAccount');

//query accounts by merging the variable name inside the query string

//This will not work

List<Account> accountList = Database.query('SELECT Id FROM Account WHERE Name =:acc.Name');

//But You can always make the above work by writing the code as.

           //initialize a Account with a Name value for demo

      Account acc = new Account(Name='MyAccount');

      String accountName = acc.Name

      //query accounts by merging the variable name inside the query string

      List<Account> accountList = Database.query('SELECT Id FROM Account WHERE                       
      Name =: accountName  ');

Hope this helps!.

Integrating Google Maps With Salesforce

Saturday, September 8, 2012

10-Sep-2013 : Have a look at my MapMyObject Component, A very flexible Google Map Mapping component.

This is one of the most frequently asked question "How to integrate Salesforce with Google Maps?"
What if you want to show the address of a Say "Account" in the account detail page?
Well the solution is create a inline visualforce page for account and call the google geocoding service to geocode the address ,use that location to mark a point on the map.

Sounds difficult right?
Well in reality its very easy. Just some fancy Java script calls and its done.
Just see the below visualforce page which pulls the Billing Address from the account and displays the same, this page can be included in the Account detail page layout.

Features

• Shows the Billing Address of the Account in the map
• On click of the marker a info window is shown with the formatted address.

 <apex:page standardController="Account">  
   <!-- Import Necessary Jquery js File and StyleSheets-->  
   <apex:includeScript value="https://maps.googleapis.com/maps/api/js?sensor=false"/>  
   <apex:includeScript value="{!URLFOR($Resource.jQuery, 'js/jquery-1.6.2.min.js')}"/>  
   <apex:includeScript value="{!URLFOR($Resource.jQuery, 'js/jquery-ui-1.8.16.custom.min.js')}"/>  
   <apex:includeScript value="{!URLFOR($Resource.jqPlugin, '/jquery.blockUI.js')}"/>  
   <apex:stylesheet value="{!URLFOR($Resource.jQuery, 'css/ui-lightness/jquery-ui-1.8.16.custom.css')}"/>  
   <script>  
      var map,geocoder,infowindow;  
      var latLngs = [];  
      $j = jQuery.noConflict();   
      $j(document).ready(function(){  
        initialize();  
      });  
      function initialize() {  
       geocoder = new google.maps.Geocoder();  
       //initial cordinates for map init  
       var latlng = new google.maps.LatLng(37.09024, -95.712891);  
       var myOptions = {  
         zoom: 4,  
         center: latlng,  
         mapTypeId: google.maps.MapTypeId.ROADMAP  
       };  
       //load the map  
       map = new google.maps.Map($j('#map')[0], myOptions);  
       codeAddress();  
      }  
      /*This function codes the address using the Billing Address in the Acoount*/  
      function codeAddress(){  
        //prepare a string for geocoding  
        var address = '{!Account.BillingStreet},{!Account.BillingCity},{!Account.BillingCountry},{!Account.BillingPostalCode}';  
        console.log(address);  
        //geocode the address  
        geocoder.geocode( { 'address': address }, function(results, status) {  
          //if it is a success  
          if (status == google.maps.GeocoderStatus.OK) {  
            var location = results[0].geometry.location;  
            var marker=addMarker(location );  
            //attach info window to the marker  
            attachInfoWindow(marker,results[0]);  
          }  
          else {  
            alert(status);  
          }  
        });   
      }  
      /*  
      *This method adds a marker to the provided location  
      **/  
      function addMarker(location) {  
       marker = new google.maps.Marker({  
           position: location,  
           map: map  
         });  
         //set the bounds and initial zoom  
       var latlngbounds = new google.maps.LatLngBounds();  
       latlngbounds.extend(marker.getPosition());  
       map.fitBounds(latlngbounds);  
       map.setZoom(14);  
       return marker;  
      }  
     //this function shows the address of a marker when it is clicked  
     function attachInfoWindow(marker,address){  
        google.maps.event.addListener(marker, 'click', function() {  
          if(infowindow!=null)  
           {  
             infowindow.close();  
           }  
         //HTML formated string that is used to dispaly info window over the map markers currently showing the formated address  
         var contentString = '<div class=" ui-state-active ui-corner-top" style="font-size: 1em; padding: 5px;">Address</div>'  
                   +'<div class="ui-widget-content ui-corner-bottom" style="font-size: .9em; padding: 15px;">'+address.formatted_address+'</div>';  
         infowindow = new google.maps.InfoWindow({  
           content: contentString  
         });  
          infowindow.open(map,marker);  
        });  
     }  
   </script>  
   <style>  
     #map {  
       width:100%;  
       height:200px;   
       margin-left:1.5%;  
     }  
   </style>  
   <div id="map" class="ui-widget-content ui-corner-all ui-state-default"></div>  
 </apex:page>  

How the page looks like?

Page showing the Location of Account

The info window showing the address

Package/Installation

You can use this package to install the same in your org 
https://login.salesforce.com/packaging/installPackage.apexp?p0=04t90000000M5aT

The above package contains a page called LocateAccount that can be used inline in the Account detail page to display the location or you can pass the Id of account to the page like this /apex/LocateAccount?id=<ACCOUNT ID>".