Perpetual inventory

[Kent Searight]

Hi all,

I have a simple database that tracks Inventory of unique items (IOW quantity is either 1 or 0), and Sales of these items (in line items of invoices). When an item is sold, it's inventory level is set to zero, and that's the end of that. No returns of items ever occur.

I thought it would be a piece of cake to report inventory levels at any given time because of the simplicity of the inventory (dealing with unique items, one transaction per item, all transactions final, that is) but I keep hitting dead ends.

Any suggestions?

Also, this issue has got me wondering how this could be solved in a more conventional inventory that has quantities greater than 1, replenishment of items, returns that restock into inventory, etc.

Thanks in advance!

[Ender]

If each item has an Acquisition Date (or Build Date) and a Sold Date, you can use a date-to-range relationship or date range Find to find all the items in inventory for a particular date.

For example, if you have these items:

Item Acquired Sold

ItemA 2-3-2007 5-7-2007

ItemB 5-3-2007 5-16-2007

ItemC 5-7-2007 5-27-2007

ItemD 5-20-2007 5-27-2007

You could Find items that were in inventory on 5-8-2007 using:

Enter Find Mode []

Set Field [ Acquired Date ; "<=5/8/2007" ]

Set Field [ Sold Date ; ">=5/8/2007" ]

Perform Find []

That should return ItemB and ItemC.

If you have open ranges, you'll need to use a calc that shows an Acquisition or Sold date when the corresponding field is empty.

A similar multi-criteria relationship could be defined if you use a globally stored field for the search date.

[Kent Searight]

Hey Mike,

Thanks. I feel like a dolt...the solution is soooo simple but I just couldn't get my mind to work :

I actually ended up solving it without having to store the sell date in inventory. From a layout based on an inventory TO I performed my first find for :less: the sell date in a related invoice TO (see diagram). Then I did Show Omitted only, then did a constrained search for :less: acquired date.

This whole thing did get me thinking about how one would do perpetual inventory reporting with more complicated inventory systems. Any ideas on that?

Thanks!

[comment]

I am puzzled: if the items are unique, why do you need a join table to Invoices?

[Kent Searight]

Hi Michael,

Because the solution was originally set up as Invoice - Line Items - Inventory. After I inherited the project I decided to leave some of the existing structure alone.

But you're right, because of the uniqueness of the items, a Line Items table isn't necessary.

Edited May 11, 2007 by Guest
clarification

[Ender]

I don't see it as a problem. The Inventory-

I've no experience with this business model myself, but it seems the likely structure for a company that sells items with serial numbers, like computers: Manufacturing builds the computers and assigns each a serial number. Those sit in a pool of available items until someone places and order and the item gets picked for the invoice.

The Line Items>-Order setup is best for putting together an order. The person placing the order need not know which unique item will get picked for their order, they only care that they get one (and maybe that it works). When the order is full-filled, that's the time when an actual Inventory item is assigned to an Order. At this point the Order becomes an Invoice, and everybody knows what computer went where.