Views:

Overview

In this business scenario we want to update the longitude and latitude of an Account whenever there is a change made to the Account's address.

We can do this by using North52's CallRestAPI function along with the Google Geocoding API service.

Note you will need an API key from a Google Maps Platform account that has the Geocoding API enabled. Click here for more details.

North52 BPA Solution

The North52 BPA solution works like this:

  • A Formula is set up that triggers whenever one of the Account's address fields are changed
    • This formula will make an API call to the Geocoding API service using the address values and an xCache record that stores your Geocoding API key
    • The formula will then interpret the response from the Geocoding API
      • If the response contains a longitude and latitude, the formula will then update the longitude and latitude of the Account
      • If the response does not contain a longitude and latitude, the formula will then update the description field to Longitude and Latitude not found

Set up xCache and Formula

xCache

To securely store the API ID we use an xCache record. Only System Administrators or specifically authorized users will be able to access this value.

  • Navigate to Settings > North52 BPA > N52 xCache
  • Create a new xCache record
    • Enter Geocoding for the Category
    • Add AppID to the Base Key so that the full BaseKey name is Geocoding_AppID
    • In the Value Information tab enter your Google Maps Platform API ID into the Value (Secured) field
  • Click Save

Formula

  • Navigate to Settings > North52 BPA > N52 Formula
  • Create a new formula, setting the following values in the Formula Guide:
    • Set Entity to Account
    • Set Formula Type to Save - Perform Action
    • Set Editor to Classic
  • Click Create
  • Change the Name of the formula to Find longitude and latitude using Googles Geocoding API
  • Expand Source & Target
    • Under Source Property, select the following fields. Note you multi-select fields by holding down Ctrl when clicking. 
      • Address 1 : City , Address 1 :  Country/Region , Address 1 : State/Province, Address 1 : County, Address 1 : Street 1, Address 1 : Street 2, Address 1 : Street 3
  • Copy the following formula into the editor:
    SmartFlow(      
        CallRestAPI( 
          SetRequestBaseURL('https://maps.googleapis.com/maps/api/geocode/'),
          SetRequestResource('json'),
          SetRequestDetails('GET'),
          SetRequestHeaders(),
          SetRequestParams(
            'address',
            [account.address1_line1]+ ' ' 
            +[account.address1_line2] + ' ' 
            +[account.address1_line3] + ' ' 
            +[account.address1_city] + ' ' 
            +[account.address1_county] + ' ' 
            +[account.address1_stateorprovince] + ' ' 
            +[account.address1_country], 
            'key',xCacheGetGlobal('Geocoding_AppID') ),  
          SetRequestAuthenticationNone(),
          SetRequestFiles(), 
          SetRequestExpected('OK'),  
          SetRequestActionPass(GetVar('responsecontent')),
          SetRequestActionFail(GetVar('responsecontent'))
      ),
    
      If(GetVarJsonValue('status') = 'REQUEST_DENIED' ,      
        UpdateRecord('account',                                               
          [account.accountid],             
          SetAttribute('description', 'Error : Longitude and Latitude not found using address' )
        ) ,   
        UpdateRecord('account',                                               
          [account.accountid],             
          SetAttribute('address1_latitude', GetVarJsonValue('results{0}.geometry.location.lat')),
          SetAttribute('address1_longitude', GetVarJsonValue('results{0}.geometry.location.lng'))
        )  
      )
    )
    
  • Click Save

Testing

To test this formula, simply create or update an Account's address fields.

Note you may need to add the fields longitude and latitude to the Account form.