Henry Olive 5 Posted July 3 Good Day, Table1 : ITEMCODE...LOCNO....LOCQTY...LOTNO(Str) X1.....................A1..............10...................12 X1.....................A2..............20...................13 X1.....................A3..............10...................14 X1.....................A4..............10...................15 X2.....................B1..............10...................23 X2.....................B2..............40...................24 X3.....................C1..............50...................25 X3.....................C2..............60...................26 Table2: ITEMCODE...QTY...LOCNO......LOTNO X1.....................50.....Empty.........Empty X2.....................50 X3.....................50 In summary i want to write Table2's LOCNO & LOTNO fields below datas Table2: ITEMCODE...QTY...LOCNO......................................................LOTNO X1.....................50.......A1=10, A2=20, A3=10, A4=10........12, 13, 14, 15 X2.....................50......B1=10, B2=40..........................................23, 24 X3.....................50......C1=50.........................................................25 I wrote some codes but I wasn't completely successful. while not Table2.Eof do begin Query1.SQL = Select * from Table1 where ItemNo = QuotedStr(Table2ItemNo.asString) while not Query1.Eof do begin if Query1.FieldByName('LOCQTY').asFloat >= Table2QTY.asFloat then begin LocNos := Query1.FieldByName('LOCNO').AsString + '=' + Query1.FieldByName('LOCQTY').asString; LotNos := Query1.FieldByName('LOTNO').AsString + '=' + Query1.FieldByName('LOTNO').asString; Table2.Edit; Table2LOCNO.asString := LocNos; Table2LOTNO.asString := LotNos; Table2.Post; end else if Query1.FieldByName('LOCQTY').asFloat < Table2QTY.asFloat then begin // MyProblem is here end; I'm so sorry i know the question is too long, but This is how I could explain it with my poor English Thank You 7 / Share this post Link to post
Remy Lebeau 1420 Posted July 3 (edited) Given the example you provided, try something more like this: var LocNos, LotNos: string; ... Table2.First; while not Table2.Eof do begin Query1.SQL.Text = 'SELECT * FROM Table1 WHERE ITEMCODE = :ItemCode AND LOCQTY <= :Qty'; Query1.ParamByName('ItemCode').AsString := Table2ItemNo.AsString; Query1.ParamByName('Qty').AsFloat := Table2QTY.AsFloat; Query1.Open; try Query1.First; if not Query1.Eof then begin LocNos := Query1.FieldByName('LOCNO').AsString + '=' + Query1.FieldByName('LOCQTY').AsString; LotNos := Query1.FieldByName('LOTNO').AsString; repeat Query1.Next; if Query1.Eof then Break; LocNos := LocNos + ', ' + Query1.FieldByName('LOCNO').AsString + '=' + Query1.FieldByName('LOCQTY').AsString; LotNos := LotNos + ', ' + Query1.FieldByName('LOTNO').AsString; until False; Table2.Edit; Table2LOCNO.AsString := LocNos; Table2LOTNO.AsString := LotNos; Table2.Post; end; finally Query1.Close; end; Table2.Next; end; Alternatively: var LocNos, LotNos: TStringList; ... Table2.First; while not Table2.Eof do begin Query1.SQL.Text := 'SELECT * FROM Table1 WHERE ITEMCODE = :ItemCode AND LOCQTY <= :Qty'; Query1.ParamByName('ItemCode').AsString := Table2ItemNo.AsString; Query1.ParamByName('Qty').AsFloat := Table2QTY.AsFloat; Query1.Open; try Query1.First; if not Query1.Eof then begin LocNos.Clear; LotNos.Clear; repeat LocNos.Add(Query1.FieldByName('LOCNO').AsString + '=' + Query1.FieldByName('LOCQTY').AsString); LotNos.Add(Query1.FieldByName('LOTNO').AsString); Query1.Next; until Query1.Eof; Table2.Edit; Table2LOCNO.AsString := LocNos.CommaText; Table2LOTNO.AsString := LotNos.CommaText; Table2.Post; end; finally Query1.Close; end; Table2.Next; end; Edited July 3 by Remy Lebeau 1 Share this post Link to post
Henry Olive 5 Posted July 4 Remy, Thank you SO SO SO much for the time you spent for me Your both codes works very well but I'm so sorry i couldnt express well my problem For example Query result like below for X1 Item ITEM..LOCNO...LOCQTY...LOTNO.. X1.........A1........... .4................01 X1.........A2.............6................02 if Table2QTY = 10 then LocNos:=A1=4, A2=6 LotNos:=01, 02 if Table2QTY = 2 then LocNos:=A1=2 (Even A1 LOCQTY = 4, because i'll pick just 2) LotNos:=01 I want to pick an Item from its Locations UPTO Table2QTY according to query result order When the result reachs to Table2QTY it must skip the other Locations Thank You Share this post Link to post
Remy Lebeau 1420 Posted July 4 (edited) 15 hours ago, Henry Olive said: I want to pick an Item from its Locations UPTO Table2QTY according to query result order When the result reachs to Table2QTY it must skip the other Locations Simply tweak the code a little to keep track of the quantity used while iterating, eg: var LocNos, LotNos: string; QtyWanted, QtyUsed: Double; ... Table2.First; while not Table2.Eof do begin QtyWanted := Table2QTY.AsFloat; if QtyWanted > 0.0 then begin Query1.SQL.Text = 'SELECT * FROM Table1 WHERE ITEMCODE = :ItemCode AND LOCQTY > 0'; Query1.ParamByName('ItemCode').AsString := Table2ItemNo.AsString; Query1.Open; try Query1.First; if not Query1.Eof then begin LocNos := ''; LotNos := ''; repeat QtyUsed := Math.Min(Query1.FieldByName('LOCQTY').AsFloat, QtyWanted); LocNos := LocNos + Query1.FieldByName('LOCNO').AsString + '=' + FloatToStr(QtyUsed) + ','; LotNos := LotNos + Query1.FieldByName('LOTNO').AsString + ','; QtyWanted := QtyWanted - QtyUsed; Query1.Next; until Query1.Eof or (QtyWanted <= 0.0); SetLength(LocNos, Length(LocNos)-1); SetLength(LotNos, Length(LotNos)-1); Table2.Edit; Table2LOCNO.AsString := LocNos; Table2LOTNO.AsString := LotNos; Table2.Post; end; finally Query1.Close; end; end; Table2.Next; end; Edited July 4 by Remy Lebeau 1 Share this post Link to post