This post will help you to create an address formula field which displays a clickable address link to Google Map. Without further ado, let's see the formula!

We are using the fields on Account, namely the following: BillingStreet, BillingState, BillingCity, BillingPostalCode, and BillingCountry.

NOTE: the address format is for the addresses in United States. You can modify however you want to suit your need.

  1. Navigate to the object, create a new field with Formula as type.
    formula-field-1
  2. Select Text for the Formula Return Type.
    formula-field-2
  3. Paste the following code into the formula field:
    formula-field-3

Here's the code for your reference:

IF (
   OR( 
      ISBLANK(BillingStreet), 
      ISBLANK(BillingCity), 
      ISBLANK(BillingState), 
      ISBLANK(BillingPostalCode),
      ISBLANK(BillingCountry)
   ), 
   'Incomplete Address', 
   SUBSTITUTE( 
      HYPERLINK(
         "https://www.google.com/maps/place/" & 
         SUBSTITUTE(
            SUBSTITUTE(
               SUBSTITUTE(
                  SUBSTITUTE( 
                     SUBSTITUTE(  
                        BillingStreet, 
                        SUBSTITUTE($Label.linebreak, "-", "") ,"%20"
                     ) & "%20" &  
                     BillingCity & "%20" & 
                     BillingState  & "%20" & 
                     BillingPostalCode , 
                     "#", "%23"
                  ), " ", "%20"
               ), "&", "%26"
            ), "/", "%2F"
         ), 
         SUBSTITUTE( 
            BillingStreet,
            SUBSTITUTE($Label.linebreak, "-", ""), BR() 
         ) & BR() & 
         BillingCity & ", " & 
         BillingState & " " & 
         BillingPostalCode & BR() & 
         BillingCountry 
      ) , "", ""
   )
)

Essentially, what the formula does is that it makes sure that all the fields are present, if not, it will display text "Incomplete Address".

Next, it uses HYPERLINK with the link address set to Google Map URL. You will notice that there's lot of SUBSTITUTE that replaces some characters into HTML entities so that the newly formed URL link can be properly read by Google Map.

Lastly, we need to make sure that the street field (textarea) does not mess up with the formula by doing SUBSTITUTE of line break with %20 (whitespace) as below:

 SUBSTITUTE(  
    BillingStreet, 
    SUBSTITUTE($Label.linebreak, "-", "") ,"%20"
 )

If you do not have the line break substitution above and you have multiple lines in your textarea field, chances are you will be getting messed up formula field like this:
messed-up-address-formula

If you are following along, you should be seeing output like this:

correct-address-formula-field

Hope this post helps! See you next time!

Post was published on , last updated on .

Like the content? Support the author by paypal.me!