1 2 3 Previous Next 43 Replies Latest reply on Nov 1, 2013 1:42 PM by philmodjunk

    Managing Inventory using a Transactions Ledger

    philmodjunk

      Title

      Managing Inventory using a Transactions Ledger

      Post

           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----<TransactionLog>----Products

                          

                               Invoices::__pkInvoiceID = TransactionLog::_fkInvoiceID
                               Products::__pkProductID = TransactionLog::_fkProductID

                          

                               (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:

                          

                               Customers----<Invoices----<LineItems>------Products.
                               (In the FileMaker 12 invoices starter solution, they call LineItems "InvoiceData".)

                          

                               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.

                     

            

        • 1. Re: Managing Inventory using a Transactions Ledger
          henryhu91

               Thank you so much for taking your time to share this post, Phil.

               I've a few question on your post regarding of the Transaction log table.

               So "Transaction Table" will be used as Quantity Inventory for "Product" table?

               For example, if I have 3 laptops in my Product Table, Laptop will be stored in Product Table and 3 quantity will be stored in "Transaction table"?

               what If I have US inventory and China Inventory with multiple inventory locations in each US and China, should I create US in, US out, China in and China out? how can we get multiple location log?

               Thank you again Phil.

          • 2. Re: Managing Inventory using a Transactions Ledger
            philmodjunk
                 

                      if I have 3 laptops in my Product Table, Laptop will be stored in Product Table and 3 quantity will be stored in "Transaction table"?

                 The key is to track the changes to your inventory. If you manufacture 3 laptops, you would create a new record in transactions that logs the increase in inventory by putting a 3 in the "QtyIN" field and by entering the ID number for your laptops into _fkProductID. If you then sell 1 of these laptops, that change in inventory would be logged by creating a new transaction record that identifies the product and which puts a 1 in the "QtyOUT" field.

                 Note that any change to your inventory must be logged in transactions. There are any number of events that can produce such a change.

                 To get the current Qty on hand for any given product, you'd use either sBalanceRunning or sBalance--Depending on the context and exactly what you want to see. You can even use a script triggered each time you save a value in QtyIN or QtyOut to update a number field in Products to keep an updated inventory count that does not have to continually recalculate the summary field.

                 

                      what If I have US inventory and China Inventory with multiple inventory locations...

                 I would not use separate quantify fields as that would cause a number of problems when it comes to reporting inventory and also would require modifying the design of your table each time you add a location. Instead, add a location field to the transaction log. If you then ship product from one location to another, you create two new records in the transaction log. The first logs it out of the location from which the product was removed and the second logs it into the new location that has recieved it.

            • 3. Re: Managing Inventory using a Transactions Ledger
              henryhu91

                   that really open up my mind with Inventory. Thanks Phil.

                   how can I log if I have 1 serial numbers for each of 3 laptops that I mentioned above?

                    

              • 4. Re: Managing Inventory using a Transactions Ledger
                philmodjunk

                     If they are exactly the same product, they should have identical product ID's and one transaction log entry with a Qty field value of 3 can log them in or out of inventory.

                     If they are different and you need to track them as individuals, you'll need to log them as three separate transaction records with a Qty of 1. If necessary, you may need to add a serial number field to your transactions table.

                • 5. Re: Managing Inventory using a Transactions Ledger
                  henryhu91

                        

                       They are same product ID and same product. for example, I have 3 same laptops with 1 ID and they all have different Serial Number 001, 002, 003. let just say I sold 001  to A company but I rent 002 to B company and  003 is in Zone A, Lot A, Bin 3. I created new field called Serial Number. 

                       Please correct me if I'm wrong. It's what I've set up so far. 

                       Transaction Table

                       ID (auto enter serial), Product ID (unique ID for parts and assemblies), Serial Number, Quantity Out, Quantity In, 

                       Inventory (US or China Inventory) I need to track and sum up total quantity for both US and China.

                       Transaction Type (to describe either this quantity is increased by Production or Receiving from China or Purchase or Assemble or Disassemble or so on)

                       Location 1 (to track specific location of the Items), comment (for Note purpose)

                  Do I need separate table for locations of each items? 
                       Thank You

                        
                  • 6. Re: Managing Inventory using a Transactions Ledger
                    philmodjunk
                         

                              I sold 001  to A company but I rent 002 to B company and  003 is in Zone A, Lot A, Bin 3.

                         These would then be three distinct records in the transactions ledger.

                         You still have just one table, but you can perform finds on this table and sort your records any number of different ways.

                         For example, you can sort your records by the laptop's ID, then by Location to get inventory totals at each location. The layout itself might be set up as a list or table view to show all transactions, or there may be sub summary layout parts used to visually group your data any number of different ways. You can even set up sub summary layout parts with summary fields and no body layout part to show just the totals for each product at each location.

                    • 7. Re: Managing Inventory using a Transactions Ledger
                      henryhu91

                           Ok Thanks Phil. 
                           DO I need three fields to log  Zone A, Lot A, Bin 3  in order to search it back easily?

                      • 8. Re: Managing Inventory using a Transactions Ledger
                        philmodjunk

                             Possibly. You definitely need at least one. You'd need three fields to provide a break down of inventory values by bin.

                        • 9. Re: Managing Inventory using a Transactions Ledger
                          henryhu91

                                

                               Gotcha Phil. Thanks.

                               What if I order 3 laptops, but the order hasn't received and I still want to record that 3 quantity, but doesn't want to add up right away to c_US_Quantity In (If (Inventory= "US"; Sum (Quantity In) - Sum (Quantity Out)) yet. How can I display the quantity in Product Layout until users receive the order? 
                               Should I create another field to store the quantity? 
                               For receiving parts, would it be better to create another table or keep using Transaction table?

                                Please advise me Phil. 

                          • 10. Re: Managing Inventory using a Transactions Ledger
                            philmodjunk

                                 Who are the "users" the customers or another location to which a product is being transferred?

                                 Often, you have two possible inventories--the physical inventory of every item physically present and the virtual or functional inventory of all items present that have not yet been sold or otherwise reserved for a specific customer. A status field in transactions can record whether or not an item sold has actually been shipped.

                            • 11. Re: Managing Inventory using a Transactions Ledger
                              henryhu91
                                   

                              Who are the "users" the customers or another location to which a product is being transferred?

                              users are staffs that order the parts and waiting to receive in US location.

                              For example, if staffs order 3 laptops from online, that 3 laptops will not be received it right away to US. may be it takes 3 business days for shipping to receive in US and after 3 days, users will go record of Laptop and will confirm by click scripted button for receiving that 3 laptops then US quantity will add on 3. 

                              During the process, how can I display the quantity that would be on its way?

                              • 12. Re: Managing Inventory using a Transactions Ledger
                                philmodjunk

                                     That is an interesting question. Perhaps a special location "intransit" could be used, or that status field that I mentioned in my previous post might show that an item is instranist.

                                • 13. Re: Managing Inventory using a Transactions Ledger
                                  henryhu91

                                        

                                       but those quantity are belong to US quantity in as well and We can pre calculate how many in total. just like in banking system available balance and present balance?

                                       what if I create "In Process" field to input quantity for those are on their way?

                                       I can't visualize how staffs will be able to see those transit quantity in value lists on Product Layout in order to choose which one of them they have received to add quantity. 

                                  • 14. Re: Managing Inventory using a Transactions Ledger
                                    philmodjunk
                                         

                                              but those quantity are belong to US quantity in as well and We can pre calculate how many in total. just like in banking system available balance and present balance?

                                         At a given instant of time, the item cannot be both in the US and in transit. It would be in one or the other. When the item is received, it would move from "in transit" to "Received". That could be logged as a change in location or a change in status.

                                         This is where you'll need to decide on the best approach. You can log an item out of one location by transferring it to the "in Transit" location when it is shipped out and log it in out of "In Transit" and into the receiving location when it is received or you can log it out of the original location and into the receiving location, but set a status field on the receiving location to show that it is "in transit" and has not been received yet. The status would then be changed when the item is received.

                                    1 2 3 Previous Next