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.

Background

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 = 'https://maps.googleapis.com/maps/api/geocode/json?address=' + 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 = {};
    obj.lat = 0;
    obj.lng = 0;
    return obj;
};

NetSuite SuiteScript Saved Search to Perform Distance Query

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

CREATE OR REPLACE FUNCTION distance (Lat1 IN NUMBER,
                                     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;

BEGIN
  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))));
END;
RUN

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))))
		*(COS(('+lat1+'*3.14159265359/180)-('+lat2+'*3.14159265359/180)))
		-(1.0-COS(('+lon1+'*3.14159265359/180)-('+lon2+'*3.14159265359/180)))
		*COS(('+lat1+'*3.14159265359/180)+('+lat2+'*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.

Marty Zigman

Holding all three official certifications, Marty is regarded as the top 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 30 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 - YouTube