1 2 Previous Next 18 Replies Latest reply on Jan 30, 2012 9:47 PM by MojtabaFazeli

    Stock calculation problem?

    mayor

      Title

      Stock calculation problem?

      Post

      Hello,

      I'm using FMP ver 9.0, have design inventory system. I have 4 tables,

      staf (name, title, division, stock_id)

      stock (stock_id, stock_name ....)

      out_stock (stock_id, stock_name....)

      in_stock (stock_id, stock_name....)

      PO (po_no, po_date, po_amount, stock_id)

      Relational : stock_id

      I face problem how to make calculation on current stock after add new stock for example.

      example

      stock              Total            Qty-Out                     Balance
      ------              ------           ----------                   -----------
      CD                     50                5                              45
      Pen                    10                3                               7

      How to make the calculation for the balance of the stock for each good calculated

      after in or out of the store. Your idea much appriciated.

      TQ

        • 1. Re: Stock calculation problem?
          philmodjunk

          You might want to search this forum for threads on an "Inventory Log"

          I usually suggest that approach for this. Here's a brief intro:

          I'd use one table instead of out_stock and In_stock.

          Stock_Id, QtyIn, QtyOut, cBal, sBalance (do not include stock name in this table, refer to the stock name field in Stock when you need to show the name).

          cBal is QtyIn - QtyOut

          sBalance is a summary field computing the total of cBal. You can set this up as a running total that restarts the total when records are grouped by Stock_ID to compute the totals for each item in your inventory in FileMaker 10 and 11. I don't know if this is also possible in FileMaker 9.

          This way the reception and removal of stock are like deposits and withdrawels in a check register with the summary total giving you an up to date total of your current amount in stock.

          • 2. Re: Stock calculation problem?
            mayor

            Thanks a lot PhilModJunk,

            I  try first your ideas and will be back soon. Actually, my problem is on how to calculate the stock balance for each goods after in and out. Your suggestion much appriciated.

            TQ

            • 3. Re: Stock calculation problem?
              mayor

              I have design table like your sugest. Below is the tables and relational.

               

              Relational

               

              I still cannot get the total amount for each stock when its in or out. Field sBalance only compute the total amount of all stock, not for balance of each stock. Please advice me. I have set sBalance as summary field to compute the total of cBal. Below is the diagram. Looking forward your advise. TQ

              stock

              • 4. Re: Stock calculation problem?
                philmodjunk

                Sort your records by StockID and then sBalance will restart each running total with each group of Stock ID's--if you defined sBalance with the correct settings like I described earlier.

                With the method I described earlier. You would not normally log both in and out values in the same row. Stock received (or manufactured) would be a separate entry from Stock Sold (or shipped). (Just as you normally do not enter a deposit and a withdrawal on the same row of a check register.)

                • 5. Re: Stock calculation problem?
                  mayor

                  I still didn't get the correct calculation of stock balance. Is it field sBalance is setting to Global field ? and is this features is working in FMP ver 9.0? Because I have tried what you have suggest to me. If you have any ideas please advice me?

                  TQ

                  • 6. Re: Stock calculation problem?
                    philmodjunk

                    sBalance should be a field of type summary. It is not a global field.

                    If you can open the field definition for sBalance and can find an option for "restart summary for each sorted group", you should be able to get this to work for you.

                    Keep in mind that the key phrase here is "sorted group". I reminded you that you needed to sort your records because your last screen shot shows that the records are not sorted. If you don't sort your records by the Stock ID field, the totals won't restart.

                    • 7. Re: Stock calculation problem?
                      mayor

                      Here I attached the screen shot of sBalance field. If anything goes wrong, please advice me. Thanks in advance

                      stock

                      • 8. Re: Stock calculation problem?
                        mayor

                        OK, i got the correct calculation of the sBalance now. But only in Preview Mode, Can I get that in Browse mode. TQ

                        • 9. Re: Stock calculation problem?
                          philmodjunk

                          As far as I know, this should work in browse mode just as in Preview Mode. Is this field in the body or in a sub summary layout part?

                          • 10. Re: Stock calculation problem?
                            mayor

                            Field in body, not in sub-summary. One more thing is, how i want to make when stock_id is entered, stock_name is displayed accordingly.

                            TQ

                            • 11. Re: Stock calculation problem?
                              LaRetta_1

                              Hi mayor,

                              Can you explain why you have StockID in your Staff table?  I realize you and Phil are addressing issues in the other tables interpreting the balance of stock but I wanted to bring it up.  It would appear that the Staff table should have a StaffID (unique auto-enter serial) and it would be the 'one' side to many stock items so you would instead put the StaffID in the stock table (if you mean that one staff person was involved in checking a stock item in and out.

                              Can you explain a bit about this relationship?

                              • 12. Re: Stock calculation problem?
                                mayor

                                The stockID is for relationship between staff table and stock table. My main point is to compute the balance of the stock after the stock is in and out. Staff table involve in taking away the stock and the po table is for adding the new stock. (base on stock balance).

                                If using stockID field as relationship, i think i need to create stockID field also in table stock.

                                TQ

                                • 13. Re: Stock calculation problem?
                                  mayor

                                  I got confuse when I want to add new stock. For example DVD balance is 20 units and I want to add new stock for DVD which is 100. So the new unit of DVD is 120.

                                  Field "QtyIN" is for add new qty of stock and "QtyOUT" is value for stock out.

                                  Sum_Qty_IN - summary total of QtyIN
                                  Sum_Qty_OUT - summary total of QtyOUT

                                  I want sum_Qty_IN hold the value of total of stocks that is added, but its not working, maybe because of this field is set to  "running total & restart summary for each sorted group by stock_id". If I untick these setting, the calculation of the balance of each stock will be wrong. 

                                  So, how I want to sum_Qty_IN hold the total of the stock added?

                                  Please advice. TQ

                                  • 14. Re: Stock calculation problem?
                                    philmodjunk

                                    How is it "not working"? That may give us a clue here.

                                    1 2 Previous Next