8 Replies Latest reply on Jan 22, 2015 7:46 PM by dejosejimpaul

    Multiple Location Inventory Set Up

    dejosejimpaul

      Title

      Multiple Location Inventory Set Up

      Post

      I'm currently working in a clothing company which have multiple warehouses and distributes clothes to several stores. Stocks are sold either from a warehouse or in a store. What I would like to do is:

      1) Track the inventory and sales of each warehouse and store
      2) Log the movement of stocks

      Any ideas how can I do this? I have attached a screenshot of what I'm trying to do hope that helps, thanks!

      Screenshot_2015-01-19_12.24.30.png

        • 1. Re: Multiple Location Inventory Set Up
          philmodjunk

          Specify the location using a field in the transaction table. If you make a delivery, create two records in the transaction log. The first record  is the "From" entry and logs the removal of stock from that location. The second record is the "To" entry and logs the addition of stack to the new location. Each store and each warehouse can be treated as a location. I suggest creating a locations table where each record identifies and documents the different stores and warehouses that are part of your inventory system.

          For an example of how this might be set up, see: Managing Inventory using a Transactions Ledger

          • 2. Re: Multiple Location Inventory Set Up
            dejosejimpaul

            Hi Phil, thanks for the reply. I have read your post in http://forums.filemaker.com/posts/a6ef951917 and it helps me get the concept but I'm getting confuse in the part where creating two records, one that logs in and one that logs out (Qty In, Qty Out). I will have a calculation like the following below to compute the QtyOnHand of each stock.

            QtyOnHand of a stock in Warehoue:
            = Sum (Purchase) - Sum (Delivery) + Sum (Pullout)

            QtyOnHand of a stock in Store:
            = Sum (Delivery) - Sum (Sale) - Sum (Pullout)

            If I have to create two records for every line items, won't I have a double entry for each item?

            Here's my updated draft:

            • 3. Re: Multiple Location Inventory Set Up
              philmodjunk

              What is a "Pull out"

              Using two records should not create the problem you are concerned about, but I don't recognize the above term...

              • 4. Re: Multiple Location Inventory Set Up
                TomaszChmielewski

                I think that the graph looks almost ok, the only thing is when calculating the Quantity On Hand they will be summarized by product id 3 times ie. ( Sum -  Sum + Sum ). I think when the database grows and there are many transactions for same products, calculating this on the fly could be demanding on the system and slow with searching and sorting the log if needed.

                Personally I would suggest a separate Inventory table linked to the Products table where one record per ID_Product and ID_location exists for Qty On Hand. This way you can look at a record from specific location and quickly get quantities from other locations.

                You can devise some clever script trigger that updates that table on stock transfer.

                If you will need to access the solution via internet, only one record has to be sent. Otherwise to calculate this, all the relevant records must be transferred, sloooow.

                I'm no expert by far, maybe Phil can elaborate more on that.

                 

                • 5. Re: Multiple Location Inventory Set Up
                  philmodjunk

                  In the example I referenced, a calculation: cBal: QtyIn - QtyOut is summed to compute a quantity on hand.

                  Getting a reasonably fast result is a concern with this method and there are two methods (which can both be employed) to speed things up:

                  Set up a housekeeping script that periodically exports the transactions to an archive file before "condensing" the transactions records by replacing a large block of transaction records with a single record with the sum of cBal for the archived records put in the QtyIn field as a "starting balance" entry for that item.

                  Use script triggers to update a number field in an Inventory table each time you log a change in inventory in the transactions table.The purpose of the transaction table isn't so much to compute Qty on hand totals--thought it can do that and should be used as a check against a number field used for that same total to ensure that your system is correctly updating that field, but to track how your inventory levels are changing over time--which can be a good tool for determining if your re-order points and current qty's need any adjustment to avoid back orders and to avoid tying up a lot of capital in inventory.

                  • 6. Re: Multiple Location Inventory Set Up
                    dejosejimpaul

                    @Phil
                    Hi Phil, "Pullout" is the transfer of stock from store back to warehouse. It's the opposite of "Delivery" - transfer of stock from warehouse to store. Maybe I can describe to you the business workflow:

                    1) "Products" are acquired from the "supplier" through a "purchase order" --> PURCHASE table
                    2) These products are then stored in the "warehoue"
                    3) The company will then perform a "delivery" / transfer of these products from the warehouse to the "stores" --> DELIVERY table
                    4) Selling of products are generally performed in the stores but can also happen in the warehoue --> SALE table
                    5) If there are products which are not being sold (not saleable) in the stores, these are being pulled out / send back to the warehouse --> PULLOUT table

                    As you might have notice, I have created a parent table for each "transaction type" or "events".

                    Calculating the inventory of the "warehouse" and "store" have different formula.

                    Warehouse Inventory = PURCHASE - DELIVERY - SALE + PULLOUT
                    Store Inventory = DELIVERY - SALE - PULLOUT

                    QtyOnHand = Warehouse Inventory + Store Inventory

                    I'm having a hard time in the DELIVERY section. I wanted to have a layout that looks like below:

                    Product        Qty          To:                From:
                    --------------------------------------------------------------------------
                    Apple             2            Store1           Warehouse1

                    The value in the "Qty" column will be added to "Store1" and that exact value will be deducted to "Warehouse1"

                    How can I have this kind of layout in my DELIVERY table? Is this even possible in my current schema?

                    @Tomasz

                    I think that the graph looks almost ok, the only thing is when calculating the Quantity On Hand they will be summarized by product id 3 times ie. ( Sum -  Sum + Sum ). I think when the database grows and there are many transactions for same products, calculating this on the fly could be demanding on the system and slow with searching and sorting the log if needed.

                    That's just what I thought but I'm planning to use the ExecuteSQL function for this. Maybe it could make a difference in terms of calculation speed? Not sure about this...

                    • 7. Re: Multiple Location Inventory Set Up
                      TomaszChmielewski

                      Love the ExecuteSQL function myself. Recently listened to the latest FileMaker Talk podcast called "Pause On Error Recap" where it was stated that ExecuteSQL function is kind of a "wrapper" for the filemaker calculation engine. This would mean that what the function does is like doing stuff the standard way, only I imagine the SQL statement gets translated into performing all those tasks of finding, sorting parsing results, etc. This brings extra overhead, although not that much the say. I guess the speed would depend on the recordset and if the values are stored vs unstored.

                      I think anyway that for DELIVERY to happen you would need an extra table for the store(s) "requests for goods from warehouse" maybe a sale's order etc. This could be generated for each store as the inventory in store goes low. I assume both are physically separate locations.

                      When the Warehouse receives this request sheet with "quantities requested" a "DELIVERY" action can be performed based on those numbers, there could be also a field "qty actually delivered" for the warehouse if for any reason the warehouse inventory is wrong and the actual "qty requested" cannot be fulfilled but mus be delivered.

                      Based on this table you can update the inventory table upon delivery/sending out of goods to the store in a batch process.

                      Of course there is also a time when the goods are "in limbo", not in warehouse anymore, not in store yet... but I don't know if that's an issue. Anyhow, some extra table where to put the: product id, qty to deliver, and maybe, store id and warehouse id would help.

                      Also it may not be that easy to resolve this just in the relationship and without running a script that takes care of the business logic.

                       

                      Here's also a very interesting topic on inventory: https://community.filemaker.com/message/169971

                      • 8. Re: Multiple Location Inventory Set Up
                        dejosejimpaul

                        @Tomasz

                        I guess the speed would depend on the recordset and if the values are stored vs unstored.

                        I totally agree with this. I'm just wondering if ExecuteSQL will be more faster than the other functions.

                        I think anyway that for DELIVERY to happen you would need an extra table for the store(s) "requests for goods from warehouse" maybe a sale's order etc. This could be generated for each store as the inventory in store goes low. I assume both are physically separate locations.

                        Are you suggesting that I divide the LOCATION table into two and have a table for WAREHOUSE and STORES? I think this would be much better and yes warehouse and store are  both separate physical locations.

                        Of course there is also a time when the goods are "in limbo", not in warehouse anymore, not in store yet... but I don't know if that's an issue.

                        This would be in consideration but I think to make the system simple, we're just gonna assume that the products being transferred goes to their designated locations.

                        Anyhow, some extra table where to put the: product id, qty to deliver, and maybe, store id and warehouse id would help.

                        I think this would be the TRANSACTIONLOG table? Would it be better if I create a separate "join table" for every "transaction type"? Perhaps a PURCHASELOG, DELIVERYLOG, PULLOUTLOG, and SALELOG?

                        By the way, I can't access the link you provided. I'm not a member yet :)