Sometimes, as a NetSuite developer, you need to unlock location-based insights. Want to see how far customers are clustered, or optimize sales rep routes? Sure, you could use an external mapping API like Google Maps or MapQuest, but those often come with a cost.
What if you could calculate distances directly within NetSuite, for free? If "as the crow flies" distances are sufficient, and you don't need real-time traffic data, there's a powerful mathematical solution: the Haversine formula.
How it Works:Â The Haversine formula treats the Earth as a sphere. Given the latitude and longitude coordinates of two points, it calculates the central angle between them and then, knowing the Earth's radius, determines the distance along the surface.
Putting It All Together: A NetSuite Distance Calculation Example
Let's walk through a practical example of how to use the Haversine formula in NetSuite to find customers within a specified radius.
Prerequisites
US Zip Code Data: Download the free CSV file from https://simplemaps.com/data/us-zips
NetSuite File Cabinet:Â You'll need a place to store the CSV for your scripts to access.
Step 1: Prepare Your NetSuite Environment
Custom Fields:Â Add two custom number fields to the Address subrecord:"Lat" (Latitude)"Long" (Longitude)
Mass Update Script (Optional):Â If you have existing address records, create a Map/Reduce script to populate the Lat/Long fields based on the CSV data.
User Event Script:Â Create a User Event script on the Customer record that will automatically populate Lat/Long for new or updated addresses by referencing the CSV.
Step 2: The Saved Search
Now that you have latitude and longitude data for your customers and a reference point (your own latitude and longitude), let's create a customer saved search using the Haversine formula.Â
 CASE WHEN SQRT(POWER(69.1 ( TO_NUMBER({address.custrecord_address_lat}) - " + String(cords.latitude) + "), 2) + POWER(69.1 (" + cords.long + " - TO_NUMBER({address.custrecord_address_long}) ) * COS( TO_NUMBER({address.custrecord_address_lat}) / 57.3), 2)) <= " + String(milesAway) + " THEN 0 ELSE 1 END Criteria: Equal to 0
Understanding the Saved Search Formula:
While it may look complex, the formula boils down to this:
It takes the latitude and longitude of a customer's address ({address.custrecord_address_lat} and {address.custrecord_address_long}").
It compares them to your reference latitude (cords.latitude} and longitude (cords.long}).
It uses the Haversine formula to calculate the distance between these two points.
Finally, it checks if that distance is less than or equal to your desired radius (milesAway).
What the Search Results Show:
The saved search filters the customers based on the distance calculation.
It will only return customers whose address locations (latitude and longitude) fall within the radius you specified in {milesAway}.
In simpler terms, this search finds all customers whose physical addresses are within your set distance from your reference point.
Taking it a Step Further: Displaying Distance in the Results
The power of the Haversine formula doesn't stop there! You can also leverage the formula within the saved search results themselves. By adding a new formula (numeric) to the results with the same calculation, but labeled "Miles Away," you can see the actual distance between each customer and your reference point. This allows you to further filter and sort your results based on proximity.
And There You Have It: Unlock Location-Based Insights with the Haversine Formula
With a little help from mathematics, you've just created a free, in-house distance calculator for NetSuite! Now, let's think about the possibilities:
Examples of How to Use This:
Sales Optimization:Â Find clusters of customers for targeted sales trips or marketing campaigns.
Service Routing:Â Streamline service calls or deliveries by assigning them based on proximity.
Market Analysis:Â Evaluate how far your customer base extends, or map out competitor locations.
Proximity-Based Search:Â Build custom search features on your SuiteCommerce website that let customers find products or stores "near me."
Learn More
If you are thinking about a new ERP or are looking for a partner with a track record of developing creatiove solutions such as this one, feel free to reach out. Equify Advisors is a NetSuite Alliance Partner and has certified experts within the ecosystem. Set some time with us to learn more on our website at https://www.equifyadvisors.com/contacttheteam.
Author:
Bobby Stevens - Senior Developer, NetSuite Solutions
NetSuite Certified SuiteFoundation
NetSuite SuiteCloud Developer II
Comments