where ; trn_rqb3.str_nmbr = C_QualifiedStockItems.str_nmbr; AND trn_rqb3.stk_code = C_QualifiedStockItems.stk_Code; AND BETWEEN (TRN_DATE,thisform.text2.Value,thisform.text3.Value); into ; cursor C_Subtotal select C_QualifiedStockItems IF C_Subtotal.Qty_In>=0 AND C_Subtotal.Qty_Out>=0 replace Qty_In with C_Subtotal.Qty_In,; Qty_Out with C_Subtotal.Qty_Out,; NewBalance with Stk_Opbl + C_Subtotal.Qty_In - C_Subtotal.Qty_Out endif endscanthanks alot
>lnStockNumber = Thisform.text1.value >lnStockClassFrom = Thisform.text4.value >lnStockClassTo = Thisform.Text5.value > >*/ First, get qualified stock items and their respective opening balances >*/ I am adding a few "place-holder" columns that will be updated during >*/ the scan loop. Not all 3 are required, but will allow for you to see >*/ what I am getting values from. >SELECT ; > str_nmbr,; > stk_name,; > stk_clas,; > stk_code,; > stk_opbl,; > 000000000 as Qty_In,; > 000000000 as Qty_Out,; > 000000000 as NewBalance; > FROM ; > stk_clas ; > WHERE ; > str_nmbr = lnStockNumber; > AND between( stk_clas, lnStockClassFrom, lnStockClassTo ); > INTO ; > CURSOR C_QualifiedStockItems READWRITE > >*/ Now, use THIS cursor to loop and get sum of inventory in/out >select C_QualifiedStockItems >scan > use in select( "C_Subtotal" ) > select ; > sum( Trn_Qnty * iif( trn_typ = 1, 1, 0 )) as Qty_In,; > sum( Trn_Qnty * iif( trn_typ= 44 or trn_typ=55, 1, 0 )) as Qty_Out; > from ; > trn_rqb3; > where ; > trn_rqb3.str_nmbr = C_QualifiedStockItems.str_nmbr; > AND trn_rqb3.stk_code = C_QualifiedStockItems.stk_Code; > into ; > cursor C_Subtotal > > select C_QualifiedStockItems > replace Qty_In with C_Subtotal.Qty_In,; > Qty_Out with C_Subtotal.Qty_Out,; > NewBalance with Stk_Opbl + C_Subtotal.Qty_In - C_Subtotal.Qty_Out > >endscan > >*/ You should be good to go from here... with whatever adjustments you need. > > >