remove qty from total required amount

[rainbows]

i am struggling with a formular and would like some help

the 3rd screen shot shows a orderqty for 10 to a batch no "pco00001" which is unique
and there is a tick box when that line item is shipped
the first screen shot shows how i get the total quantity for all orders for that part ( 9096b1073) there are 2 orders . one for 10 and one for 20 total 30, it then looks at the stock and gives me a diffqty of which i need to place a purchase order for 2
this is a rolling reqqty and what i would like to do is look at the line batch no see if it has been ticked in the shipped box then remove the orderqty from the required qty so then the rolling qty is now 20

hope i have explained it ok

thanks steve

batch number

 

[arnelgp]

first create a query from Order Details table to sum all OrderQty for same material where Shipped=False.
next join the above query to your Stocklist and do the Calculation.

 

[rainbows]

thanks but i need to explain more

the sales order is for part "NS2782M1" see attached order {this is a text field )
this is an assembled part which has a bill of materials attached to it see screen shot 2 you can see the product number and the qty required to produce that product in this case item 4 therefor for an order of 10 products i need 10 of item 4 but as i have 28 on order or stock i only need 2 as shown on screen shot above . is it possible to add another expression in the query attached looking for shipped or not and adding them up in the query?

 

[rainbows]

thank you . below seems to be working