Hi,
I am designing a report using “Lot No. information” table. This is the code that i have written,
recILE.RESET;
recILE.SETRANGE(recILE.“Lot No.”,“Lot No. Information”.“Lot No.”);
recILE.SETFILTER(recILE.“Location Code”,’%1’,‘PD-CT001’);
recILE.SETFILTER(recILE.“Prod. Order No.”,’<>%1’,’’);
IF recILE.FIND(’-’) THEN
BEGIN
REPEAT
recILEnt.RESET;
recILEnt.SETRANGE(recILEnt.“Document No.”,recILE.“Document No.”);
recILEnt.SETRANGE(recILEnt.“Lot No.”,recILE.“Lot No.”);
recILEnt.SETRANGE(recILEnt.“Entry Type”,recILE.“Entry Type”);
IF recILEnt.FIND(’-’) THEN
BEGIN
REPEAT
ILECount := recILEnt.COUNT;
IF ILECount = 2 THEN
BEGIN
IF (recILEnt.“Entry Type” = recILEnt.“Entry Type” :: Consumption) THEN
ConQty += recILEnt.Quantity;
IF (recILEnt.“Entry Type” = recILEnt.“Entry Type” :: Output) THEN
OutQty += recILEnt.Quantity;
END;
UNTIL recILEnt.NEXT = 0;
END;
UNTIL recILE.NEXT = 0;
END;
Here after entering into the second ILE record variable i.e recILEnt it should perform the summing of the quantity where the line count is equal to 2 which is checked for each document for the particular lot. How do I do this… where should I show the COUNT function? Right now its givig me the value as zero.
I tried with your code and its giving double the output qty for me…
I created a released production Order with qty 10 and posted 2 output entries with 5 each time…
at the end it is showing 20 in report OutQty as it will repeat 2 times for each ILE…
Check your system whether you have double entries or not and filters…
COUNT is showing 2 in my case correctly
Hi Mohan,
Thanks fa the reply…
Actually if I comment the COUNT part i.e
ILECount := recILEnt.COUNT;
IF ILECount = 2 THEN
its summing up properly only, without considering the no. of lines in that document, but I want it to sum up only fa the document which has only 2 lines in it i.e only one line with the “entry type” consumption and only one line with the “entry type” output fa that particular document.
Can you be more clear with example…
Sure…
Consider I have a “Lot No.” say ‘AAA’ , it has few document nos.
Lot No. Doc. No. Entry Type Qty
AAA 1 consumption 10
AAA 1 output 20
AAA 2 consumption 15
AAA 2 output 10
AAA 3 consumption 50
So in this example, the variables I have created should have the value as,
ConQty = 25 and OutQty = 30
So as said, it should not consider the Doc No. 3 as it does not have a one-one relation fa the output and consumption.
You mean the doc. No 3 did not have a Output entry so skip it,is it?
What if it has 2 consuptions or 2 output entries?
In your code you are filtering on Entry type means you will get only either consumption or output entries…
Ya exactly… I want to sum up the documents which has only one consumption and one output entry , 2 consumptions 2 outputs, 1 consumption 2 outputs or vice versa is not to be considered…
So thought of doing it by COUNT coz one document wil not hav 2 simultaneous consumption entry or 2 simultaneous output entry. So if there are 2 lines for a document one wil be consumption and the other wil be output.
Documents which has the Line count less than 2 or greater than 2 has to be skipped as you said.
You can try something like this
ConQty := 0;
OutQty := 0;
recILE.RESET;
recILE.SETRANGE(recILE.“Lot No.”,“Lot No. Information”.“Lot No.”);
recILE.SETFILTER(recILE.“Location Code”,’%1’,‘PD-CT001’);
recILE.SETFILTER(recILE.“Prod. Order No.”,’<>%1’,’’);
IF recILE.FINDSET THEN
REPEAT
OneCon := FALSE;
oneOut := FALSE;
recILEnt.RESET;
recILEnt.SETRANGE(recILEnt.“Document No.”,recILE.“Document No.”);
recILEnt.SETRANGE(recILEnt.“Lot No.”,recILE.“Lot No.”);
recILEnt.SETRANGE(recILEnt.“Entry Type”,recILEnt.“Entry Type”::Consumption);
ILECount := recILEnt.COUNT;
OneCon := ILECount = 1;
recILEnt.SETRANGE(recILEnt.“Entry Type”);
recILEnt.SETRANGE(recILEnt.“Entry Type”,recILEnt.“Entry Type”::Output);
ILECount := recILEnt.COUNT;
oneOut := ILECount = 1;
IF OneCon AND oneOut THEN BEGIN
recILEnt.SETRANGE(recILEnt.“Entry Type”);
IF recILEnt.FINDSET THEN
REPEAT
IF (recILEnt.“Entry Type” = recILEnt.“Entry Type” :: Consumption) THEN
ConQty += recILEnt.Quantity;
IF (recILEnt.“Entry Type” = recILEnt.“Entry Type” :: Output) THEN
OutQty += recILEnt.Quantity;
UNTIL recILEnt.NEXT = 0;
END;
UNTIL recILE.NEXT = 0;
Where OneCon and OneOut are Boolean variables
Hey… thank u so much… but its jus doubling the value, if I divide it by 2 its giving the exact one…
No, that is not the solution
Try this
ConQty := 0;
OutQty := 0;
recILE.RESET;
recILE.SETRANGE(recILE.“Lot No.”,“Lot No. Information”.“Lot No.”);
recILE.SETFILTER(recILE.“Location Code”,’%1’,‘PD-CT001’);
recILE.SETFILTER(recILE.“Prod. Order No.”,’<>%1’,’’);
IF recILE.FINDFIRST THEN BEGIN
// REPEAT
OneCon := FALSE;
oneOut := FALSE;
recILEnt.RESET;
recILEnt.SETRANGE(recILEnt.“Document No.”,recILE.“Document No.”);
recILEnt.SETRANGE(recILEnt.“Lot No.”,recILE.“Lot No.”);
recILEnt.SETRANGE(recILEnt.“Entry Type”,recILEnt.“Entry Type”::Consumption);
ILECount := recILEnt.COUNT;
OneCon := ILECount = 1;
recILEnt.SETRANGE(recILEnt.“Entry Type”);
recILEnt.SETRANGE(recILEnt.“Entry Type”,recILEnt.“Entry Type”::Output);
ILECount := recILEnt.COUNT;
oneOut := ILECount = 1;
IF OneCon AND oneOut THEN BEGIN
recILEnt.SETRANGE(recILEnt.“Entry Type”);
IF recILEnt.FINDSET THEN
REPEAT
IF (recILEnt.“Entry Type” = recILEnt.“Entry Type” :: Consumption) THEN
ConQty += recILEnt.Quantity;
IF (recILEnt.“Entry Type” = recILEnt.“Entry Type” :: Output) THEN
OutQty += recILEnt.Quantity;
UNTIL recILEnt.NEXT = 0;
END;
// UNTIL recILE.NEXT = 0;
END;
hmmm…
try this
ConQty := 0;
OutQty := 0;
recILE.RESET;
recILE.SETCURRENTKEY(“Document No.”);
recILE.SETRANGE(recILE.“Lot No.”,“Lot No. Information”.“Lot No.”);
recILE.SETFILTER(recILE.“Location Code”,’%1’,‘PD-CT001’);
recILE.SETFILTER(recILE.“Prod. Order No.”,’<>%1’,’’);
IF recILE.FINDFIRST THEN BEGIN
REPEAT
IF PrevDocNo <> recILE.“Document No.” THEN BEGIN
PrevDocNo := recILE.“Document No.”;
OneCon := FALSE;
oneOut := FALSE;
recILEnt.RESET;
recILEnt.SETRANGE(recILEnt.“Document No.”,recILE.“Document No.”);
recILEnt.SETRANGE(recILEnt.“Lot No.”,recILE.“Lot No.”);
recILEnt.SETRANGE(recILEnt.“Entry Type”,recILEnt.“Entry Type”::Consumption);
ILECount := recILEnt.COUNT;
OneCon := ILECount = 1;
recILEnt.SETRANGE(recILEnt.“Entry Type”);
recILEnt.SETRANGE(recILEnt.“Entry Type”,recILEnt.“Entry Type”::Output);
ILECount := recILEnt.COUNT;
oneOut := ILECount = 1;
IF OneCon AND oneOut THEN BEGIN
recILEnt.SETRANGE(recILEnt.“Entry Type”);
IF recILEnt.FINDSET THEN
REPEAT
IF (recILEnt.“Entry Type” = recILEnt.“Entry Type” :: Consumption) THEN
ConQty += recILEnt.Quantity;
IF (recILEnt.“Entry Type” = recILEnt.“Entry Type” :: Output) THEN
OutQty += recILEnt.Quantity;
UNTIL recILEnt.NEXT = 0;
END;
END;
UNTIL recILE.NEXT = 0;
END;
Where PrevDocNo is of datatype Code and Length 20
Mohan… thank you so much… its working very fine…[:D] but i am not fully clear, how did that code variable stop doubling?
This is how we do Grouping through Coding using PrevDocNo…
The below code is executed once per document No.