Published on August 17, 2021.

A few weeks ago, I wrote about a technique that you can use to generate charts using NetSuite data and the most recent beta version of the SuiteQL Query Tool. That technique involved using the tool's HTML document generation function and Chart.js (an open source Javascript-based charting library) to chart a query's results.

In this post, I'm going to share a similar technique that can be used to generate maps. It makes use of two Google APIs: the Maps JavaScript API, and the Geocoding API. If you'd like to try this yourself, you'll need to signup for a Google Cloud Platform account and obtain an API key.

Querying for Addresses

The example that I am using involves generating a map of customer addresses. I'm using a test drive account, so I'm working with limited data. Therefore my query might seem a bit odd. I'm querying for customers who have placed at least on sales order whose amount exceeded $20,000.00. (A more "real world" example might be to map your top customers based on sales in the past year.)

In any case, the query that you'd want to use will look similiar to this.

SELECT
	ID AS CustomerID,
	CompanyName,
	EntityAddress.Addressee,
	EntityAddress.Addr1,
	EntityAddress.City,
	EntityAddress.State,
	EntityAddress.Zip,
	EntityAddress.Country
FROM
	Customer
	INNER JOIN CustomerAddressbook ON
		( CustomerAddressbook.entity = Customer.id )	
		AND ( customerAddressbook.defaultshipping = 'T' )
	INNER JOIN EntityAddress ON	
		( EntityAddress.nkey = CustomerAddressbook.addressbookaddress )	
WHERE
	( Customer.ID IN ( SELECT Entity FROM Transaction WHERE ( Type = 'SalesOrd' ) AND ( ForeignTotal > 20000 ) ))
	AND ( EntityAddress.Zip IS NOT NULL )
ORDER BY
	CompanyName

I'm joining the Customer table to the CustomerAddressbook table, and then joining to the EntityAddress table. The CustomerAddressbook table is a join table that maps a customer to one or more addresses. It is also used to indicate which address is the customer's default shipping and billing address. (Note that in this example I'm joining to the customer's default shipping address.)

The EntityAddress table is a somewhat generic table that stores addresses for many different NetSuite entity types. In it, you'll find addresses for customers, vendors, employees, and more.

Also note that if you were working with Vendor addresses, you'd want to use the VendorAddressbook table to join the Vendor table to the EntityAddress table. Similarly, for employees, you'd use the EmployeeAddressbook table to join from the Employee table to the EntityAddress table.

In my test account, the results of the query look like this.

The HTML / Javascript Template

Now that we have the addresses that we want to generate a map for, we can add a template consisting of HTML and JavaScript. I'm including the template at the end of this post. (Remember to replace the references to "API-KEY" with your own API key.)

In the template, I'm using some very basic FreeMarker code to take the results of the query and generate a JavaScript array of addresses. That code runs server-side.

The code involved in generating the map runs client-side. For each address that is being added to the map, a call is made to the Geocoding API to get the latitude and longitude for the address. In addition, an "infowindow" is created for each address, so that when a pin is clicked, the infowindow appears. The infowindow is simply a snippet of HTML. In this case, I'm displaying the customer's name and address. Additionally, the customer's name serves as a link back to NetSuite, which displays the customer's information. Note that you could easily use alternative information in the infowindow.

This example uses a standard "blue-dot" icon as the pin for all of the addresses, but you could easily add conditional logic to change the pins so that they represent a certain type of customer. For example, you could drop different pins to represent the salesrep associated with the customer. And if you'd like to explore alternative pins, here are the Classic Google My Maps Icons and more.

Here's an animation showing the process of generating the map.

One more note about this technique: Ideally, you'd already have the coordinates for each address stored in NetSuite, and have a process for obtaining them from the Geocoding API. That would prevent you from having to look them up via the API every time that an address was mapped. This would also make the generation of the map go much faster.

Wrapping Up

As always, I hope you found this post to be helpful. Please let me know if you have any questions about it.

<!DOCTYPE html>
<html>
	<head>
		<meta name="viewport" content="initial-scale=1.0, user-scalable=no">
		<meta charset="utf-8">
		<title>SuiteQL-Based Map</title>
		<style>
			html, body, #mapCanvas {
				height: 100%;
				margin: 0px;
				padding: 0px
			 }
		</style>

		<script src="https://maps.googleapis.com/maps/api/js?key=API-KEY&v=3.exp"></script>

		<script>

			function mapInit() {

				var mapOptions = { center: new google.maps.LatLng( 0, 0 ), zoom: 4 }

				var map = new google.maps.Map( document.getElementById('mapCanvas'), mapOptions );
																
				var addresses = new Array();
				
				<#list results.records as record>
				
					addresses.push( 
						{ 
							"customerid": "${record.customerid}",
							"name": "${record.companyname}",
							"addr1": "${record.addr1}",
							"city": "${record.city}",
							"state": "${record.state}",
							"zip": "${record.zip}",
							"country": "${record.country}"
						} 
					);
														
				</#list>
				
				var markers = new Array();
				
				var infoWindows = new Array();
				
				var latLngBounds = new google.maps.LatLngBounds();
								
				for ( let i = 0; i < addresses.length; i++ ) { 
				
					var address = addresses[i];

					var addressString = encodeURIComponent( address.addr1 + ', ' + address.city + ', ' + address.state + ' ' + address.zip + ' ' + address.country );
					
					var url = 'https://maps.googleapis.com/maps/api/geocode/json?key=API-KEY&address=' + addressString;
					
					var request = new XMLHttpRequest();
					request.open( 'GET', url, false );
					request.send( null );

					if ( request.status === 200 ) {
										
						var responseObj = JSON.parse(request.responseText);
						
						var location = responseObj.results[0].geometry.location;
					
						var latLng = new google.maps.LatLng( location.lat, location.lng );
						
						var content = '<div>' + address.name + '</div>';
						
						var href= '/app/common/entity/custjob.nl?id=' + address.customerid;
						
						var content = '<div>' + '<a href="' + href + '" target="_new" style="font-weight: bold;">' + address.name + '</a><br>';
						content += address.addr1 + '<br>';
						content += address.city + ', ' + address.state + ' ' + address.zip + ' ' + address.country;
						content += '</div>';
						
						infoWindows[i] = new google.maps.InfoWindow( { content: content } );
												
						markers[i] = new google.maps.Marker( { position: latLng, map: map, title: address.name, icon: 'https://maps.google.com/mapfiles/ms/icons/blue-dot.png' } );
						
						google.maps.event.addListener( markers[i], 'click', function() { infoWindows[i].open( map, markers[i] ); } ); 	
						
						latLngBounds.extend( markers[i].position );				
					
					}					
				
				}
				
				map.setCenter(latLngBounds.getCenter());
				
				map.fitBounds(latLngBounds);			

			}

			google.maps.event.addDomListener( window, 'load', mapInit );

		</script>
	</head>
	<body>
	<div id="mapCanvas"></div>
	</body>
</html>