Managing Inventory using a Transactions Ledger
How to best manage inventory comes up from time to time and the thread I was using as a referral source for the following approach has gone AWOL so I am posting my own description of the method here so that I can refer others to it the next time the question arises.
Managing inventory is usually more than just answering the question: "How much of each product do I have in stock?" While it's certainly the most immediate question you need answered, tracking how your inventory levels rise and fall over time is just as important if not more so in the long run. The ideal stock inventory ties up a minimum of your working capital in product sitting around waiting to be sold, delivered, consumed etc without ever actually running totally out.
Watching your levels change can tell you things about the frequency and size of your orders or production rates that replenish your stock levels. If you see that the amount on hand is gradually increasing over time, you can reduce the size and/or frequency of your new orders. If you see that a given products stock level frequently gets close to zero, you can increase the quantity or frequency of your re-order to reduce the chance that you might have to tell a customer that you are unable to fill their order.
One way to manage inventory so that you can watch the levels change over time is to log each change in inventory in separate records of a table that functions as a "Transactions Ledger". Such a table would need a set of fields such as these:
|_fkProductID||The ID number of the item whose change is being logged in a given record. It typically is set up as a foreign key (_fk) that links to a table of products|
|TransactionDate||Date of change|
|TransactionType||Field identifying why the inventory has changed: Sales, Consumed, Received, Returned, Shrinkage, Error Correction are some of the possible reasons why you need to log a change in inventory.|
|QtyIN||Number field for logging any increase in inventory.|
|QtyOUT||number field for logging any decrease in inventory.|
|cBal||calculation field, QtyIN - QtyOUT not shown on any layout but used to compute inventory totals.|
|sBalanceRunning||Summary field, Total of cBal. Set up as a running total that restarts "when grouped by _fkProductID".|
|sBalance||Summary Field, Total of cBalance--but with no "running" option specified to show Qty on hand for a given item from a related table context such as a related products table.|
|_fkInvoiceID||Number, serves as a foreign key to the invoice table. (More about this field later.)|
Now you can set up an Inventory Ledger layout. Create a List View layout with all of the above fields except cBal and sBalance in a single row in the body with sBalanceRunning as the far right hand field. You can include a Description field from a related products table to provide an easy way to see the name of each item, not just its ID number. This field could be put in a sub summary layout part to serve as a sub head that separates the groups of transaction records for each item in your inventory. You can also include a "reorder level" field from products to show the minimum balance before more product should be ordered to replenish stock.
If you sort the records on this layout by _fkItemID, you see a running balance down the right hand side that restarts at zero for each item in your inventory. To log a change in inventory, you simply create a new record on this layout, enter an item ID (can be selected from a drop down list), the date, and the amount added or removed from your inventory.
If you are selling products and want to update inventory each time that an item is added to an invoice, you can set up these relationships:
Invoices::__pkInvoiceID = TransactionLog::_fkInvoiceID
(See the first post of this thread for an explanation of the notation I am using here: Common Forum Relationship and Field Notations Explained)
If you are familiar with the standard set of table for an invoicing system, this should look very familiar. The typical data model for an invoicing database looks like this:
This then shows us a very nice way to incorporate a transaction log table into the typical invoicing system. We can add the needed fields to the existing LineItems table, using the Qty field already present in the LineItems table for QtyOUT to get the LineItems table to also serve as our TransactionLog table to avoid having to enter sales driven changes in inventory a second time. You can still set up a layout for the Transactions Ledger, but each time you fill out an invoice, each line item on the invoice is creating a record in the transaction ledger documenting the decrease inventory automatically.
To log any changes in inventory not due to the sale of product, you'd still use the Inventory Ledger or some other such layout to generate the needed transaction log entries.
Note: the Inventory starter solution released with Filemaker 12 uses a Transactions Log table with some, but not all of these features.
Note: This thread is now more than 3 months old. Comments posted to it no longer cause it to appear in Recent Items and thus, I am unlikely to notice when comments are posted to it. If you were referred to this thread from your own thread to answer one of your questions, please post back to your orignal thread instead of here. If you found this thread after searching the forum, start a new thread and include a link to here in it in order to post your comments or ask a question.