Jump to content
Sign in to follow this  
Kent Searight

Perpetual inventory

Recommended Posts

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!

Share this post

Link to post
Share on other sites

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.

Share this post

Link to post
Share on other sites

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?



Share this post

Link to post
Share on other sites

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

Share this post

Link to post
Share on other sites

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 by Guest

Share this post

Link to post
Share on other sites

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.

Share this post

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

  • Create New...

Important Information

By using this site, you agree to our Terms of Use.