Published on January 29, 2023.

Late last year, a NetSuite client asked me for help on a project that they were working on. Their goal was to reduce the number of customer service inquiries that they were getting, especially calls and emails that they were receiving from customers that wanted to know the status of their orders, and tracking numbers for orders that have shipped.

My client wanted to provide a Web site that customers could use to get this information. And to simplify things, they wanted customers to be able to lookup orders without needing to setup an account, log into the site, and so on.

In this post I'll discuss the process that I went through in designing and developing the application, including crafting the SuiteQL query that the app uses, setting up SuiteAPI, and developing the Web app using Xojo.

But first, here's a short animation that shows the application.

Click the image to view a larger version.

The SuiteQL Query

As I mentioned earlier, one of my client's goals was to provide a way for their customers to lookup their orders without needing to log into an application. To achieve that goal, we decided to ask customers for two things: Their order number, and the zip code that the order is shipping to.

The order number is actually the unique NetSuite internal ID for the transaction, and that's really all we need to identify the order. But by also asking for the zip code, we can ensure that the customer knows at least something else about the order, and this helps prevent someone from simply looking up order after order. I should also point out that we can get away with this very simple authentication scheme because we're not showing any personally identifiable information (PII) in the app. We're only showing an order's status and tracking numbers.

My first step was to create the SuiteQL query that the application uses. In June of last year, I wrote about how you can use SuiteQL to get shipment information, tracking numbers, and so on. Those queries were primarily designed to provide information about outbound shipments.

However, for this project, I needed a query that starts with a sales order and joins to the shipment information.

Here's the first version of the query that I developed.

SELECT DISTINCT
	SalesOrder.TranID AS SalesOrderTranID, 
	SalesOrder.ID AS SalesOrderID,
	REPLACE( BUILTIN.DF( SalesOrder.Status ), 'Sales Order : ', '' ) AS Status,
	NTLL.PreviousDoc AS SalesOrderNSID, 
	NTLL.NextDoc AS FulfillmentNSID, 
	Fulfillment.TranID As FulfillmentTranID, 
	Fulfillment.TranDate AS FulfillmentDate, 
	BUILTIN.DF( Fulfillment.Entity ) AS Fulfillment_Entity, 
	TransactionShipment.ShippingMethod, 
	TransactionShipment.ShippingRate, 
	TransactionShipment.HandlingRate, 
	TransactionShipment.Weight, 
	TransactionShipment.SourceAddress, 
	TransactionShipment.DestinationAddress, 
	ShipmentPackage.TrackingNumber
FROM
	Transaction AS SalesOrder
	INNER JOIN TransactionShippingAddress ON
		( TransactionShippingAddress.nkey = SalesOrder.ShippingAddress )
	INNER JOIN TransactionLine AS SOLine ON
		( SOLine.Transaction = SalesOrder.ID )
		AND ( SOLine.MainLine = 'F' )
	INNER JOIN NextTransactionLineLink AS NTLL ON
		( NTLL.PreviousDoc = SOLine.Transaction ) 
		AND ( NTLL.PreviousLine = SOLine.ID ) 
		AND ( NTLL.LinkType = 'ShipRcpt' )
	INNER JOIN Transaction AS Fulfillment ON 
		( Fulfillment.ID = NTLL.NextDoc ) 
		AND ( Fulfillment.Type = 'ItemShip' )
	INNER JOIN TransactionShipment ON
		( TransactionShipment.Doc = Fulfillment.ID )
	INNER JOIN ShipmentPackage ON
		( ShipmentPackage.ItemFulfillment = Fulfillment.ID )
WHERE
	( SalesOrder.Type = 'SalesOrd' ) 
	AND ( SalesOrder.ID = ? )
	AND ( TransactionShippingAddress.Zip = ? )

The query includes a number of joins. It starts with the Sales Order transaction, joins to the transaction's lines, and from there it uses the NextTransactionLineLink table to join to any related Item Fulfillment transactions. I then joined to the TransactionShipment table to get the shipment-level information (ship method, rate, and so on). A shipment can include multiple packages, and so to get the tracking numbers for the packages, I also joined to the related ShipmentPackage records. Also note that I joined to the TransactionShippingAddress table, which is how I'm filtering based on the zip code.

That first version of query works, and it returns a lot of useful information. However, for this project, I really only need to know the status of the order and the related tracking numbers. So I was able to greatly simplify the query.

Here's the revised query, and the version that I ended up using in the application.

SELECT DISTINCT
	REPLACE( BUILTIN.DF( SalesOrder.Status ), 'Sales Order : ', '' ) AS Status,
	ShipmentPackage.TrackingNumber
FROM
	Transaction AS SalesOrder
	INNER JOIN TransactionShippingAddress ON
		( TransactionShippingAddress.nkey = SalesOrder.ShippingAddress )
	LEFT OUTER JOIN NextTransactionLineLink AS NTLL ON
		( NTLL.PreviousDoc = SalesOrder.ID ) 
		AND ( NTLL.LinkType = 'ShipRcpt' )
	LEFT OUTER JOIN ShipmentPackage ON
		( ShipmentPackage.ItemFulfillment = NTLL.NextDoc )
WHERE
	( SalesOrder.Type = 'SalesOrd' ) 
	AND ( SalesOrder.ID = ? )
	AND ( TransactionShippingAddress.Zip = ? )

As you can see, I was able to reduce the number of the joins. Also, I modified the joins to the NextTransactionLineLink and ShipmentPackage, changing them from INNER joins to LEFT OUTER joins. By doing so, a record will be returned, even if the order hasn't shipped.

Setting Up SuiteAPI

Last November, I open sourced SuiteAPI, an alternative NetSuite Web API that I've developed and frequently use as the foundation for client integration projects. SuiteAPI provides the type of functionality that I often need for those types of projects, whether I'm developing Web, mobile, or desktop applications.

I had previously used SuiteAPI for other projects for this client, so it was already installed and ready to go. (For detailed instructions on how to install and configure SuiteAPI, visit: https://suiteapi.com/install/ ).

The only change that I needed to make was to the NetSuite role that I had setup for the SuiteAPI integration. I needed to add a permission so that SuiteAPI had access to Item Fulfillment transactions.

Here's the complete list of permissions that the app needed:
• Transactions - Find Transaction (View)
• Transactions - Sales Order (View)
• Transactions - Item Fulfillment (View)
• Reports - SuiteAnalytics Workbook (Edit)
• Setup - Log in using Access Tokens

Note: If you need help determining the permissions that are needed to access tables, this blog post might help.

The application uses SuiteAPI's "queryRun" procedure, and the request payloads look like this:

{
  "procedure": "queryRun",
  "query": "
	SELECT DISTINCT
		REPLACE( BUILTIN.DF( SalesOrder.Status ), 'Sales Order : ', '' ) AS Status,
		ShipmentPackage.TrackingNumber
	FROM
		Transaction AS SalesOrder
		INNER JOIN TransactionShippingAddress ON
			( TransactionShippingAddress.nkey = SalesOrder.ShippingAddress )
		LEFT OUTER JOIN NextTransactionLineLink AS NTLL ON
			( NTLL.PreviousDoc = SalesOrder.ID ) 
			AND ( NTLL.LinkType = 'ShipRcpt' )
		LEFT OUTER JOIN ShipmentPackage ON
			( ShipmentPackage.ItemFulfillment = NTLL.NextDoc )
	WHERE
		( SalesOrder.Type = 'SalesOrd' ) 
		AND ( SalesOrder.ID = ? )
		AND ( TransactionShippingAddress.Zip = ? )
	",
  "params": [
    "24700",
    "10001"
  ]
}

Notice that I'm using query parameters for both the Sales Order ID and the Shipping Address Zip Code.

The Xojo Web Application

For this project, I used Xojo, my preferred low-code development tool. I've written extensively about Xojo in the past, but one related blog post that might be of interest to you is "NetSuite Integration with SuiteAPI and Xojo." In that post, I provide additional information on SuiteAPI, and an example of a Web app developed with Xojo that uses SuiteAPI to integrate with NetSuite. That Web app provides users with a way to easily lookup an item in NetSuite.

The Xojo project that I developed for this order status lookup application is very similar. It consists of a few fields (one for the order number, another for the zip code), a button, and so on. Here's a screen shot showing the app's single Web page in the Xojo IDE.

Click the image to view a larger version.

When the user clicks the button, the app uses an instance of custom URLConnection object to make an API call to SuiteAPI. When the API response is received, it's evaluated, and the interface is updated accordingly. Here's another screenshot of the Xojo IDE, this time showing the code that runs when a SuiteAPI response is received.

Click the image to view a larger version.

Wrapping Up

This is another great example of how you can use SuiteAPI, SuiteQL, and a rapid application development tool like Xojo to quickly develop applications that integrate with NetSuite. I can't stress enough how nice it is to be able combine Xojo, SuiteAPI, and SuiteQL queries, on projects like this.

If you're interested in learning more about SuiteAPI, please visit https://suiteapi.com. And to learn more about Xojo, visit https://xojo.com.