This article is relevant if you are looking to explain NetSuite inventory movement by accumulating the quantity and value as transactions happen over time.
Background
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.
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?
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.
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.
https://cl.ly/2W1D1321091Y
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.
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.
Jeff,
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).
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?
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.
Marty
Great post Marty. Is there a way to include item quantities sold within kits so the cumulative quantity equals the quantity available? Thanks
Hi Chris,
That is likely hard with a single search. Did you see this article?
https://blog.prolecto.com/2016/07/10/how-to-generate-netsuite-kit-item-members-weighted-price/
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.
Marty
Hi,
How to retrieve a php save search ?
The function InventoryBalanceSaveSearch does not exist
Hello Nicolas,
I believe you may want to check NetSuite fundamentals here. PHP is not used on the platform. And the Saved Search I demonstrated I crafted to illustrate the power of the technique.
Marty
Hi Marty,
Special Account Type is only filled by Accounts that have been system-generated, correct? in this case, i will probably have to work with standard “account type” and find a proper filtering.
Ingo
Hello Ingo,
I believe you are correct. If the Special Account Type does not give you want you need, use Account Type.
Marty