12 Replies Latest reply on Jun 15, 2017 10:57 AM by philmodjunk

    Dynamic Inventroy

    goldencode

      Hello everyone,

       

      I am using the Invoices Starter Solution that is provided by Filemaker Pro 15 Advanced

       

      In the table Products, each product has a quantity, now lets assume that i'm selling Mobiles and the quantity i have is 10 Mobiles, and because i'm lucky a customer wanted to buy 4 Mobiles, i'v created an invoice for him and the remaining Mobiles in my inventory are 6.

       

      The question is, how am i supposed to create a Field (Remaining Quantity) in the table Products that will change according to the sold quantity? In other words i want to track the remaining quantity, i have been trying to reach out a solution the past couple days with no luck, my brain stopped working, therefore i'm here to get your assistance,

       

      Please help, and thank you so much for your valuable time.

        • 1. Re: Dynamic Inventroy
          mikebeargie

          You're missing an entire "transactions" table.

           

          Invoices should be made up of multiple transactions (your outgoing products), and any inventory you are adding should be a transaction as well (incoming products).

           

          Once you have a relationship between Products and Transactions, then it's a simple calculation of:

          Sum(Transactions::Qty) in your Products table to give you the amount currently in stock.

          1 of 1 people found this helpful
          • 2. Re: Dynamic Inventroy
            DanielShanahan

            This article on updating inventory quantity in FileMaker Pro might be helpful.

            1 of 1 people found this helpful
            • 3. Re: Dynamic Inventroy
              gofmp15

              FileMaker templates were made for show not for use. The were so complex and new users had so many problems using them that they have been stripped of complexity and do very little other than look nice.

               

              Your question, which is only a first of many, is answered thusly:

               

              Related tables do the work for you.

               

              Simple is to create an invoice file and a products file and an inbetween file for invoice items.

               

              Invoice<->Invoice items<->Products

               

              The invoice items file has two relating fields which I would name:

              Join Invoice ID

              Join Product ID

               

              The invoice file inserts the product cost from the product table and a qty is entered, etc.

              The Product file has a field to sum the qty of items ordered from all of the invoice items records. This lets you know how many items were used/sold since the database began.

              1 of 1 people found this helpful
              • 4. Re: Dynamic Inventroy
                ibrahim_bittar

                Hi goldencode

                 

                Starter solutions (in my opinion) are meant to show you what you can do with FileMaker but they are – by any means – not suitable for real life work.

                 

                If you want a really good-free-modifiable FileMaker solution, take a look at FM Starting Point - Free FileMaker Template by RCC

                 

                Just my 2¢ of Mexican pesos.

                 

                Best regards

                 

                Ibrahim

                • 5. Re: Dynamic Inventroy
                  goldencode

                  Actually i'm not using the starter solution for real life use, i'm just experiencing things..The starter solutions in my opinion is a great source to see how things work and experiment with them by editing existing contents or adding more ideas. Thank you for the advice

                   

                  Regarding my main question, thank you so much gofmp15, i couldn't believe that it is this simple, overthinking was the problem, and i guess you meant TABLE by FILE!

                   

                  Also, thanks a lot everyone for your helpful answers, i know that things can be achieved in different ways, this one worked for me and it is simpler than i thought.

                  • 6. Re: Dynamic Inventroy
                    philmodjunk

                    For managing inventory, a transactions table is vital. Not only can it be used to compute current inventory levels, you can use it to look at the "history" of how your inventory levels rise and fall. This can then assist you in setting optimum "re-order levels" that minimize the amount of capital you have to tie up in inventory on hand while still avoiding having to back order customers due to insufficient inventory to fill their orders.

                    1 of 1 people found this helpful
                    • 7. Re: Dynamic Inventroy
                      goldencode

                      Thanks a lot philmodjunk for the clarification,

                       

                      Even if the simple way worked, however your clarification pulled my attention to think deeply into it, now i feel like i have to add "transaction" table, do you have a sample on how to archive that?

                      • 8. Re: Dynamic Inventroy
                        philmodjunk

                        A transaction table is just a basic accounting register. It has at least these fields:

                        TransactionDate

                        ProductID

                        QtyIn

                        QtyOut

                        cBal (QtyIn - QtyOut)

                        optional fields:

                        sBalance (summary, total of cBal)

                        srBalance (summary, running total, restart total When grouped by productID.)

                        TransactionDescription

                         

                        This table can take the place of InvoiceData or LineItems for invoicing. QtyOut would be used for QtySold.

                        1 of 1 people found this helpful
                        • 9. Re: Dynamic Inventroy
                          goldencode

                          Hold on! It can take the place of InvoiceData or LineItems, you mean the idea of transaction table works as same as the InvoiceData table in the Filemaker Invoices Starter Solution? I mean by adding QtyIn & QtyOut to InvoiceData table, i would be able to get the remaining quantity with some calculations without making a new table called "transaction"?

                           

                          I know i can try it myself, i'm not trying to be lazy here, but forgive me at this time i'm away from Filemaker

                           

                          Thank you so much philmodjunk for the best knowledge as always!

                          • 10. Re: Dynamic Inventroy
                            philmodjunk

                            Close. InvoiceData already has a quantity field for indicating how many of a given item is to be purchased. You don't need both QtyOut and that Qty field that's already in the field. You can use the same field for both.

                             

                            Note that you would still need to set up another layout based on this table in order to log other inventory changes that aren't due to the sale of product, such as:

                             

                            Product Received From vendor (or your own manufacturing line)

                            Product Returned to Vendor

                            Inventory Shrinkage

                             

                            This assumes that you want to reduce inventory by the number ordered or purchased, the instant that you fill out the details of the order. There may be cases where that doesn't work for your business if there's a significant delay between entering the data and shipping out the ordered product.

                            1 of 1 people found this helpful
                            • 11. Re: Dynamic Inventroy
                              gofmp15

                              Not mentioned is that one field can be used for quantity to reflect in or out and another field to label the transaction.

                               

                              Transaction type   qty

                              Received   +10

                              Sold   -2

                              Returned  +1

                              Missing   -3

                               

                              Now your report on the line items table would have the categories mentioned sorted and listed...

                               

                              Only one line item table needed and your total count is easy, just sum all line item records.

                               

                              With the records being dated, you can also sort by month/year

                              • 12. Re: Dynamic Inventroy
                                philmodjunk

                                Not mentioned is that one field can be used for quantity to reflect in or out and another field to label the transaction.

                                 

                                Read reply #8 again:

                                 

                                optional fields:

                                sBalance (summary, total of cBal)

                                srBalance (summary, running total, restart total When grouped by productID.)

                                TransactionDescription