This article is relevant if you are looking to produce a view into your NetSuite based stock ledger by understanding the movement of your inventory balances.
During a recent NetSuite implementation, we optimized the purchase processes for movement of inventory between Asia-Pacific manufacturing for United States based distribution. ¬†In the processing model which we designed, and which will be the subject of a different article, I was able to leverage NetSuite’s built-in mechanism to produce in-transit inventory. ¬†In our client’s case, when they create a purchase order from their supplier, they want to see when items are in-transit from Asia to the United States when goods move as cargo slowly over the Pacific Ocean to their warehouse.
One of the challenges the client was having is reconciling their inventory balances over time. ¬†To help solve this, I created an Inventory sublist view by using a NetSuite Advanced Saved Search technique leverage work I have produced solving the cumulative balance challenge.
NetSuite Cumulative Inventory Balances Views
The image below illustrates the view. ¬†The key is to produce two key definitions that explain the inventory on hand balance:
- Stock Ledger: controlled by NetSuite’s general ledger “inventory” switch.
- In-Transit: controlled by a NetSuite “In Transit” general ledger account. ¬†Here, we are using an account’s Special Account Type: “InvInTransit” (see image)
The view is very convenient because the information lives on the item card. ¬†Otherwise, you would need to use a NetSuite provided inventory activity report.
How to Create the NetSuite Cumulative Inventory Balance Saved Search
Using a Transaction based saved search, there are two key parts to getting the inventory balance to accumulate with each subsequent transaction:
- Criteria: Use the general ledger account with posting = true and an account special type.
- Results: Use a special Oracle Analytical function to summarize the quantity with each transaction
The two images help you understand how to craft the search. ¬†The tricky one is the cumulative balance in the results column. ¬†See ¬†related article¬†for more background.
Linking the Saved Search to the Inventory Balance
Once your saved search is producing the results you need, then the final step is to link it to the Item record. ¬†Here are the basic steps to add a sublist to a NetSuite record:
- Saved Search Public Sublist: Activate the switch, located in the upper right corner, to indicate that the Saved Search can be used in a sublist view. ¬†Make your NetSuite saved search Public.
- Saved Search Filter:¬†While obscure, the key to getting your saved search to link to your item record is to define the very first Available Filter option to reference “Item”. ¬†More generally, have the first filter¬†definition¬†be a saved search field that links to the “host”. ¬†Think of the “host” as the record that will present your sublist. ¬†If your host record is item “ABC”, this action works on your behalf as if you defined item “ABC” in the item filter definition when you execute the search in typical list fashion.
- Define Subtab: In Customizations, Forms, Subtab, define your subtab and link to Item.
- Define Sublists:¬†In Customizations, Forms, Sublists, reference your newly defined saved search against Item.
Work with Superior Talent for your NetSuite Innovations
One of my most favorite things about NetSuite is the platform. ¬†Meaning, I love the ability to configure and customize the NetSuite system to meet business requirements. ¬† ¬†In this article, without needing to write any code, I was able to “point and click” my way to a useful capacity. ¬†The key to getting more out of your NetSuite account is to demand what you want and work with individuals that possess a strong understanding of business, the NetSuite platform, and software application development. ¬†This is my firm’s core competency. ¬† If you have a challenging NetSuite concern, let’s have a conversation.