Managing Inventory Activity Using Running Totals in NetSuite Saved Searches

by | Dec 30, 2023 | NetSuite Fundamentals, Tech Talk


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
Typeis any of Assembly Build, Assembly Unbuild, Inventory Adjustment, Inventory Transfer, Invoice, Item Fulfillment, Credit Memo, Item Receipt, Cash Refund, Cash Sale 
Main Lineis false 
Postingis true 
Dateis on or before today 
Accountis any of 1200 Inventory, 1225 Inventory : Rental Inventory, 1230 Inventory : Used Inventory 
NOTE: In the saved search criteria above, we are only including our inventory GL accounts for Posting transactions. 

Results Tab

Document Number      
Formula (Numeric)  sum/* comment */({quantity}) OVER(PARTITION BY {item}, {location} ORDER BY {trandate}, {internalid} ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) Balance 

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


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.


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

Join our mailing list to stay up to date on the latest NetSuite solutions.