Bulk Constitution of Items via Integration

eXtendTech Team
5 min readFeb 16, 2022

--

Introduction

In a previous blog article, we highlighted an item integration framework that we built. Our framework enables NetSuite users to build ‘connected’ item records from their vendors. The integration allows users to search through their vendors’ product catalogs right from within NetSuite. This allows them to find the items they wish to purchase and/or sell.

Once an item has been selected, our integration application automatically creates the desired item in NetSuite. The result is having a item record that is fully connected with the vendor item. This ultimately simplifies the ongoing maintenance required to manage and keep items up-to-date.

An example of a “connected” NetSuite item record.

We’ve seen how well this framework works for our customers that prefer to build only selected item records from their vendors. However, we are seeing more and more situations where clients would like to constitute a massive list of products offered from a vendor. This is especially true when first getting started on the NetSuite platform. To handle these scenarios, we decided to build our own simplified CSV import process that leverages our item integration framework.

Item Constitution: An Overview

One of the key features of our integration enables users to dynamically assign additional metadata elements at the time of item constitution.

As a current example, we will focus on one of our larger distributor clients. Their goal is to build connected item records in preparation for their NetSuite go-live. For this effort, they are focusing on their best selling items from their preferred vendors. Using our application, they can dynamically set their item category as well as the corresponding Avalara Tax code (for taxability purposes) at the time that they constitute a product from a vendor.

In the example below, they are preparing to constitute a single product/style that is offered in three different colors and six different sizes. The result is the creation of 19 distinct matrix item records in NetSuite. This means there will be one parent matrix item (representing the style) and 18 submatrix items; each one representing a distinct size & color combination.

Above, an example of searching a vendor’s item catalog for a product to create within NetSuite.

Using this tool, our client can automatically build the item records in an efficient manner. However, this client was looking to perform this same routine on thousands of products from dozens of their preferred vendors. For just one of their preferred vendors, they were looking to build over 850 products that would result in creating over 40,000 distinct NetSuite item records.

Just like Frank Costanza’s story about the origins of Festivus on the Seinfeld TV show, we realized there had to be another way!

Solution Overview: Bulk Item Constitution

Keeping consistent with our integrated Item Search user interface, we started by creating a standard template that could be used to bulk constitute a large list of items from a vendor.

The template itself closely mirrors the key elements of the user interface to include Product ID, Vendor, First Category and Second Category. Below is an example of such a file.

A sample of an Excel file containing a list of products to be constituted in NetSuite

Once the spreadsheet is built, we provide a simple user interface to upload the list of products to be constituted.

Unlike NetSuite’s standard CSV import feature which (as the name would imply) can only accept CSV files, our bulk constitution routine is able to accept either CSV or Excel (.xlsx) formatted files.

A simple NetSuite drag & drop area that can consume a CSV or Excel formatted file.

Once the upload of the file to NetSuite is complete, our integration application kicks-off a Map/Reduce script that runs in the background to build the NetSuite item records.

Tracking the Progress to Completion

All activities of the bulk item constitution process are logged for tracking the progress and noting any failures during item creation.

All activities of the bulk constitution map/reduce script are logged.

Once the process is complete, it sends an email to the user confirming the list of items that were created.

A sample email summary of products constituted in NetSuite.

How has this helped our large distributor client as they continue their preparation for go-live? To date, this client has leveraged this application to build more than 215,000 connected item records from their preferred vendors.

Now, the client is easily able to constitute large numbers of products. Having built the items through our integration framework enables them to automate their ongoing item maintenance as well. By establishing a scheduled workflow, they can automatically call-out for all of their connected item records to ensure the purchase prices on these products are always current. A scheduled workflow can also update item records to note availability issues or flag items that will be or have been discontinued.

Furthermore, these connected item records will also facilitate the electronic transmission of a purchase order as the PO payload that is sent will be able to reference the vendor’s part number or SKU.

An example of PO line items that are connected at a part-level with the vendor.

Conclusion

We realize that aligning item records in such a fashion can be a challenging endeavor. It often requires a significant amount of organizational process change and data alignment for those seeking to attain this type of integration with their vendors. For the NetSuite customers that are committed to this type of alignment, the results can be amazing!

While transactional integration can be partially achieved without item synchronization in place, businesses are best served by making the effort to integrate the key items they commonly transact upon.

Our experience and tools help organizations realize an efficient and cost-effective integration. It also provides the value of being electronically integrated with their key trading partners.

If you’re looking to achieve efficiencies lower operational costs through establishing Item or Transaction integrations with your key customers or vendors, let’s have a conversation to see where we can help.

This article was originally published on https://extendtech.net/.

--

--

eXtendTech Team

eXtendTech is a NetSuite solution partner that develops applications to improve efficiency, productivity, and savings within the NetSuite platform.