Solution: Route NetSuite Leads based on the Shortest Distance

This article is relevant if you are looking for a way to distribute leads based on distance or you seek to know the distance between two geographical points.


One our clients runs NetSuite in a truly distributed fashion. They have over 100 North America operations that manage on-the-ground heavy equipment. Recently, they sought to leverage internet based lead capture mechanisms which effectively transcend their geographical operational footprint.

During the design of the business practice and requirement definition for ways to collect leads and prospects through custom SuiteScript RESTLets (our often preferred way to perform http real-time integration), we realized that getting the prospect information to the right location manager was important.

Instead of using State or Zip code mapping, we suggested that we could calculate the shortest distance between the lead’s physical address and all of our locations. With this information in hand, we could then use NetSuite’s built-in lead assignment technology to route it to the right manager for follow up.

NetSuite Lead Routing Solution Components

Here are the key things we needed to solve the problem:

  1. NetSuite Location Long / Lat Coordinates: We customized NetSuite’s built-in Location record to add both the latitude and longitude coordinates. See image.
  2. Lead Long / Lat Coordinates to Google Maps API: Our leads were required to pass in their physical street address via our RESTLet. With this information, we query Google Maps to get back the exact Long / Lat references.
  3. Saved Search Trigonometry: We query our location list using a trigonometry formula to produce the distance between the Lead and our NetSuite locations. We then return the location record with the Minimum value representing the shortest distance.

Sample Google Maps API SuiteScript Code

I have modified the actual code to make it easier to understand so you can see the essence of the solution. First, here is the code that allows you to get the long / lat from Google Maps (remember, code ethically and ensure you respect licensing requirements):

function getGeoCode(google_api_key, add1, city, state, zip)	{
    //prepare the address to be passed on a URL changing spaces to '+';
	//assumes you have a Google API Key giving you authorization to make the call

	add1 = add1.split(' ').join('+');
    city = city.split(' ').join('+');
    var address = add1.trim() + ',+' + city.trim() + ',+' + state.trim() + '+' + zip;
    var url = '' + address + '&key='+google_api_key;

	nlapiLogExecution('DEBUG', 'Google Geocode', url);

	//here, we wrapped netSuite's nlapiRequestURL to make outbound https request
	var obj = callWebService(url);

    //inspect the result and return if good.
	if (obj){
        if (obj.results){
            if (obj.results.length > 0){
                if (obj.results[0].geometry){
                    if (obj.results[0].geometry.location){
                        return obj.results[0].geometry.location;

	//if we get here, we failed. Return an object that contains bogus long / lat
    obj = {}; = 0;
    obj.lng = 0;
    return obj;

NetSuite SuiteScript Saved Search to Perform Distance Query

I urge you to read the article posted here ( to learn how to use Oracle PLSQL to calculate the distance between two points. This is the basic SQL request:

                                     Lon1 IN NUMBER,
                                     Lat2 IN NUMBER,
                                     Lon2 IN NUMBER,
                                     Radius IN NUMBER DEFAULT 3963) RETURN NUMBER IS
-- Convert degrees to radians
DegToRad NUMBER := 57.29577951;

  RETURN(NVL(Radius,0) * ACOS((sin(NVL(Lat1,0) / DegToRad) * SIN(NVL(Lat2,0) / DegToRad)) +
        (COS(NVL(Lat1,0) / DegToRad) * COS(NVL(Lat2,0) / DegToRad) *
         COS(NVL(Lon2,0) / DegToRad - NVL(Lon1,0)/ DegToRad))));

Now the trick is to convert this to something we can call with NetSuite’s saved search. I must thank Sean, one of our Senior Consultants, for coming up with this innovative solution. He effectively produced the underlying math to come up with the function mimicking the PLSQL offering above.

function findClosestLocation(lat1, lon1) {
        //lat1 and lon1 represent the Lead's location

	//get reference to the custom column names in our location records
	var lat2 = '{custrecord_location_latitude}';
        var lon2 = '{custrecord_location_longitude}';

	//here is the trig formula; make that a custom formula field in our saved search
	var formula = '3963.1676*ACOS(0.5*((1.0+(COS(('+lon1+'*3.14159265359/180)-('+lon2+'*3.14159265359/180))))

	//craft the Saved Search with the formula using a minimum aggregate
        var cols = new Array();
        cols[0] = new nlobjSearchColumn('formulanumeric', null, 'min').setFormula(formula);  //

        //perform the search; we expect only one location record; the shortest distance
        var results = nlapiSearchRecord('location', null, null, cols);
        if (results){
		return results[0].getId();  // return the location internal id

Invent with NetSuite’s Platform

The solution above is representative of some of the creative things you do with the NetSuite platform to solve meaningful business challenges. If you are ready to get more out of your NetSuite investment, let’s have a conversation.

Be Sociable, Share!

Marty Zigman

Holding all three official certifications, Marty is Southern California's NetSuite expert and leads a team of senior professionals at Prolecto Resources, Inc. He is a former Deloitte & Touche CPA and has held CTO roles. For over 25 years, Marty has produced leadership in ERP, CRM and eCommerce business systems. Contact Marty to set up a conversation.

More Posts - Website - Twitter - Facebook - LinkedIn - Google Plus - YouTube

| Tags: , , | Category: CRM, NetSuite, Technical | 4 Comments


  1. Brian
    Posted March 6, 2018 at 1:36 pm | Permalink

    We use the Google Maps site to manually pull the long/lat today and I’m going to look at this code to automate that process. What i came here looking for. Is once i have all my customer Long/Lat data in the customer record i want to be able to display these in a google map. I have this working (using a static set of data in my html code) but What i can’t seem to figure out is how in place of using static code can I Call via saved search to get basic closed won customer locations. Seems like i only need 3 fields, customer name/ID and the Long/Lat fields really to get the basics. Just can’t seem to get anything to work. Nothing special about the customer record other than closed won as the status so it’s just figuring out how to execute within the page. I should mention i’m using a suitlet and portlet to load an HTML into the dashboard. This works fine. have a sizing issue to figure out but really just trying now to figure out how to pull a structured saved search of customers and locations.

  2. Posted March 10, 2018 at 10:40 am | Permalink

    Hi Brian,

    Sounds like basic SuiteScript challenges working with records and data. If you want, we can have one of our technical consultants help you. Just send me a message here.


  3. Dimitri Vasilev
    Posted July 3, 2018 at 11:24 am | Permalink

    Hi Marty,

    Thank you for your post. It is exactly what I was looking for. I do have one question: what is included in CallWebService()? When I use http.get(), I receive an error – (SSL_INVALID_URL: The URL must be a fully qualified HTTP URL). I know that the URL I have is correct and I feel this error has to do with CORS requests. So basically, how does CallWebService() work?

  4. Posted July 7, 2018 at 6:23 am | Permalink

    Hello Dimitri,

    Your question appears off topic. I suspect one of our technical consultants can help you with your development efforts. Feel free to contact me directly for hands-on help.

Post a Comment

Your email is never published nor shared. Required fields are marked *


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>