Published on January 30, 2024.

Over the past few weeks, I've written several posts about a custom mobile app that I'm working on for one of my NetSuite clients. The app is intended for use by my client's sales reps, and it's designed to give them easy and fast access to customer information, sales orders, estimates, inventory information, and more.

One of the features that my client requested involved upsells. (If you're not familiar with the concept of upsell, you can read about it here.) The request was the the reps be able to see the same list of items that a user can see in NetSuite when viewing a customer and the Sales > Upsell tab. Here's an example.

Click the image to view a larger version.

Upsell information is available via the UpsellItem table. Here's a query that you can use to replicate the data shown in the NetSuite UI.

SELECT
	BUILTIN.DF( UpsellItem.PurchasedItem ) AS ItemPurchased,
	BUILTIN.DF( UpsellItem.Item ) AS UpsellItem,
	( UpsellItem.CorrrelationFld * 100 ) AS Corrrelation,
	UpsellItem.CountFld AS Count
FROM
	UpsellItem
WHERE
	UpsellItem.Customer = 237
ORDER BY
	Corrrelation DESC,
	Count DESC,
	ItemPurchased

My client wanted the reps to see the upsell items along with prices, with the prices based on the customer's assigned Price Level.

To get a customer's price level, you can query the Customer table for the PriceLevel column - and apply the BUILTIN.DF function to the column to get the price level name. For example...

SELECT
	BUILTIN.DF( PriceLevel ) AS PriceLevelName
FROM
	Customer
WHERE
	ID = 237

In this example, the customer has been assigned a price level named "VIP Customer."

The final query is shown here. I've added a correlated subquery to get the unit price for the upsell item. It's a fairly simple query, made against the ItemPrice table. Notice that I'm using the name of the customer's price level in that query. I'm using the MAX price, which should be the first column (i.e. "QTY 0") price.

SELECT
	BUILTIN.DF( UpsellItem.PurchasedItem ) AS ItemPurchased,
	BUILTIN.DF( UpsellItem.Item ) AS UpsellItem,
	( UpsellItem.CorrrelationFld * 100 ) AS Corrrelation,
	UpsellItem.CountFld AS Count,
	( 
		SELECT 
			 COALESCE( MAX( Price ), 0 )
		FROM 
			ItemPrice 
		WHERE 
			( Item = UpsellItem.Item ) 
			AND ( PriceLevelName = 'VIP Customer' ) 
			AND ( IsInactive = 'F' ) 
	) AS UnitPrice
FROM
	UpsellItem
WHERE
	UpsellItem.Customer = 237
ORDER BY
	Corrrelation DESC,
	Count DESC,
	ItemPurchased

The results of the query are shown here.

Click the image to view a larger version.

One more thing: In order to access the UpsellItem table, you need to use a role that has been assigned the "Lists - Upsell Assistant" permission.

This was an interesting request from my client, and I believe it was the first time that I needed to use the UpsellItem table.

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