Learn the High Performance Pattern to Keep NetSuite Inventory Synchronized with eCommerce

This article is relevant if you use NetSuite and you need to keep an eCommerce or other party updated as to the inventory availability.

Background

In the realm of NetSuite Systems Integration, we often encounter the necessity to sync inventory data between NetSuite and third-party eCommerce platforms. While Shopify is a frequent collaborator, in this instance, our focus is on a client using Big Commerce. The driving principle here is straightforward: we should only accept orders if we are certain we have the inventory to fulfill them.

At the core of this initiative is the presumption that NetSuite stands as the definitive inventory ledger upon which the eCommerce system relies for authoritative data.  This approach is what we term the “leader pattern,” a concept elaborated upon in my 2023 article, Reconfigure NetSuite To Scale Boomi and Stripe Configuration Challenges.

To amplify the efficacy of this approach, we augmented our client’s NetSuite configuration to include inventory data from their suppliers. This was particularly crucial for them as their business model leans heavily on drop shipping. For those interested in diving deeper into this inventory model, please read my 2023 article, How to Include Supplier Available On Hand Inventory Values in NetSuite.   The solution below will reference supplier inventory concepts.

The simplicity of our client’s requirements admittedly made the solution easier to solve.   Their eCommerce platform needed only a binary Yes/No flag to indicate whether an item was in stock or not. There was no call for a sophisticated inventory ledger on the eCommerce side (we appreciate when clients rely more heavily on NetSuite’s authority).  This eCommerce flag was the key logic for guiding the customer’s shopping experience, ensuring that orders were only taken if they could be fulfilled.

Leveraging NetSuite Platform Capacities to Update eCommerce

When it comes to solving the inventory syncing challenge, multiple avenues exist. However, our firm champions leveraging NetSuite’s inherent capabilities to fulfill these requirements. This strategy amplifies the ROI from your NetSuite investment and sidesteps the need for third-party utilities like Celigo.  Let me be upfront about our preference for native NetSuite solutions—it’s an intentional bias.  However, we should not be dogmatic about it as well.

In our exploration of this problem, Boban D., a highly valued member of our Senior Technical Analysts, architected a pattern for flagging items requiring updates. We were pleasantly surprised by the brisk performance—queries on a sizable database of around 500,000 items returned results in just 1-2 seconds. With this level of efficiency, running these updates every 15 minutes, the finest granularity offered by NetSuite poses no concern.

For those interested in the technical details, you’ll see the pattern in my 2021 article, Learn How To Update NetSuite using SQL, where we disclose license-free algorithms to empower clients to update NetSuite via SuiteQL.  This technology has increasingly become our go-to, considerably expanding our problem-solving scope.

Here’s the core pattern to review before studying the SuiteQL below:

1. Item Availability Assessment: Start by identifying items and verifying their availability. You’re free to establish rules as intricate as your situation demands. In our implementation, we even incorporated vendor stock levels as discussed in this article.
2. Kit Availability Evaluation: Kits present unique challenges; if a single component is unavailable, the entire kit becomes unavailable. Here, subqueries come into play to assess kit statuses, considering that eCommerce systems generally don’t distinguish kits from individual items.  Consider my 2022 article, Solve the NetSuite Theoretical Item Groups or Kits Available Quantity Challenge.
3. Flagging Items for Synchronization: Items that flip states—either from being available to unavailable or vice versa—need to be flagged. The idea is to capture dynamic shifts induced by other transactional activities.
4. eCommerce System Update: Once the items are marked for sync, execute another process to refresh the eCommerce system. This involves an API call to the eCommerce platform, and once the update is successfully ingested, we revert the synchronization flag to “No.”

For a more visual explanation, please click and refer to the related image detailing the conceptual architecture.

NetSuite SuiteQL for Item and Kit Availability for Synchronization

For NetSuite administrators inclined to craft their own solutions, the SQL pattern we offer can serve as a catalyst. It’s worth noting that the SQL we employ is engineered to not merely return results but to actually update the NetSuite database, a concept we dive into in depth in my article, Learn How To Update NetSuite using SQL.  Thanks to our license-free tools, administrators can now carry out these crucial updates without venturing into the complexities of scripting.

Finally, the article will not go into the mechanics of actual updates to the eCommerce system. – yet our philosophy is retained.   Please consider my 2022 article, Contrast Platform vs NetSuite Point-to-Point Integration Options.

SuiteQL Patterns to Update Items for Synchronization

Click the images to help you understand the simple item custom fields.

/*
First determine inventory availability based on location 1
and our special vendor managed inventory availability
*/
WITH AVAIL AS (
	SELECT I.id
	, 	CASE 
		WHEN (IL.quantityavailable + NVL(VMI.CUSTRECORD_RTP_VI_ON_HAND,0)) = 0 THEN 'N' ELSE 'Y' 
	END AS available
	FROM Item I
	JOIN InventoryItemLocations IL 
		ON I.id = IL.item
	LEFT OUTER JOIN customrecord_rtp_vendor_inventory VMI 
		ON I.id = VMI.CUSTRECORD_RTP_VI_ITEM
	WHERE
		I. ItemType = 'InvtPart'
		AND IL.location = 1
	)

/*
Join the availability query above with the item database
and only return results back where the available flag is
different from what is currently stored; these items need
to updated for sync.  
Use Prolecto syntax to push updates to the database 
*/
SELECT I.id
, 	'inventoryitem' AS type
, 	AVAIL.available AS custitem_rny_available
, 	'T' AS custitem_rny_need_to_sync
FROM Item I
JOIN AVAIL ON I.id = AVAIL.id
WHERE NVL(I.custitem_rny_available,'?') != AVAIL.available
ORDER BY I.id

/*
First determine required inventory availability of kit members without
respect to item commitments or other; additional logic can be applied
where business rules must be respected
*/

WITH KITMEMBER AS (
	SELECT KM.item
	, 	KM.parentitem
	, 	KM.quantity AS required,
	, 	(IL.quantityavailable + NVL(VMI.CUSTRECORD_RTP_VI_ON_HAND,0)) AS available
	FROM kititemmember KM
	JOIN InventoryItemLocations IL ON 
		KM.item = IL.item and IL.location = 1
	LEFT OUTER JOIN customrecord_rtp_vendor_inventory VMI 
		ON KM.item = VMI.CUSTRECORD_RTP_VI_ITEM
	),
/*
Using all items that participate in kits, now find which
kits have insufficient kit member quantities; these kits will
be flagged as not available  
*/

KIT_NOT_AVAIL AS (
	SELECT K.id AS item
	, 	'N' AS available
	FROM Item K
	WHERE EXISTS (
		SELECT 'x' FROM KITMEMBER KM
		WHERE 
			KM.parentitem = K.id 
			AND KM.available < KM.required
	)
		AND K.itemtype = 'Kit'
	)

/*
Join the not available query above with the kit item database
and only return results back where the available flag is
different from what is currently stored; these kit items need
to updated for sync.  
Use Prolecto syntax to push updates to the database 
*/


SELECT K.id
, 	'kititem' AS recordtype
, 	NVL(A.available,'Y') AS custitem_rny_available
, 	'T' AS custitem_rny_need_to_sync
FROM Item K
LEFT OUTER JOIN KIT_NOT_AVAIL A ON 
	A.item = K.id
WHERE 
	K.itemtype = 'Kit' 
	AND NVL(K.custitem_rny_available,'?') != NVL(A.available,'Y')
ORDER BY K.id

Unlocking the Full Potential of NetSuite for Business Innovation

Our firm is an ardent advocate of the NetSuite ERP system for its remarkable out-of-the-box capacity. More importantly, we view it as a malleable technological platform that equips executives and managers to realize their business visions—without the dependency on third-party tools.

However, this capacity is not an end but a starting point. It represents untapped potential, and the possibilities lie in unlocking it. The art is to keenly listen to business needs and meticulously sift through available options to arrive at the most effective and elegant solution. Our clients frequently laud the innovative bent of our thought process and solutions.

If you found this article relevant, feel free to sign up for notifications to new articles as I post them. If you are ready to tackle your NetSuite integration or record synchronization challenge, 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