How to Track Drop Ship Inventory Status in NetSuite
Tracking Drop Ship Inventory Status
NetSuite’s Inventory Management feature is very useful when it comes to tracking your in-house inventory. However, tracking the status of a drop ship is not readily available. Larger vendors may have EDI integrations in place to provide you with the information. For small to medium sized vendors, this information is delivered as a manually exported file such as CSV, TEXT or XML.
One of our premium furniture retail clients came to us with their issue of tracking drop ship inventory statuses. They currently have an eCommerce page set up and track all their in-house inventory via the inventory module in NetSuite. Drop ship vendors send them a CSV or Excel extract of their current inventory status via email on a daily or weekly basis.
Here are some of the pain points:
- Our client does not have control over how these files are generated.
Every vendor has their own column headers and formats. Some may have multiple headers, column header values may not be the same, and headers are typically in different order
- Vendors would include the status of all items.
It is very rare that a client will sell 100% of a vendor’s offered items. When these exports arrive to the client, it includes statuses of ALL of their items
- Due to manual external data processing and entry into NetSuite, the status received becomes out of date and prone to user error.
For our client, some of the information is displayed to the customers on their eCommerce site. It is critical that they display timely data
Few critical road blocks we discovered are:
- Vendors have different file formats.
Since NetSuite can only process TEXT files (CSV, Pipe Delimited, XML, etc) those sending in Excel files will still need someone to convert them.
- NetSuite is unable to differentiate in-value comma vs delimiter comma
This has been an issue for some time now since NetSuite sees a CSV as normal text file. Since they have no control over how they generate the CSV, we had to have someone manually convert them into a pipe delimited file
We knew from the start that manual user intervention was unavoidable. Our goal then became making sure other steps were automated for them while giving them full control over how the data is processed within NetSuite.
Our final product included the following features that allowed our client to have full control over HOW each vendors’ data was processed while being able to process them quickly and efficiently.
- Client can set up a mapping for the export file to a NetSuite field
This process allows them to easily set up and update new or existing vendor files to the system. It tells the system what column maps to which field
- Client can set up the data processing of mapped columns
This is where the true flexibility magic happens. There are so many logic permutations of business rules that coding it will become unmanageable. We came up with a solution to use a user defined NetSuite Search Formula to define business logic.
- The process handles identification of matching NetSuite Items to drop ship inventory status update
Once the mappings are set up per vendor, everything is automated. The user simply loads the file as they get them.
- Transparency into processing
The solution also includes the ability to run the file in test mode. This allows user to first SEE what the values per each item would be before running it to update the records. In addition, the solution will report any errors that may have occurred during processing for manual interventions
Our client has reported to us that, what used to take days now takes minutes in export file processing time. The beauty of it all is that this solution can be used for ANY external data source coming into NetSuite. Do you have similar problem you are facing? Please contact us!