Pages

2009/10/20

Searching on Transactions affecting Inventory

It can often occur that you want to run a Saved Search to find all transactions affecting inventory, like when maybe you want to see the consumption history of a certain item, or whatever.

In most accounts, this can be trickier than it sounds. Your first reaction may be to simply add a criteria on Transaction Type and select the transactions that normally affect inventory - Inventory Adjustments, Transfers, Item Fulfillments, etc.

But (and if you didn't know this, this is important to know) there can often be times where you booked an Invoice, or a Bill, or a Credit Memo without first creating the SO/PO/RA and then the corresponding Item Fulfillment or Item Receipt (i.e., a "standalone" Invoice/Bill/Credit Memo).

This means that your Invoice/Bill/Credit Memo is affecting inventory. So ok, you may think, you need to add these Transaction Types to your search. Nope, wrong. Cause then you'll be double-counting your inventory impact in cases where you have a Fulfillment AND an Invoice. Ok, so then filter on just Invoices? Wrong again, then you'll be missing out on all the Transactions that have been fulfilled but not invoiced. What's a boy (or girl) to do?

Well, you can follow this neat tip. Add the following criteria:
Is Posting = True
Account = Inventory Asset (make sure here to actually select the Inventory Asset account you're using, as per your Chart of Account. There may be more than 1, which is alright).

This way, you are 100% certain to grab only transactions affecting your inventory, because you're looking at the inventory asset account. That doesn't lie. If something goes in or out of your inventory, it will definitely have an impact on your inventory asset. And by saying Is Posting, you are basically weeding out Sales Orders and Purchase Orders and other non-impacting stuff.

So a quick example of a complete search using these criteria might be:

Transaction Search
CRITERIA
Is Posting = True
Account = Inventory Asset

COLUMNS
Date
Type
Number
Item
Quantity
Item Rate
Amount

Enjoy!

2 comments:

  1. so simple and functional tnxs for the post! I added the "created from: type=sales order" to bring only item fulfillments and retrieve the net sales amount, but when adding the column "Created from: amount" it returns the Main amount though I set in criteria "Created from: Main line=F"
    Do you know if there is a way to add the net sales amount in this kind of saved search?

    ReplyDelete
    Replies
    1. Hi Jonathan,

      This will end up being more complicated because, indeed, Create From joins will only ever return main line info, so you cannot get amount from the line.

      I assume what you're trying to pull is an inventory movement with sales info report, but fundamentally inventory movements do not record sales values. In this case it might simply be better to pull a report looking at Account Type = Income and Expense to get, essentially, Invoices and Bills. The Income and Expense values would be your sales and purchase values.

      There are probably other ways to go about this, depends exactly what you're looking for.

      Delete