First the SQL
1) You want On Hand Qty - Product can be stored in multiple locators in M_Storage table. I assume you want total of all locators
2) So SQL = SELECT SUM (QTYONHAND) FROM M_STORAGE s
3) Now you want to sync with product table, so we add WHERE s.M_PRODUCT_ID = M_PRODUCT.M_PRODUCT_ID
4) So the final SQL we need is
(SELECT SUM(QTYONHAND) FROM M_STORAGE s WHERE s.M_PRODUCT_ID = M_PRODUCT.M_PRODUCT_ID)
Now lets add it to compiere:
1) Log in as SuperUser or System
2) Role = System Administrator
3) Open Table window
4) Select Column tab
5) Create new record
6) Insert a name for DB Column Name
7) Choose a system element – this case most likely Qty_OnHand
8) Reference = String
9) Length = 20 should be plenty
10) Click Save
11) Click synchronize column, cross fingers
12) Look on bottom left of window for “alter table M_PRODUCTADD “name of the field you just made”
Lets add it to the M_Product window.
1) Go to Window Tab Field
2) Choose Product Table
3) Goto Tab
4) Choose the “tab” we want which is Product (it is for the M_PRODUCT table)
5) Choose Field Tab
6) New Record
7) Name your Field (Qty on Hand)
8) Column – Choose the new column you created.
9) Save
Now open the Product window and it should work!
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Hello
I'm Trying TO Add OnhandQty Information In Product Windows
SO I Just Want Link Value of Colums from M_Storage.QtyOnHand To
M_Product.QtyOnHand, Is There any way to easy setp without Java Codeing
Please Let Me Know;--
I Digging Out couple days;--
Thanks
your problem-
First the SQL
1) You want On Hand Qty - Product can be stored in multiple locators in M_Storage table. I assume you want total of all locators
2) So SQL = SELECT SUM (QTYONHAND) FROM M_STORAGE s
3) Now you want to sync with product table, so we add WHERE s.M_PRODUCT_ID = M_PRODUCT.M_PRODUCT_ID
4) So the final SQL we need is
(SELECT SUM(QTYONHAND) FROM M_STORAGE s WHERE s.M_PRODUCT_ID = M_PRODUCT.M_PRODUCT_ID)
Now lets add it to compiere:
1) Log in as SuperUser or System
2) Role = System Administrator
3) Open Table window
4) Select Column tab
5) Create new record
6) Insert a name for DB Column Name
7) Choose a system element – this case most likely Qty_OnHand
8) Reference = String
9) Length = 20 should be plenty
10) Click Save
11) Click synchronize column, cross fingers
12) Look on bottom left of window for “alter table M_PRODUCTADD “name of the field you just made”
Lets add it to the M_Product window.
1) Go to Window Tab Field
2) Choose Product Table
3) Goto Tab
4) Choose the “tab” we want which is Product (it is for the M_PRODUCT table)
5) Choose Field Tab
6) New Record
7) Name your Field (Qty on Hand)
8) Column – Choose the new column you created.
9) Save
Now open the Product window and it should work!
Hi stingreye,
When you wrote the SQL, where did you place that code? Did you put it in the Default Logic field?
Thank you,
Dhruv
Oops Forgot the most important part!
The field is the Column SQL Field.
Its on the column tab of the Table And Column Window.