Published on October 14, 2020.

Back in May, I wrote about a client of mine - a promotional products distributor - that needed a way to make information in their NetSuite instance available to an external purchasing system. It was one of the first projects that I worked on where I made use of SuiteTalk REST's SuiteQL support.

Recently, that same client came to me with a similar project. They needed a way to make the product and vendor information in NetSuite available to an external Product Information Manager ("PIM"). In this post, I'll discuss two SuiteQL queries that I used to achieve their goals.

Item Information

The first query, shown here, provides item information, as well as each item's preferred vendor when it's applicable.

SELECT
	Item.ID,
	Item.ItemID,
	Item.Parent,
	Item.ExternalID,
	Item.IsInactive,
	Item.IsOnline,
	Item.ItemType,
	Item.CreatedDate,
	Item.LastModifiedDate,	
	Item.PurchaseDescription,
	Item.QuantityOnHand,
	Item.QuantityBackordered,
	Item.ReorderPoint,
	Item.Cost,
	Item.LeadTime,
	ItemVendor.Vendor AS PreferredVendorID,
	Vendor.Companyname AS PreferredVendorName,
	Vendor.Externalid AS VendorExternalID
FROM
	Item
	LEFT OUTER JOIN ItemVendor
		ON ( ( ItemVendor.item = Item.id ) AND ( ItemVendor.preferredvendor = 'T' ) )
	LEFT OUTER JOIN Vendor
		ON ( Vendor.id = ItemVendor.vendor )
WHERE
	( Item.CreatedDate >= ( CURRENT_DATE - 7 ) )
	OR ( Item.LastModifiedDate >= ( CURRENT_DATE - 7 ) )

Notice that I'm using LEFT OUTER JOINs to get the preferred vendor. This ensures that if an item doesn't have a preferred vendor, then it is still included in the result set. The first join is to the ItemVendor table, which associates an item with the IDs of the vendors who can supply the item. The second join is to the Vendor.

Also notice that in the WHERE clause I'm indicating that we want items returned that were either created or modified in the last 7 days.

Vendor Information

Here's the query for the vendor information.

SELECT
	Vendor.ID,
	Vendor.IsInactive, 
	Vendor.CompanyName,
	Vendor.AccountNumber,
	Vendor.Balance,
	Vendor.Email,
	Vendor.Phone,
	BUILTIN.DF( Vendor.Terms ) AS Terms,
	Vendor.DateCreated,
	Vendor.LastModifiedDate
FROM
	Vendor
WHERE
	( Vendor.DateCreated >= ( CURRENT_DATE - 7 ) )
	OR ( Vendor.LastModifiedDate >= ( CURRENT_DATE - 7 ) )

This query is pretty straightforward. Notice that the WHERE clause is similar to that used to get the item information, in that we're requesting vendors that have been created or modified in the last 7 days.

The Balance column on the Vendors table provides a convenient way to get a vendor's current open Accounts Payable balance. My client uses this in their PIM to make purchasing and sourcing decisions.

And finally, notice the use of the "BUILTIN.DF" function, which maps the numeric Terms value to a string (such as "Net 30"). I've written about SuiteQL Built-In functions previously, first back in early July and again a few weeks later. The "BUILTIN.DF" function is quite helpful, and as you can see here, it saved me from having to make a join.

Wrapping Up

For this project, I made the SuiteQL queries available to my client's PIM via a RESTlet. This was one of those quick, "in and out" projects that I enjoy working on from time to time. And best of all, the client was thrilled with the results.