13 Replies Latest reply on Aug 30, 2013 10:48 AM by philmodjunk

    Stock Balance in each store at each items, finally need help

    RhezaElfuego

      Title

      Stock Balance in each store at each items, finally need help

      Post

            

           After all this time, finally I hd to write to ask.

           Kindly help me please...

            

           I hd created a table called DATABASE 1 QTY

           which appear all my data such as RECEIVING, TRANSFER, POS, INVENTORY STOCKS, etc.

           I hv only one DATABASE 1 QTY::Qty field, that record all quantity in every transactions process.

           and wanted to control it to appear in other several tables, such as ITEMS and STORES, per ITEMS, per STORES.

           For info, I hv 1 table Store called "MAIN STORE" and 1 other table Store called "ANAK MAS". that 2 table will contain information of my stores.

           I also have ITEMS table called ITEMS LIST, which contain every details of my Items. In every items, I put 2 field (ITEMS LIST::Unit on MAIN STORE) and  (ITEMS LIST::Unit on ANAK MAS), bcoz I would like to appear the Qty of the selected Item in each store.

            

           Here are some calculation that Im willing to do but tottally doesnt work : (for example in Units on ANAK MAS field)

      Case (DATABASE 1 | QTY::Store Name = "ANAK MAS" ; Sum (DATABASE 1 | QTY::Qty) ) -
      Case (DATABASE 1 | QTY::From Store = "ANAK MAS" ; Sum (DATABASE 1 | QTY::Qty) )

            

            

           Means : 1. Sum all Qty when the "Store Name" field says "ANAK MAS"

                          2. Sum all Qty when the "From Store" filed says "ANAK MAS"

                         3. Then substract ( no 1. - no 2. )

            

           Note :

           Store name field will define stocks comes in

           From Store field will define stocks comes out.

            

           And my layout in DATABASE 1 QTY will looks like:

      Date --- Transaction Type ---Item Name--- Qty --- From       --- To

           21/8/13--PURCHASED --- Necklace A --- 5 --- (BLANK) --- MAIN STORE

           22/8/13--TRANSFER --- Necklace A --- 5 --- MAIN STORE --- ANAK MAS

           23/8/13--POS             --- Necklace A ---3--- ANAK MAS --- (BLANK)

            

           As you see that I only have 1 Qty (Quantity) field that will calculate based on "From" and "To"

           If its a transfer, then "From" will decrease stocks and "To" will increase Stocks in the choosen Store. And will copy-paste all the method to other Layout such as : POS, WASTAGE, BREAKAGE, etc.

           Now the problem is to create the function in every field in other table to call the value from table DATABASE 1 QTY.

           I wanted to have  a calculation in my Necklace A record in ITEMS LIST table, that show how many stock left on what stores.

           It should be appear :

           Units on MAIN STORE : 0

           Units on ANAK MAS : 2

            

           Can you please help me with this?

            

           Thank you so much before!

      Screen_Shot_2013-08-21_at_9.21.39_AM.png

        • 1. Re: Stock Balance in each store at each items, finally need help
          RhezaElfuego

               Here is the History Transaction from the DATABASE 1 QTY table

                

          • 2. Re: Stock Balance in each store at each items, finally need help
            RhezaElfuego
            And I would like to appear the Case calculation here :

                 Units on Main Store, and

                 Units on Anak Mas

            • 3. Re: Stock Balance in each store at each items, finally need help
              philmodjunk

                   I did not read everything that you've posted. I stopped when I saw this:

              Case (DATABASE 1 | QTY::Store Name = "ANAK MAS" ; Sum (DATABASE 1 | QTY::Qty) ) -
              Case (DATABASE 1 | QTY::From Store = "ANAK MAS" ; Sum (DATABASE 1 | QTY::Qty) )

                   You cannot selectively sum related values in this fashion. To sum selectively, you must either use a relationship that matches only to the records you want to sum or you need to use the ExecuteSQL function to produce the needed selective sum. ExecuteSQL is only available in FileMaker 12.

              • 4. Re: Stock Balance in each store at each items, finally need help
                RhezaElfuego

                     I use Filemaker 12 and if you really get what Im desired to get for my field, do I have to use only the ExecuteSQL way to get what I want for my calculation?

                     Bytheway, thanks for answering, eventough I still need your support to complete this mission.

                      

                     Can you help me more with anything Phil?

                     Many thanks for your kind explanations!

                • 5. Re: Stock Balance in each store at each items, finally need help
                  RhezaElfuego

                       "To sum selectively, you must either use a relationship that matches only to the records you want to sum"

                        

                  could you give me some example phil so i may try it and it might work...

                  • 6. Re: Stock Balance in each store at each items, finally need help
                    philmodjunk

                         Say you have this data in a table named "ProduceSold":

                         Store ID  |  Fruit       |     Qty
                            5            |  apple    |     3
                            6            |  Pear      |     4
                            5            |  apple    |     7
                            5            |  Fig         |      2
                            6            | apple     |     23

                         and you want the total of Qty when Store ID = 5 and  Fruit = "Apple"---> 10 from the context of a different table than the one shown above.

                         You'd need to define a relationship to this table that matches to both the value of "apple" in the fruit field and to a specific store ID:

                         OtherTable::StoreID = ProduceSold ::StoreID AND
                         OtherTable::constApple = ProduceSold::Fruit

                         Then Sum ( ProduceSold::Qty ) will return the value of 10 when on a record in OtherTable where StoreID is 5. A summary field in FruitSold that computes the total of Qty would also return the same total when referenced from the context of this same record in OtherTable.

                         There are a number of different approaches to setting up a match field that specifies "apple" for use in that relationship. You can use a text field and assign different values for it to match to different fruit values to get different totals. This can be a global field so that you can assign the value once and get totals for the same fruit but for different stores--each from a different record in OtherTable. This can be a calculation field that has "Apple" as the only calculation term and then this relationship always and only matches to records with "apple" in the Fruit field. (I use the "const" naming convention shown here when using such a calculation field in a relationship.)

                         You can also use a relationship that just matches by store ID to get this total if you put the summary field that I described inside a one row portal to the related table if you then set up a portal filter to specify the additional value such as "apple". This can be simpler to set up, but the resulting total is "display only", you can't access that sub total in other calculations.

                         ExecuteSQL accomplishes the same result, but by defining the relationship in  Join clause and can include a WHERE clause to limit the records totalled to those matching the additional criteria such as fruit = "apple". The advantage here is that you neither need an additional match field, relationship in the relationship graph nor a filtered portal to get the desired total and the total can be accessed for use in other calcualtions. The downside to this is that you need to know SQL and have to deal with a less than friendly set up where the smallest syntax error returns a ? result with no error messages or codes to tell you why you have gotten that result.

                    • 7. Re: Stock Balance in each store at each items, finally need help
                      RhezaElfuego

                           Wow, thats pretty advanced case to deal with.

                           But I nearly get it, so I will change the Store into one table, so I can get the store ID.

                           Then the "other table" I might say it will be my ITEM LIST table, so I can filter he Item Qty trough Store ID in my ITEM LIST table.

                           And...the TRANSACTION issue,

                           there are 2 field that contain "FROM STORE" and "TO STORE", lets get my previous example for this :

                            

                      Date --- Transaction Type ---Item Name --- Qty --- From            --- To                  

                           21/8/13--PURCHASED      --- Necklace A --- 5    --- (BLANK)        --- MAIN STORE

                           22/8/13--TRANSFER         --- Necklace A --- 5    --- MAIN STORE --- ANAK MAS

                           23/8/13--POS                   --- Necklace A --- 3    --- ANAK MAS   --- (BLANK)

                            

                           Lets say we wanted to get the calculation for "MAIN STORE" for "NECKLACE A",

                           1. first we have to sum the Qty field for : TO STORE= "MAIN STORE"

                           2. second we have to sum the Qty field for : FROM STORE = "MAIN STORE"

                           Then we substract the no. 1 with no. 2 to get the value, that all transaction TO STORE are Qty in, and all transaction that FROM STORE are Qty out from the store that appear on the field record.
                            
                           is there any other simpler way or right way to do that?
                           I believe that this issue is not that complicated...
                            
                           But anyway, thanks a lot for your feedback Phil! Still need your help...
                      • 8. Re: Stock Balance in each store at each items, finally need help
                        philmodjunk

                             Well, I wouldn't structure my data in that manner. I'd set it up like this:

                             Date        Store         Type      Item                   QtyIn (Debit)     QtyOut ( Credit )   Balance
                             21/8/13   Main          Purch    Necklace A       5                             0                                5
                        22/8/13   Main          Trans    Necklace A        0                             5                               0
                             22/8/13   Anak Mas Trans    Necklace A        5                             0                               5

                             23/8/13   Anak Mas  POS     Necklace A         0                            5                                0

                             And the records are sorted first by store, then by date. There's another field, a calculation field that computes QtyIn - QtyOut and Balance is a running total summary field that computes the total of this calculation field. This summary field is also set to restart totals with each sorted group with store being the "group by" field.

                             WIth the transfer transactions split into two records like this so that you have a single store field in each record, the calculations for totals and sub totals are simpler. You can produce summary reports to see quantities on hand for each product for just one store all your stores or just some of your stores from layouts based on this transactions table. From a table of Stores (one record for each store), you can also access data in this table and get summary totals if you need to, but don't access the Balance field for that, use a summary field that computes the same total but that is not set to produce a running total.

                             This table example, can actually serve as the LIneItems table in a typical invoicing set of tables/relatonships. and I would not link stores or products by their names/descriptions, I'd use ID numbers.

                             For more on this inventory method: Managing Inventory using a Transactions Ledger

                             For more on summary reports that you can produce from such a table: Creating Filemaker Pro summary reports--Tutorial

                        • 9. Re: Stock Balance in each store at each items, finally need help
                          RhezaElfuego

                               Aaaaannd........I got to back to my first concept heheheh

                               thank youuu because you hv convinced me to use other method rather than this current method.

                               I was hoping that with one Qty field will make everything simpler...but okay it makes more difficulties than it should.

                                

                               Last question, I have a TRANSFER layout, based on DATABASE 1 QTY table. it looks like below picture.

                               And to create 2 records like above example, or here :

                          22/8/13   Main          Trans    Necklace A        0                             5                               0
                          22/8/13   Anak Mas Trans    Necklace A        5                             0                               5

                                

                               thats 2 records, lets say that the admin is not me, its my employee, so I need to reduce their mistake when they input anything to the system.

                               They cant enter :

                               1. Item A , From Store B, Qty 3 pcs,

                               and then open another record and fill

                               2. Item A, To store A, Qty 3 pcs,

                               How can we setup so it can be automatically fill the next record when we input data in 1 record?  I mean, can we generate with functions or script in the buttons or something else?

                                

                               Anyway, nice to discuss with you phil! much appreciate!

                                

                          • 10. Re: Stock Balance in each store at each items, finally need help
                            RhezaElfuego

                                 Now Im deciding to use this portal to show my transactions per items.

                                 But...I want to fill this field (Total Qty Field), with the last result from running balance...

                                 Any solution?

                                 The Total QTY MAIN STORE should be "0" for this ITEM, and Total QTY ANAK MAS should be "2".

                                 I'd filter the portal with store name, so I got 2 portal with each store, for this item.

                                  

                            • 11. Re: Stock Balance in each store at each items, finally need help
                              philmodjunk

                                   I have a TRANSFER layout

                                   I'd use a set of global fields where the user selects the item, specifies the quantity, selects to and from stores and then clicks a button to post that transaction. A script performed by that button would then make the "double entry" pair of records in your transactions table.

                                   To show the total of a field from an UNFILTERED portal:

                                   Option 1: You can define a summary field exactly the same as the running total summary but without the running total feature specified. Put it on your layout and it will show you the total from the portal.

                                   Option 2: You can define a sum funciton in the parent table that computes the sum of the balance calculation field.

                                   If you are editing data in the portal that will cause this total to update, use option 2 as it will update more smoothly.

                                   In a FILTERED portal, you'd use Option 1, but place the field inside a one row portal with the same portal filter expression as that of the larger portal.

                              • 12. Re: Stock Balance in each store at each items, finally need help
                                RhezaElfuego

                                     Phil, can you give me a little more detail to make the button that could make a double entry pair of records ?

                                      

                                      

                                • 13. Re: Stock Balance in each store at each items, finally need help
                                  philmodjunk

                                       Say you define these fields as fields with global storage: gDate, gToStore, gFromStore, gItem, gQty. the lower case g is my naming convention for identifying a field with global storage. Since global fields can be defined in any table and still remain accessible from any layout and script in the file, I usually put them in a special unrelated globals table unless I need to use the field as a match field in a relationship so I'll pretend that they are in a table named "Globals".

                                       If [ Not IsEmpty ( Globals::gDate ) and Not IsEmpty ( Globals::gToStore ) and Not IsEmpty ( Globals::gFromStore ) and Not IsEmpty ( Globals::gItem ) and Not IsEmpty ( Globals::gQty) ]
                                          Go to Layout [ "Transactions" (Transactions) ]
                                           New Record/Request
                                           Set Field [ Transactions::Date ; Globals::gDate ]
                                           Set Field [ Transactions::Store ; Globals::gFromStore ]
                                           Set Field [ Transactions::QtyOut ; Globals::gQty ]
                                           Set Field [ Transactions::Item ; Globals::gItem ]
                                           New Record/Request
                                           Set Field [ Transactions::Date ; Globals::gDate ]
                                           Set Field [ Transactions::Store ; Globals::gToStore ]
                                           Set Field [ Transactions::QtyIn ; Globals::gQty ]
                                           Set Field [ Transactions::Item ; Globals::gItem ]
                                       End If