Inventory system - a different way?

[spankalee]

I'm developing a custom FM inventory system for a friend. After reading around about different ways to do it I'm left with a few questions, and have an idea about a slightly different way to do it that I'd like to get feed back on.

An inventory system that just keeps a table of items and their count is frowned upon, and for good reason, it's hard to tell if the data got corrupted. So just about everyone will tell you to do a transactional system. The inventory is really a line-item database of credits and debits, if you will, from the inventory. Calculating the current stock is a simple Sum().

I see my line items going into different DBs. OrdersInLineItems and OrdersOutLineItems, so the current inventory becomes a sum of two Sum()s. But how do invventory recounts work? Once that count is done every transaction before it should be ignored. I've seen suggestion the the count should be entered as an adjustment to the current inventory numbers, but then if something is altered before that count the inventory level will change. Is this how it's normally done?

The solution I thought up was to have an Inventory table that just kept the current inventory levels. This table would be updated every time a shipment was recieved or went out, and for every recount. A script would be available that would re-calculate the inventory db from the other three if necessary. This way calculations wouldn't need to be done just to see what the current stock was.

Any comments?

[Anatoli]

That is what I recommend.

If you keep separate "Transaction" file with all transactions recorded you can always re-summarize and process that.

To see current stock level on Item record will be instantaneous. If user want to compare that last number to result from all transaction on that item calculation can do that.

[spankalee]

The problem I see with a transaction table is how do you reset the inventory after a recount? What you need is a Sum() of all transactions that occured after the last recount, plus the recount ammout. I can't see how to do this in a calculation field.

[kennedy]

In our systems, one table is for transactions... dates and additions or subtractions; another table is for inventory counts, date and amount counted. Current balance is the most recent inventory count plus the sum of the transactions with date greater than the date of the inventory count. Lossage or scrap is the delta between an inventory count and balance that would have been computed right before that inventory count.

[LiveOak]

In a transactional approach, ALL old transactions are locked. Just like an month end close in an accounting system. Corrections based upon physical counts are entered as a adjustment transactions. Once closed, historical transactions are NEVER altered or deleted!

-bd

[LiveOak]

I like that approach, as long as the old transactions include an ajustment (for loss, scrap or whatever) that makes the old transactions equal the latest physical inventory count. This way if the physical count number is lost, it can be accurately created. The idea of making the current inventory level the sum of a count plus recent transactions gives both an accurate number and faster related computation of this number.

-bd

[spankalee]

kennedy - how do you do that calculation? I think I can see how to do it in a script that would fill in the values for an inventory count database, but how would you do it in a calculation field, to make the value 'live' (if this is what you're saying you're doing)?

LiveOak - I also like locking the all old transactions in the transactional approach. This is what I was worried about, that a transaction preceeding a physical count would be altered and then the delta value would no longer be valid. Whould you go about locking by migrating the transactions to a seperate database that wasn't editable?

Having an inventory table that acts as a cache of the inventory counts seems like a reasonable idea to me, but maybe I'm more concerned than I should be about the performance hit from calculating the inventory.

[Edward Newman]

In the system I wrote for our office the list alone would not work well. I devised a system that uses a transaction list to provide an audit trail to be referenced in the future, but also maintains a value for each warehouse in my product database. This requires the use of scripts for each transaction. The scripts are in the transaction database and start with the creation of a new record, once all of the information is entered, it posts to the products table, closes and locks the record. There is also a cancel button to exit out and delete the record from the transaction log. This approach has worked well as it does not require any sums or calculated fields.

[kennedy]

Yep, you have to decide your data access pattern to decide what's best. Do you mostly just record transactions, and rarely determine inventory levels? Or do you often determine inventory levels, but have fewer transactions? And so on.

In the planning systems that I write, the access pattern is way beyond anything FMP could handle... we have to code in C++ or similar. But for a POS system for a small Proshop, FMP should do just fine... and I can afford to wait for it to do the inventory calcs the few times I need them.

[BobWeaver]

I would probably create a daily starting inventory record based on the previous day's starting inventory and the previous day's transactions. Then, the current qty on hand would be summed from a self-join relationship based on the current date. When a true inventory is done, this value can be used to override the calculated inventory record. This should be reasonably fast since it doesn't have to summarize any more than one day's records.

[jasonwood]

I am failing to understand why the inventory methods you are discussing are better than having a simple number for each item.

If you code it properly, I can't see how the the number could ever go off.

Here's a brief summary of how my invoicing system works (maybe someone could point out the weaknesses for me?)

User clicks "Process Invoice". The system goes through all line items and does the following: evaluates the difference between qtyProcessed (usually zero) and qtyShip (the qty you are selling--or returning). It then subtracts that amount from the inventory database for that item and adds that number qtyProcessed.

If a staff member changes the quantity, there is no problem because the line item knows the quantity that has been processed. Next time it is processed it will only process the change.

The line item even remembers which sku was processed, just in case the user decides to change the sku. The system will notice that this has happened and will take the necessary actions to clear the inventory changes for the previous sku and then process the new item.

Also, some items are bundles (eg: a ten-pack of cd-r's). For these products, the system looks in the file to see which alternate sku is used for inventory (and the quantity), and takes the necessary action in that sku. This would be difficult in a calculation/summary based inventory.

It's also not a problem if the user forgets to process the invoice. All invoices are automatically "reprocessed" in the morning, right before they are locked permanently.

... am I missing something? Isn't this simpler and just as effective?

[BobWeaver]

It shouldn't go off, but sometimes it does. Occasionally someone enters a wrong stock number or some other mistake. Or, the computer crashes. The methods given here are transaction based. So there is a record of every transaction, and an automatic audit trail to help reconstruct things if the inventory quantity in the database gets totally out of whack with the real number. Other methods may work too, but this is essentially the balance sheet and income statement type approach that accountants have developed over many years. So, I assume that they have very valid reasons for using this method.

[jasonwood]

If they enter the wrong stock number, there'd be a problem either way. That's one reason I prefer using barcodes.

But assuming there is a problem due to a crash or any other reason, would the invoices and PO's themselves not be sufficient for the record of "transactions"? Couldn't you simply have a script that rebuilds your inventory based on invoices and PO's? Or is that essentially what you've been talking about? (My interpretation is that you guys are suggesting a separate transactions database... is that right?)

[BobWeaver]

I wasn't suggesting a separate transactions file; just use the invoices.