Hi everyone,
I have a query list of products with a calculated field showing current stock on hand figures against each product. This figure is accumulated from various B/O transactions across the system using SUM aggregations.
If I then want to be able to create an individual stock adjustment transaction against each of the products to re-set all stock balances back to 0 how would I do that?
Looping through Query List to create new records
Re: Looping through Query List to create new records
Figured it out reading through other posted notes / discussions.
So you need 2 processes. the first selects all supplier product records. Then this first process calls the second process using the supplier product record as the input parameter.
The second process creates the update txn with the appropriate amount to set the balance back to 0.
Overall, the sequence looks like this:
Process 1: SelectAllSupplierProducts
FIND ALL SupplierProduct
UpdateStockBalance
Process 2: UpdateStockBalance
SupplierProduct.QtyOnHand = SUM........
If SupplierProduct.QtyOnHand <> 0 then
CREATE SupplierProductStockMovements WITH
SupplierProductStockMovements.Description = 'Adjustment to reset Stock Balance to 0',
SupplierProductStockMovements.EnteredBy=LoggedInCompanyUser.FirstName +' '+ LoggedInCompanyUser.Surname,
SupplierProductStockMovements.MovementDate=CURRENT_DATE,
SupplierProductStockMovements.MovementType='Qty Adjustment',
SupplierProductStockMovements.TxnQuantity=SupplierProduct.QtyOnHand * -1,
SupplierProductStockMovements.ob_SupplierProduct=SupplierProduct
SupplierProduct.QtyOnHand = 0
Thanks to everyone who contributed ideas through the earlier posts.
So you need 2 processes. the first selects all supplier product records. Then this first process calls the second process using the supplier product record as the input parameter.
The second process creates the update txn with the appropriate amount to set the balance back to 0.
Overall, the sequence looks like this:
Process 1: SelectAllSupplierProducts
FIND ALL SupplierProduct
UpdateStockBalance
Process 2: UpdateStockBalance
SupplierProduct.QtyOnHand = SUM........
If SupplierProduct.QtyOnHand <> 0 then
CREATE SupplierProductStockMovements WITH
SupplierProductStockMovements.Description = 'Adjustment to reset Stock Balance to 0',
SupplierProductStockMovements.EnteredBy=LoggedInCompanyUser.FirstName +' '+ LoggedInCompanyUser.Surname,
SupplierProductStockMovements.MovementDate=CURRENT_DATE,
SupplierProductStockMovements.MovementType='Qty Adjustment',
SupplierProductStockMovements.TxnQuantity=SupplierProduct.QtyOnHand * -1,
SupplierProductStockMovements.ob_SupplierProduct=SupplierProduct
SupplierProduct.QtyOnHand = 0
Thanks to everyone who contributed ideas through the earlier posts.