6 Replies Latest reply on Jun 20, 2012 2:57 PM by philmodjunk

    Stock List Multiple Warehouses

    ThomChase

      Title

      Stock List Multiple Warehouses

      Post

      Dear All, 

       

      I am designing a little tool to keep track of our stock in FM. 

       

      I have three tables

       

      PRODUCT

      STOCK_MOVE

      LOCATION

       

      The product table keeps track of the products with their name, type, manufacturer etc....

      The stock move table is induvidal records for each move. It contains a qty_in, qty_out and a c_blance (qty_in-qty_out)

      The location is the list of the warehouses and vans. 

       

      I have mastered the stock move bit and adding in the name of the warehouse as I go with a simple script. 

       

      What I want to do is now show the c_balance for each location. The critical part here is to do this with (if possible) with out creating induvidual fields for the locations or even worse relationships for each loaction. It needs to be able to handle the edition of new locations on the fly. 

       

      Is this possible?

       

      Can you do a field somewhere like this.

       

      Sum (stock_move::c_balance if location::name = .......

       

      I'm stuck.

       

      Many thanks

       

      Thom

       

        • 1. Re: Stock List Multiple Warehouses
          philmodjunk

          The basic set up you have here looks very similar to the "inventory Ledger" approach to inventory managment that I have recommended to many different folks here in the forum.

          You only need a summary field to compute the total fo c_blance. You may want two, one set up for use as a running balance in a "ledger view" of your data so that you can see quantities on hand rise and fall. (Often noting that a running balance for a given product approaches zero repeatedly is an important indicator that you need to increase your re-order point in order to insure that you don't run out of product and be unable to fill an order.)

          Define sTotal as a summary field that comptues the total of c_blance for your report.

          Set up a list view layout with sub summary layout parts. If you set up a sub summary part "when sorted by" a location field (either a field in Stock move or a related field in Location), you can put the location name field in this sub summary part to serve as a sub header for that location.

          If you add a second sub summary part below it, "when sorted by StockID--another field you should have in Stock Move, you can put sTotal in this sub summary part to show the total on hand for that item.

          The body part can be deleted from this layout as you don't appear to need to list the individual stock moves--just the totals for each item.

          If you then sort by location and then by StockID, you'll get lists of quantities on hand for each item at each location.

          • 2. Re: Stock List Multiple Warehouses
            ThomChase

            Hi Phil, 

             

            Many thanks for this. Two quick questions as I try this. 

             

            Firstly can you point me to some of the other posts on this?

             

            Do I build this report in the Stock_Move table or in the Product table?

             

            T

            • 3. Re: Stock List Multiple Warehouses
              philmodjunk

              This report will be built on Stock Move.

              You can enter "inventory Ledger" in the search the forum box at the top of this screen and see what thread are found with this phrase.

              • 4. Re: Stock List Multiple Warehouses
                ThomChase

                Got it. 

                 

                Seems to be working well. I will implement it in the main database now. 

                 

                Many thanks

                 

                Thom

                • 5. Re: Stock List Multiple Warehouses
                  ThomChase

                  Actually there are two small extra questions.

                   

                  Can I display the stock holdings in the various locations under the product record in product.

                   

                  Finally can I choice just one location to show in the report we've just created. Would I use a perform find based on a location dropdown list global field?

                   

                  • 6. Re: Stock List Multiple Warehouses
                    philmodjunk

                    2) what version of FileMaker do you have? With FileMaker 12, I've worked out a nifty way using ExecuteSQL to do this.

                    Setting this up in Filemaker 11 and earlier is also possible but requires a very different and much more complex method with relationships and summary fields or aggregate function calculations to do the same thing.

                    Yes you can. One of the nice things with summary reports is you can get a lot of different reports by performing differnt finds and/or sorting in a different order. (if a sub summary part specifies a "sorted by" field not used in the current sort order, it disappears from view.)

                    This script could use your global field for location. I'll call it globals::gLocation.

                    Go to Layout [Summary report layout (stockMoves)]
                    Enter Find Mode []
                    Set Field [StockMove::Location ; Globals::gLocation ]
                    Set Error capture [on]
                    Perform Find[]
                    Sort [Resore ; no dialog ] //sort by location, then by StockID even though all found records are from the same location

                    You might also use this for the set field step if your relationship supports it:

                    Set field [Location::LocationName ; Globals::gLocation]