Looping through Query List to create new records

If you have questions or if you want to share your opinion about Aware IM post your message on this forum
Post Reply
MarkP
Posts: 20
Joined: Tue Jul 28, 2020 2:57 am
Location: Brisbane AUS

Looping through Query List to create new records

Post by MarkP »

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?
MarkP
Posts: 20
Joined: Tue Jul 28, 2020 2:57 am
Location: Brisbane AUS

Re: Looping through Query List to create new records

Post by MarkP »

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.
Post Reply