This article is relevant if you use NetSuite Item Groups and you need to act on them to determine if inventory is available.

Background

In our work with our clients, we often times are working with NetSuite item groups. I have written articles in the past about item groups that discuss some of the merits of such structures:

1. Understand NetSuite Item Groups vs. Kits to Produce Superior Reporting
2. Tip: NetSuite Item Group Client Side SuiteScript Programming Challenges

The major question that arises is “How many item groups do I have in stock?”

Use a Phantom Inventory Component

Many times, our clients are using item groups but in the minds of people who act with them, they think of them as a single unit or a bundle. The question then becomes “how do we know if we have enough inventory available?”  One possible way to answer the availability question is to innovate and add another phantom (fake) inventory component to your item group to represent the inventory ledger for that bundle. For example, consider the following item group (I have been recently doing network enhancements in my house so these tools come to mind):

RJ45 Cable Crimper Kit [item group]

• Cable Crimper Tool x 1 [inventory item]
• Cable Crimper Cutter x 1 [inventory item]
• RJ45 Jacks x 12 [inventory item]
• RJ45 Cable Crimper Kit Package (phantom) x 1 [inventory item]

In this example, the RJ45 Cable Crimper Kit Package (phantom) is not a real inventory item. Instead, this item represents the item group and should structurally always be defined with a component quantity of 1. When you fulfill on the item group, you fulfill the package line along with the other items.

This model can work and often invites interesting modeling discussion around what the price and cost are for the phantom. If used correctly in your operations, you can assess the number of item groups available via the phantom. A bit of a warning: using item groups in this manner opens all kinds of reporting and other flexibility but will demand more of the business users in day-to-day use.

How to Dynamically Derive the Number of NetSuite Item Groups Available

Without the item groups, there is another way to assess the number of item groups available. Since NetSuite item groups are represented via a simple bill of materials, we can determine the number of inventory items available of the underlying components and come up with a quantity. Thus, the number of item groups available become the smaller of the total available underlying components that could be pulled together into a bundle.

NetSuite saved search can help us. However, by using some SuiteScript (2.0 presented), we can derive a simple to understand and reliable value for the number of item groups available and on hand. The algorithm below was created by one of our senior consultants and was used to support an eCommerce integration. The cool part of this is the eCommerce system did not need to know anything about the item group — keeping it very simple to merchandise and sell to the end users hiding all the complexity in the NetSuite backend.

NetSuite SuiteScript 2.0 for Item Group Inventory Available

//result set looks like this
//{"internalid":"298","itemId":"X999X : 9999","qtyAvailable":1,"itemType":"InvtPart"}
//{"internalid":"10180","itemId":"ZT417-S","qtyAvailable ":58,"itemType":"InvtPart"}
//{"internalid":"10181","itemId":"ZT418-S","qtyAvailable ":193,"itemType":"InvtPart"}
//{"internalid":"11209","itemId":"B1001","qtyAvailable ":133,"itemType":"Group"}


function getItemInventory() {
    var funcName = "getItemInventory";
    var result;
	var BASE_PRICE = 1;

    try {
		var itemList = [];
			//you can either pass in the list of locations to this function, or perform a search here.  
			//Yet the bottom line is that locationList[] is an array of the internal IDs of locations whose inventory you want to work with
		var locationSearch = search.create({
			type: search.Type.LOCATION,
			filters: ["makeinventoryavailable",search.Operator.IS,true ],  
		}).run().getRange(0,1000);

		var locationList = [];
		for (var i = 0; i < locationSearch.length; i++){
			locationList.push(locationSearch[i].id);
		}

		//assumptions below on location and pricelist based criteria and results
		var itemSearchObj = search.create({
			type: search.Type.ITEM,
			filters: [
				  ["type",search.Operator.NONEOF,["OthCharge","Description","Discount","Markup","NonInvtPart","Payment","Subtotal","Service","Kit"]]
				  ,"AND",["isinactive",search.Operator.IS,false]
				  ,"AND",["pricing.pricelevel",search.Operator.ANYOF,[BASE_PRICE]]
				  ,"AND",["inventorylocation.internalid",search.Operator.ANYOF,locationList]
				 ],
			columns: [search.createColumn({name: "internalid", summary: search.Summary.GROUP}),
				  search.createColumn({name: "itemid", summary: search.Summary.GROUP}),
				  search.createColumn({name: "type", summary: search.Summary.GROUP}),
				  search.createColumn({name: "internalid", join: "inventorylocation", summary: search.Summary.GROUP}),
				  search.createColumn({name:"locationquantityavailable", summary: search.Summary.SUM}),
				  search.createColumn({name:"unitprice", join: "pricing", summary: search.Summary.AVG})
				 ]
		}).run();

		//build a list of items that are not groups
		var minRange = 0;
		var itemSearch = itemSearchObj.getRange(minRange, minRange+1000);
		while (itemSearch.length > 0) {
			for (var i = 0; i < itemSearch.length; i++){
                addItemInfo(itemList, itemSearch[i], locationSearch);
				minRange += itemSearch.length;
				itemSearch = itemSearchObj.getRange(minRange, minRange+1000);
			}
		}

		// now that we have all the regular items, retrieve the list of item groups and their components, to assemble the inventory information for them
		var itemGroups = search.create({
			type: search.Type.ITEM,
			filters:    [
					["isinactive",search.Operator.IS,false]
						,"AND",["type",search.Operator.ANYOF,["Group"]]
					],
			columns:["itemid","memberitem","memberquantity"]
			}).run().getRange(0,1000);

		i = 0;
		var itemGroupList = [];

		for (var i = 0; i < itemGroups.length; i++) {
			if (i == 0 || itemGroups[i].id != itemGroups[i-1].id) {
				// found new item; add it
				for (var x = 0; x < itemList.length; x++){
					if (itemList[x].internalid == itemGroups[i].getValue("memberitem")) {
						  var obj = getGroupItemInventory(itemList[x],itemGroups[i].getValue("memberquantity"));
						  obj.internalid = itemGroups[i].id;
						  obj.itemId = itemGroups[i].getValue("itemid");
						  obj.itemType = "Group";
						  itemGroupList.push(obj);
					}
				}
			} else {
				// another member of the last item
				for (var x = 0; x < itemList.length; x++){
					if (itemList[x].internalid == itemGroups[i].getValue("memberitem")){
						 mergeItemInventory(itemGroupList[itemGroupList.length-1], getGroupItemInventory(itemList[x],itemGroups[i].getValue("memberquantity")));
					}
				}
			}
		}

		//add the group items to the list
		for (var i = 0; i < itemGroupList.length; i++)
			itemList.push(itemGroupList[i]);
		return itemList;

	} catch (e) {
        log.error("InventoryInquiry",e);
        return e;
    }
}


// ================================================================================================================================

function mergeItemInventory(obj, newItem) {
	obj.qtyAvailable = Math.min(obj.qtyAvailable,(newItem.qtyAvailable || 0));
}
 
// ================================================================================================================================

function getGroupItemInventory(itemData, qty) {
	// if this member requires more than 1 unit, then we need to divide the quantity on hand by that quantity
	var obj = JSON.parse(JSON.stringify(itemData));
	obj.qtyAvailable = obj.qtyAvailable / qty;
	return obj;                  
}

// ================================================================================================================================

function addItemInfo(itemList, itemData, locationSearch) {
	var obj;
	for (var i = 0; i < itemList.length; i++){
		if (itemList[i].itemId == itemData.getValue({name: "itemid", summary: search.Summary.GROUP})) {
			  obj = itemList[i];
			  break;
		}
	}

	if (!obj) {
		var obj = createNewItemObject(
			itemData.getValue({name: "internalid", summary: search.Summary.GROUP}), 
			itemData.getValue({name: "itemid", summary: search.Summary.GROUP}), 
			itemData.getValue({name: "type", summary: search.Summary.GROUP})
			);
		itemList.push(obj);
	}
	var qtyAvailable = itemData.getValue({name:"locationquantityavailable", summary: search.Summary.SUM}) || 0;
	obj.qtyAvailable = parseInt(qtyAvailable);
	obj.price = parseFloat(itemData.getValue({name:"unitprice", join: "pricing", summary: search.Summary.AVG}));
}

function createNewItemObject(intId, itemId, type) {
	var obj = {internalid: intId, itemId: itemId, qtyAvailable: 0, itemType: type};
	return obj;
}

Expand Your Business Capacities with NetSuite Innovation

The key to getting more out the NetSuite business system is to fundamentally understand the architecture of the built-in capacities. Then, with the help of the customization platform, you have the ability to invent all kinds of solutions to business problems. If you would like stronger leadership and talent to help you get more out of your NetSuite investment, let's have a conversation.

Marty Zigman

Holding all three official certifications, Marty is regarded as the top NetSuite expert and leads a team of senior professionals at Prolecto Resources, Inc. He is a former Deloitte & Touche CPA and has held CTO roles. For over 30 years, Marty has produced leadership in ERP, CRM and eCommerce business systems. Contact Marty to set up a conversation.

More Posts - Website - Twitter - Facebook - LinkedIn - YouTube