Learn How To Accumulate NetSuite Inventory Balances via Saved Search

This article is relevant if you are looking to explain NetSuite inventory movement by accumulating the quantity and value as transactions happen over time.


Many times, when working with inventory, we need to better understand how the quantity balance or value changed over time. When trying to explain how the quantity balance went negative (underwater), it may be important to diagnose the sequence of events that led to the situation.

In my mind, I categorize these concerns under the topic of inventory movement. Meaning, we need a chronological account for how the balance changes over time with an explanation for the current balance as reported by NetSuite.

Using NetSuite Advanced Saved Searches to Account for Inventory Movement

The key to understanding NetSuite inventory ledger is to know that all item transactions which post to inventory-based general ledger accounts effectively make up the ledger balance. Thus, if we can organize our transactions in the right row order, and then use formulas to add the previous row to the current row, we can effectively produce a cumulative balance. See my article Solving the NetSuite Cumulative Saved Search Tally Challenge.

I previously wrote an article on how this was achieved: How To: NetSuite Stock Inventory Movement and In-Transit.

In this article, I seek to demonstrate further the outputs and provide additional screen shots to illustrate how this is done. I recommend clicking on each image to learn the pattern.

Get NetSuite Database and Accounting Assistance

The article is designed to show that we can indeed get at the information in NetSuite if we learn how to put saved search to work. These concepts are generally comfortable for individuals with database and accounting background. If you are looking for strong support to enhance your NetSuite experience, let’s have a conversation.

Be Sociable, Share!

Marty Zigman

Holding all three official certifications, Marty is Southern California's 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 25 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 - Google Plus - YouTube

| Category: Accounting, NetSuite, Reporting | 10 Comments


  1. Jeff
    Posted February 18, 2018 at 7:32 pm | Permalink

    This is great, but I noticed that the cumulative amount is not taking into account landed costs on item receipts. It’s being excluded by the Special Account Type criteria. Any thoughts?

  2. Jeff
    Posted February 18, 2018 at 9:30 pm | Permalink

    So I figured out it’s not the Special Account Type criteria that is keeping those out. Landed costs are InvAsset special account types. It’s the quantity not zero criteria. When I removed that, it worked. But now I’m not entirely sure why that criteria was necessary in the first place.

  3. Jeff
    Posted February 18, 2018 at 9:55 pm | Permalink

    Now I see that removing the quantity = zero criteria to include landed costs results in cumulative quantity and amount values that are ultimately correct, but get all wonky within the item receipt rows.

    You can see what I mean in the screenshot below. The quantity and amount cumulative values correct themselves by the last row item fulfillment shown, but are wrong/out of order until then.


  4. Jeff
    Posted February 18, 2018 at 10:11 pm | Permalink

    I seem to have gotten the order working correctly by adding a third “sort by” field of “Formula (Text)”. I honestly can’t tell you why that worked other than I had a hunch it might so I gave it a shot.

  5. Jeff
    Posted February 18, 2018 at 10:17 pm | Permalink

    Sorry to blow up the comments so much, but now that I’ve gone down this rabbit-hole I figure other readers with the same issue may want to know.

    My “Formula (Text)” sorting solution improved the sorting situation, but hasn’t completely fixed it. I found an item receipt sequence where the rows are still out of order relative to the cumulative amounts.

    Back to the drawing board.

  6. Posted February 19, 2018 at 6:40 am | Permalink


    Thanks for working on getting the landed cost dimensions included. I will need to think about this because those elements show up as additional GL lines. This is why I used the criteria of where there is a quantity to make sure we hit the ledger row. Thus, for now, the value column is effectively the PO rate and not the fully burdened cost (less than ideal).

  7. Jeff
    Posted February 19, 2018 at 8:27 am | Permalink

    No problem. Thank you for this helpful article!

    Why use the Special Account Type criteria vs. Account Type vs. actual account numbers for inventory accounts? I can’t figure out how one designates an account as an InvtAsset special account type in the first place. I only have one account designated that way, which makes me think I should just select it’s account number in the search criteria instead. Any drawback there?

  8. Posted February 24, 2018 at 3:59 pm | Permalink

    Hi Jeff,

    I use the Special Account Type to be sure I am on NetSuite’s inventory account in any account. I don’t though have a hard rule for this.


  9. Chris
    Posted December 29, 2018 at 6:16 am | Permalink

    Great post Marty. Is there a way to include item quantities sold within kits so the cumulative quantity equals the quantity available? Thanks

  10. Posted January 5, 2019 at 3:57 pm | Permalink

    Hi Chris,

    That is likely hard with a single search. Did you see this article?

    We now solve these kinds of situations with our Content Renderer Engine (CRE) as we can meld multiple searches together. But that takes some creativity to get there.


Post a Comment

Your email is never published nor shared. Required fields are marked *


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>