AnsweredAssumed Answered

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

Question asked by RhezaElfuego on Aug 20, 2013
Latest reply on Aug 30, 2013 by philmodjunk


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



     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!