1 2 Previous Next 17 Replies Latest reply on Dec 10, 2013 11:23 AM by philmodjunk

    Stock Update

    gullfounder

      Title

      Stock Update

      Post

           Hi,

                I have issue regarding the update of qty in my database. Here is what i wanted to achieve. I have 2 table Product and Product line. I made a layout based on Product and made a portal into it for Product line. there is another table name items.

           Product is related to Product Line which is related to Items.

           after i insert the all the required products in Product Line. I want a button which updates the qty in items table for each item inserted in Product line.

           Can some pleas help on it? Have very little experience in on Filemaker.

           I have Filemaker 12 Pro.

            

        • 1. Re: Stock Update
          philmodjunk

               It's possible to set up the Qty field in items as a calculation field and then no button is required.

               See this thread for one approach that can be used for inventory management: Managing Inventory using a Transactions Ledger

               But as the number of records in Product Line increases, it can take much too long for such a calculation field to update--especially if you pull up a list of items on the items layout. So you may want to use two fields, one the calculation field and one a simple number field. Your script would just use Set Field to copy the value of the calculation field into the number field. But you will need to be careful to run this script every time a record is added, deleted or changed.

          • 2. Re: Stock Update
            gullfounder

                 I have read the post you linked regarding the Inventory Management and Transaction Ledger. I have made everything to test it. Made simple tables and Layouts. The only thing can't manage to understand it the Layout for Transaction Ledger. You have detailed the field and there roles. But i cannot understand to setup the Layout.

                 Will you Explain that part a bit more?

                  

                 I am attaching a File to see how i made the sample.

                  

                 https://www.dropbox.com/s/uprx3ndxk77q0bh/New%20Stock.fmp12

            • 3. Re: Stock Update
              philmodjunk

                   All I see are mostly blank layouts and no layout where any attempt at a "ledger" layout has been made. I suggest that you ask some specific questions about the ledger layout and then I'll be able to respond with an answer. Otherwise, I could spend a lot of time telling you about the parts of this that you already understand.

              • 4. Re: Stock Update
                gullfounder

                     I want to understand about Inventory Layout. where all those Transaction will be saved. I mean the "Inventory Layout in my File".

                • 5. Re: Stock Update
                  philmodjunk

                       Yes, but can you be more specific? There are many aspects to the design and function of that layout. Chances are good that there are many parts of it that you do understand. If you can narrow this down to specific questions, I can more easily answer without telling you what you already know and understand.

                  • 6. Re: Stock Update
                    gullfounder

                         I don't understand how these Layout part works? I mean i have all the field i need in a layout. But i just can't seemed to put them in proper place.

                         Like where do i place the Transaction ID field? where do i place the QTYout and QTYin field? all those fields.

                    • 7. Re: Stock Update
                      philmodjunk

                           All of those fields would be placed in the Body layout part.

                           This would be a layout that you view in List View so the body should be sized to be no larger than is needed to hold a single row of such fields so that each row represents one record that modifies your inventory for a specific inventory item.

                      • 8. Re: Stock Update
                        gullfounder

                             and the Layout parts? just header body and Footer?

                        • 9. Re: Stock Update
                          philmodjunk

                               That would depend on what you want to set up. You could just use the Header and Body.

                               Sub Summary parts can be added to serve as a "sub head" and/or a  "sub foot" for a group of all transactions for a given item, but that is an option, not a requirement. Such parts would serve to visually divide each group of transaction records from other groups and can be used to display summary field data based on the entire group.

                               But a running total summary field set to "restart with each sorted group" also shows the current total for each item in the last record of the group so such parts are not absolutely necessary.

                          • 10. Re: Stock Update
                            gullfounder

                                 I have certain requirement regarding this. I wanted to see only the current stock of each item only. How do i setup this?

                                 Another is I need to view my stock levels of specific dates. For Example: Date range form Nov 1 to Nov 15. How do i setup that?

                            • 11. Re: Stock Update
                              philmodjunk

                                   1) If you reference this data from a layout based on the products table, you can get a report with one line for each product with the current total on hand.

                                   2) You can also use a summary report where you have a sub summary layout part and no body layout part to get one row for each item and the total on hand. This requires a found set of all the transactions and a sort order that correctly groups the items. (You do not use a running total type summary field for this type of report, however. You'd use a summary field that does not have that option specified.)

                                   To see the inventory levels for a given date range, there are two possible reports you might want to see.

                                   If you just want to see the total quantity on hand--one row of data for each item, this is a variation of 2) above, but you perform a find to limit your transaction records to just those with the specified date range.

                                   If you want to see the inventory levels in a running balance "ledger view" where you can see all the transactions in the specified date range so you can see how the levels rise and fall with each transaction, you'll need a more sophisticated approach combining a slightly different layout design with a script and some calculation fields--and it will be much easier to do this for a single item in one report that for a group of items.

                              • 12. Re: Stock Update
                                gullfounder

                                     I have been working that inventory system. Made some improvements. After clicking the update button in Purchase layout for each item in Product Line i enter new record in Inventory. 

                                     Also i  know the concept of getting the Current stock of requried item with referencing it to transaction layout. But for date range i dint get it. Pleas can you explain part number 2 from your last reply.

                                      

                                     I am attaching the new file with new update.

                                     https://www.dropbox.com/s/uprx3ndxk77q0bh/New%20Stock.fmp12

                                • 13. Re: Stock Update
                                  philmodjunk

                                       The method in the Inventory Ledger thread does not require such an update script as the ledger records and the invoice Line Item records are the same records in the same table.

                                       There are multiple possible approaches depending on what exactly you want to do with that date range. That's why I said: "There are two possible reports that you might want to see".

                                       Can you describe exactly how you want your report to appear? There are seemingly small details to how that report might be set up that can make a big difference in what is needed to make it happen for you.

                                  • 14. Re: Stock Update
                                    gullfounder

                                         here is What i wanted.

                                         1) Set Range of date. date X to date y

                                         2) Report shows all items transactions that took between those date.

                                    1 2 Previous Next