Overview
In the world of inventory management, maintaining accurate and up-to-date information is paramount. For businesses using NetSuite, the ability to not only see their current inventory balances by location but also to see the impact of each affecting transaction over time provides a dynamic and real-time view and history of stock levels.
In this blog, we will explore the concept of adding running totals to a saved search to allow us to see an incremental impact on inventory transactions for an item.
Understanding Running Totals
A running total is a cumulative sum that continuously updates as new data is added. In the context of inventory management, running totals offer a comprehensive view of the stock balance for an item at a particular location, considering all transactions—additions and removals—over time.
Creating the Inventory Activity Detail Saved Search
Criteria Tab
| FILTER | DESCRIPTION | FORMULA |
| Type | is any of Assembly Build, Assembly Unbuild, Inventory Adjustment, Inventory Transfer, Invoice, Item Fulfillment, Credit Memo, Item Receipt, Cash Refund, Cash Sale | |
| Main Line | is false | |
| Posting | is true | |
| Date | is on or before today | |
| Account | is any of 1200 Inventory, 1225 Inventory : Rental Inventory, 1230 Inventory : Used Inventory |
Results Tab
| FIELD | SUMMARY TYPE | FUNCTION | FORMULA | WHEN ORDERED BY FIELD | CUSTOM LABEL | SUMMARY LABEL |
| Item | ||||||
| Location | ||||||
| Type | ||||||
| Date | ||||||
| Document Number | ||||||
| Account | ||||||
| Status | ||||||
| Quantity | ||||||
| Formula (Numeric) | sum/* comment */({quantity}) OVER(PARTITION BY {item}, {location} ORDER BY {trandate}, {internalid} ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) | Balance | ||||
| Amount |
Notice that NetSuite experts added a Formula (Numeric) field to the results section and added the following formula to calculate our running total.
sum/* comment */({quantity}) OVER(PARTITION BY {item}, {location} ORDER BY {trandate}, {internalid} ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
This formula is actually an SQL statement that is passed to theOracle NetSuite database to perform a sum of the transaction quantities. We are telling the database to partition or group the records on the database by both item and location so that it will tally the quantities within this grouping. We also instruct the statement to sort the records by transaction date and then internal ID in order to identify the transactions in the order that they occurred within the grouping.
Available Filters
| FILTER | SHOW IN FILTER REGION | SHOW AS MULTI-SELECT | LABEL |
| Item | Yes | ||
| Location | Yes | ||
| Date | Yes | ||
| Type | Yes | Yes | |
| Name | Yes |
Saved Search Output
When we run the saved search, we will select an item and a location to filter the results and produce the inventory activity detail for that item. We can also add an as-of-date filter to see what the inventory balances were as of a point in time as well as what transactions impacted the inventory levels.
The field labeled Balance displays the results of the running SQL statement totals and displays the on-hand balance for the inventory item after each transaction. See the image below.
When we look at our item’s Location inventory on hand and inventory balance amount in NetSuite, we see that it matches our Inventory Activity Detail totals.
Conclusion
Implementing running totals in NetSuite case management, especially for inventory management, is a strategic move toward achieving operational excellence. By leveraging these cumulative sums, businesses can gain deeper insights into stock balances, optimize replenishment strategies, and enhance overall efficiency. The real-time nature of running totals ensures that decision-makers have access to the most current information, enabling them to navigate the complexities of inventory management with confidence and precision.
About Us
We are a NetSuite Solutions Partner and reseller with 30+ years of combined experience. We specialize in implementation, optimization, integration, rapid project recovery and rescue, and custom development to meet any business need. If you would like more information on NetSuite or need consultation for your project or implementation, feel free to contact us.
To Contact the NetSuite support team, Click Here