While not CaLculating

[Henry Olive 5]

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

 

/

[Remy Lebeau 1393]

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

[Henry Olive 5]

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

 

 

 

 

 

 

 

 

 

 

 

 

 

 

[Remy Lebeau 1393]

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

[Henry Olive 5]

Remy,

THANK YOU SO MUCH