Published on January 28, 2024.

One of the client projects that I'm working on is a custom NetSuite purchasing tool. My client has some rather complex business rules that involve how and when they make purchases, based on everything from deals that are in effect, minimum quantities, available credit, and more.

When making a decision about which vendor to purchase an item from, the purchasing agent needs to know which vendors carry the item. They also need to know who the preferred vendor is, the pricing offered by each vendor, and information about their relationship with the vendor - such as the vendor's terms, the credit limit that the vendor has extended, and the current balance with the vendor.

One of the SuiteQL queries that I developed for this project is shown here.

SELECT
	BUILTIN.DF( ItemVendor.Item ) AS Item,
	Vendor.CompanyName AS Vendor,
	ItemVendor.PreferredVendor,
	ItemVendor.PurchasePrice,
	BUILTIN.DF( Vendor.Contact ) AS Contact,
	Vendor.Balance,
	Vendor.CreditLimit,
	BUILTIN.DF( Vendor.Terms ) AS Terms
FROM
	ItemVendor
	INNER JOIN Vendor ON
		( Vendor.ID = ItemVendor.Vendor )
WHERE
	ItemVendor.Item = 114
ORDER BY
	ItemVendor.PreferredVendor DESC,
	Vendor

The main table in the query is ItemVendor. It is used to store the data that you can see in NetSuite when you pull up an item and navigate to the Purchasing/Inventory tab, and the Vendors subtab. For example...

Click the image to view a larger version.

In the query, I'm using the ItemVendor table to get the vendors that carry the item, their prices, and to show which of the vendors is the preferred vendor. I'm then joining to the Vendor table to get additional information such the open accounts payable balance, the credit limit, and the terms.

Also, back in 2020, I wrote about a similar project. The query that I shared in that blog post included additional information about the items being purchased, including quantity information, reorder points, and so on. If you're interested, see "Use SuiteQL to Analyze Inventory and Make Better Purchasing Decisions."