Published on May 21, 2023.

A NetSuite client recently asked me for help with a sales-related project. Their goal was to provide their sales team with potentially helpful information during the sales order entry process.

Specifically, the client wanted to provide sales reps with two lists:
• The company's top selling items (based on all sales made in the past 90 days).
• The items that the customer (for whom the sales order is being entered) has previously purchased.

This project required the use of several elements and techniques. First, there are two queries involved - one to get the top selling items, and a second to get the customer's item purchase history. Additionally, there's the need for two scripts - a User Event Script which runs server-side to add UI elements (including a custom tab, subtabs, and sublists), and a Client Script which loads the customer purchase history when a client is selected.

In this post I'll walk you through the queries and the scripts that were involved. Also, a link to the scripts is provided below.

But first, here's a short animation showing how the solution works.

Click the image to view a larger version.

The SuiteQL Queries

As I mentioned earlier, I used two SuiteQL queries to create this solution.

The first query returns the top 25 items sold over the past 90 days.

SELECT TOP 25
	Item.ItemID,
	Item.Description,
	SUM( SOLine.Quantity * -1 ) AS QuantitySold				
FROM
	Transaction AS SalesOrder
	INNER JOIN TransactionLine AS SOLine ON
		( SOLine.Transaction = SalesOrder.ID )
		AND ( SOLine.MainLine = 'F' )
	INNER JOIN Item ON
		( Item.ID = SOLine.Item )						
WHERE
	( SalesOrder.Type = 'SalesOrd' )
	AND ( SalesOrder.TranDate > BUILTIN.RELATIVE_RANGES( 'DAGO90', 'START' ) )
	AND ( SalesOrder.Void = 'F' )
	AND ( SalesOrder.Voided = 'F' )
	AND ( Item.ItemType <> 'Discount' )
GROUP BY
	ItemID,
	Description
ORDER BY
	QuantitySold DESC,
	ItemID

Notice the use of the BUILTIN.RELATIVE_RANGES function. This makes dynamically specifying the date filter easy. If you're not familiar with the function, you might want to check out "Using Dynamic Calendar Date Ranges in SuiteQL Queries" from August of 2021.

The second query, which returns the specific customer's purchase history, looks like this.

WITH 
	SalesDetails AS
		(
			SELECT
				Item.ItemID,
				Item.Description,
				( SOLine.Quantity * -1 ) AS Quantity,
				SalesOrder.TranDate
			FROM
				Transaction AS SalesOrder
				INNER JOIN TransactionLine AS SOLine ON
					( SOLine.Transaction = SalesOrder.ID )
					AND ( SOLine.MainLine = 'F' )
				INNER JOIN Item ON
					( Item.ID = SOLine.Item )
			WHERE
				( SalesOrder.Type = 'SalesOrd' )
				AND ( SalesOrder.Entity = ? )
				AND ( SalesOrder.Void = 'F' )
				AND ( SalesOrder.Voided = 'F' )
				AND ( Item.ItemType <> 'Discount' )
		),
	SalesSummary AS
		(
		SELECT
			ItemID,
			Description,
			SUM( Quantity ) AS TotalPurchased,
			MAX( TranDate ) AS LastPurchased
		FROM
			SalesDetails
		GROUP BY
			ItemID,
			Description
		)
SELECT
	ItemID,
	Description,
	TotalPurchased,
	TO_CHAR( LastPurchased, 'MM/DD/YYYY' ) AS LastPurchased
FROM
	SalesSummary
ORDER BY
	TotalPurchased DESC,
	ItemID

Notice the use of the WITH clause and the two subqueries that it defines. These are known as Common Table Expressions, a concept that I first discussed in this post from December of 2021. Also notice that the second subquery references the first subquery. This is one of the ways that CTEs can be helpful, especially when working with complex queries that consist of multiple subqueries.

The first subquery, SalesDetails, returns all of the customer's sales order lineitems, including the item ID, description, quantity sold, and the transaction dates.

The second subquery, SalesSummary, summarizes the results of the SalesDetails subquery, group the rows by item ID and description, and totalling the quantity sold, and calculating the most recent date that the customer purchased the item.

The final query returns the results of the SalesSummary subquery. It uses the TO_CHAR function to return the last purchased date in MM/DD/YYYY format.

The User Event Script

The User Event Script uses the "beforeLoad" entry point to customize the sales order entry form. It adds a "Sales Assistance" custom tab to the form, and positions it before the standard "Items" tab.

It thens adds the "Top 25 Items Sold" subtab to the "Sales Assistance" tab. The subtab includes a sublist, which is populated using the results of the "top 25 items" SuiteQL query that I mentioned earlier.

The User Event Script adds a second subtab, "Customer Top Purchases," to the "Sales Assistance" tab, and that subtab also contains a sublist. However, the script does not populate that sublist. The reason is that when the User Event Script runs, the customer for whom the sales order is being entered isn't yet known. That's handled by the Client Script.

The Client Script

The Client Script dynamically loads the "Customer Top Purchases" sublist when a customer is selected. It uses the "fieldChanged" entry point to determine when a customer has been selected. It then uses the customer purchase history SuiteQL query mentioned above to load the data based on that customer.

In the script, I get the selected customer's ID using this:

let entity = context.currentRecord.getValue( { fieldId: 'entity' } );

Notice that the SuiteQL query in the script uses a parameter for the "SalesOrder.Entity" filter. When the "query.runSuiteQL" method is called, the entity is included as the single params array value, like this:

let records = query.runSuiteQL( { query: sql, params: [ entity ] } ).asMappedResults();

Wrapping Up

You can download the scripts referenced in this post here:
https://tdietrich-opensource.s3.amazonaws.com/suitescripts/sales-assistance-scripts.v20230521.zip

I enjoy working on projects like this because they make use of SuiteQL in ways that have a direct impact on the user experience. In this case, the queries are being used to provide helpful information to sales reps during the sales order entry process. My client is already planning to provide additional information to the sales reps, including upsells and cross-sells based on items as they are added to a sales order.

As always, I hope you've found the queries and code that I've shared in this post to be helpful.