3 Replies Latest reply on May 24, 2015 9:16 AM by philmodjunk

    inventory report

    AvtandilKiladze

      Title

      inventory report

      Post

      hello,

      i'm making inventory DB, to track product transaction and see balance in multiple warehouses.

      i have 3 table:

      TB1 (warehouses): wharehouse_id .... with: wh1, wh2, wh3 etc...

      TB2: (Products) Product_id .... with: A, B, C, D etc...

      TB3: (Transactions): Transaction_id. warehouse_id_sender, warehouse_id_reciever. Product_id, quantity

      what i want to do is:

      make report to see for example: 

      wh1 productA Sum_quantity

      wh1 productB Sum_quantity

      wh2 productA Sum_quantity

      wh2 productB Sum_quantity

       

        • 1. Re: inventory report
          philmodjunk

          TB1 (warehouses): wharehouse_id .... with: wh1, wh2, wh3 etc...

          It would be better to use just a serial number 1, 2, 3, etc, A second field can then serve as the name or other visible Identifier (whatever you would see on a sign on the side or out front of the warehouse). You can then manage changes to how the warehouses are identified without breaking links to your transactions.

          TB2: (Products) Product_id .... with: A, B, C, D etc...

          It would be better to use just a serial number 1, 2, 3, etc. A second field would then be the SKU and another can serve as name or description. SKUs and Names sometimes change so you don't want to use values that might need to be changed as the match field in a relationship to transactions.

          TB3: (Transactions): Transaction_id. warehouse_id_sender, warehouse_id_reciever. Product_id, quantity

          How do you use this table to record a transaction when it is not from one Warehouse to another? Such as when shrinkage occurs or new product is received from a supplier (or manufacturing) or product is sold and shipped to a customer?

          I suggest a table like this:

          _fkProductID, _fkWareHouseID, TransactionType (Sale, Received, Shrinkage, Transfer...) QtyIn, QtyOut, cBal (QtyIn - QtyOut), sBalance (Summary, total of cBal )

          To transfer product from one Warehouse to another uses two records in Transactions. One with the qty recorded in QtyOut to record removing it from one warehouse and a second with the same qty recorded in QtyIn to record the reception of this shipment in the second warehouse. A sale, recieved shipment, shrinkage, etc. would use just one transaction record.

          This then allows you to use a Summary report and other methods based on the Transactions table to show the Qty on hand (sBalance) for one or multiple products. (A Bookkeeping Ledger type view with transactions grouped a number of different ways depending on how you sort the records is also possible.)

          A thread with more on an InventoryLedger system: Managing Inventory using a Transactions Ledger
          A thread on summary reports: Creating Filemaker Pro summary reports--Tutorial

          Note that you may find it useful to have two Summary fields that compute the total of cBal, one with running total specified and one without.

          Caulkins Consulting, Home of Adventures In FileMaking

          • 2. Re: inventory report
            AvtandilKiladze

            First of all, thanks for your response, it helped me a lot.

            How do you use this table to record a transaction when it is not from one Warehouse to another? Such as when shrinkage occurs or new product is received from a supplier (or manufacturing) or product is sold and shipped to a customer?

            when its new product i'll add it to warehouse as income. i don't need other transaction because we don't sell products. it will stay in our warehouse. for fault i will have 1 warehouse where it will be counted.

            i made report, it works good. + on warehouse layout i made portal from material list, by this i can see what materials are in specific warehouse. i made balance by ExecuteSQL. so it works very good. 

            now i have one problem, Some of my Products have serial number. in product list i have field SerialNumberStatus (Yes/No). what i want to do is in report to see materials summed if Serial Number is NO and to see materials with serial number by 1 quantity. 

            • 3. Re: inventory report
              philmodjunk

              i don't need other transaction because we don't sell products.

              You must have a very strange business if you neither buy, sell, ship, consume, lose, damage or have stolen products stored in your warehouse.  Selling product is just one example of a transaction that does not involve transfer from one warehouse to another within your system of warehouses.

              While there are other ways to get subtotals showing quantity on hand, such as executeSQL, they will be a more complex way to accomplish the same results than if you use one transaction record to add material to a warehouse and a second transaction to remove that material when it is a warehouse to warehouse transfer.

              What you describe sounds like yet another ExecuteSQL query will be required with your current system. With what I am recommending, it might be as simple as performing a different find, but using the same report layout as before.