10 Replies Latest reply on Mar 23, 2017 9:27 AM by fivos@macclub.org

    Inventory or stock relations problem

    andresen

      Wonder if I'm on right track!

      What relations are missing if I in order, pic the various product i use and in the same time the count for that product is deducted from the product count (or a stock table or some kind of inventory)?

       

      i have order, orderlineitem, product, purchase, suppliers, customers and contacts table.

       

      order---<orderlineitem>---product

      product---<purchase

      and

      product---<orderlineitem

       

      purchase---<product

      and

      purchase---<orderlineitem

      and

      purchase>--suppliers

       

      I'm not sure all the relations are necessary.

       

      When I buy products, I stand in purchase and want to buy different product from one supplier. And of course the count should increase in the product table.

      I'm using portals in order, purchase and product table.

        • 1. Re: Inventory or stock relations problem
          Johan Hedman

          You are missing the tale where you keep track of purchasedItem . That table have to be related to your Purchase. Then from purchaseItem you track against your Product what OrderItems against PurchasedItems to keep track of your order stock

          • 2. Re: Inventory or stock relations problem
            philmodjunk

            It's possible to set up a transactions table where every inventory change adds a new record to that table. Much like deposits and withdrawals from a bank account, one field records quantities added to inventory and another records items removed. Such a table could also be used as your orderItems table if you want to see an immediate change in inventory.

            • 3. Re: Inventory or stock relations problem
              andresen

              order---<orderlineitem>---product

               

              product---<purchase

              and

              product---<orderlineitem

              and

              product ---<purchaselineitem

               

              purchase---<product

              and

              purchase---<orderlineitem

              and

              purchase>--suppliers

               

              Johan Hedman skrev:

               

              Then from purchaseItem you track against your Product what OrderItems against PurchasedItems to keep track of your order stock

              That means that first have to make a table purchaselineitem and then track that against product.

              Are you thinking that a script is working whit the order items agains purchaselineitem?

               

              Can't orderlineitems and purchaselineitem be one and the same?

              • 4. Re: Inventory or stock relations problem
                Johan Hedman

                You can have purchaselineitem and orderlineitem in one table like Phil wrote about, but I prefer having them in separate tables and keep a calculated field on Products that keeps track of Sum() from each related table. With this I keep purchase and order apart

                • 5. Re: Inventory or stock relations problem
                  philmodjunk

                  The reason why I recommend using a single table is that it makes for easy reporting where you can examine how inventory levels rise and fall over time to see if your re-order points are correctly optimized to minimize back orders without tying up unnecessary capital in product that is just sitting unused in storage.

                  • 6. Re: Inventory or stock relations problem
                    andresen
                    Johan Hedman skrev:

                    You can have purchaselineitem and orderlineitem in one table like Phil wrote about, but I prefer having them in separate tables and keep a calculated field on Products that keeps track of Sum() from each related table. With this I keep purchase and order apart

                    Is this way heavy for the system when you have manny post in your system? I'm thinking in this case of having a calculated field running every time I do something with the system?

                     

                    philmodjunk skrev:

                    The reason why I recommend using a single table is that it makes for easy reporting where you can examine how inventory levels rise and fall over time to see if your re-order points are correctly optimized to minimize back orders without tying up unnecessary capital in product that is just sitting unused in storage.

                    I have read the post: Managing Inventory using a Transactions Ledger.

                    Is this way heavy for the system if I go this way?

                     

                    It feels like either way what I select it's going to have some pros and cons. Problem I have now is to figure out what is the easiest for me to do. The knowledge I have in this is hopefully sufficient.

                    • 7. Re: Inventory or stock relations problem
                      Johan Hedman

                      philmodjunk have a good point that it would be easier to create reports on your stock history if you have in one table

                       

                      Data is not a problem. FileMaker can store millions of records and it would not affect the performance if you do things correct.

                       

                      Instead of having calculated fields to sum up your stock you can have a Numeric field that you do Set Field to and set the calculated Sum from. Then when you do a search or need that value things will go much faster.

                      • 8. Re: Inventory or stock relations problem
                        andresen

                        I did try to make the transaction table and could in that table see the in and out of my inventory. So if I am in product layout and want to se the stock. I then only have to make som portals and in them showing the two transaction table ( in and out) I know it's only one table but I thinking of making the portal user or self driven?

                         

                        In this way the stock is not in product table but in transaction table, correct?

                         

                        is it something else I missed?

                        • 9. Re: Inventory or stock relations problem
                          philmodjunk

                          You can do it any number of ways.

                           

                          You can use a calculation field in your products table that sums all the "in" and "out" values to compute a current total.

                          A number field in Products can be updated via script with each inventory transaction change to show the current level

                          A summary field from the transactions table can be placed on the products layout to show the same value.

                           

                          Developers tend to go with the script updated number field if there are (or are likely to be) performance issues calculating stock levels from the other methods.

                          1 of 1 people found this helpful
                          • 10. Re: Inventory or stock relations problem
                            fivos@macclub.org

                            hello Andresen

                            have ou tried the Inventory.fmp12 from the template solution?

                            i think that there, you can find all what you need.

                            1 of 1 people found this helpful