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.
Background
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.
See Related Articles
- How to Configure and Optimize the NetSuite Inventory Stock Ledger
- Understand NetSuite’s Intraday Inventory Ledger Costing Impacts
- Learn How To Craft Better NetSuite Financial Saved Searches
- Learn How To Accumulate NetSuite Inventory Balances via Saved Search
- Learn the NetSuite Item Sales Trend Pattern via Saved Search
- NetSuite Account Special Types Reference
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
Have you ever had a client wishing to commit in transit inventory to the orders – so at least they know the goods are “spoken” for and can’t be accidentally sold to another client.
It is like your Asia Pacific client – there is a considerable time delay between leaving China and Arriving to North America – but the client want to know what orders can be satisified by the in transit orders.
Your input would be appreciated as you have some really interesting blogs to read!
Hello Lisa,
These concerns are usually solved by modeling the problem leveraging item commitment, inventory on order, possibly a remote virtual warehouse so that you can create transfer orders to drive NetSuite’s native inventory in transit. We solved for this in this article while NetSuite has recently enhanced their supply chain system with freight container concepts.
Hi Marty,
do you have an idea about the classification A/B/C in item record location sublist? or in creating calculated inventory count form?
Hi Ken. I am not sure about your question as it pertains to NetSuite’s Cycle Count functionality. Have you exercised the software for a specific question?
Marty
This was super helpful, thanks.
For others on this path, you might need to add {line} as an added sort parameter depending on your transactions in order to keep everything in sync.
Hello Eric,
Please look at this related 2022 article about the sorting pattern.
https://blog.prolecto.com/2022/05/07/understand-netsuites-intraday-inventory-ledger-costing-impacts/
Marty
Hello Marty,
I have a requirement to show Op.Balance qty of an item in the saved search based on the from date put in the filter.
Any help would be appreciated.
Thanks,
Bhavin,
Please see this article and supporting video:
https://blog.prolecto.com/2023/07/15/how-to-configure-and-optimize-the-netsuite-inventory-stock-ledger/
Marty
Hi Marty,
Our company has gone round and round with NS on inventory. We are a Construction company and we were basically told by NS that they do not have the native functionality to move mass inventory to an asset account. We do not resale our inventory. We could utilize Inventory Adjustments, but that form in NS is extremely inefficient. How can I automate the problem of moving mass amounts of inventory to multiple asset accounts? With NS or with a third party software? Thank you sir,
Hello Kyle,
The key to the platform is to understand how the item references the GL asset account — and about getting this right as it is rigid. The modeling of this is fundamental to the use of the inventory and general ledger. Then, it is important to understand the GL inventory asset account acts a subledger. We often discuss with clients to avoid any journal entries into the subledger accounts so that it is easy to help explain the balances. If you need to move around balances implies another practice that I can’t see from your description.
I recommend we have a specific conversation about your situation. Sometimes I speak with CFOs to understand their larger narratives and I usually can help them find an acceptable compromise. Naturally, we have many practices and tools we can offer as well. Please reach out to me at: https://www.prolecto.com/contact-us/
Marty
Hello Marty, we have an Inventory Item with an In Transit value, but there aren’t any open transfer Orders or Purchase Orders for that item. I want to zero out the in transit quantity. Do you know how that can be accomplished?
Hello Stan,
Are you positive that you don’t have an unbalanced Transfer Order? Meaning, you may have a closed Transfer Order before producing respective item receipts?
Marty