lot issue date qty price curbal value
A RECEIPT 01-12 7 10 7 70
B RECEIPT 02-12 4 20 11 150
ISSUE 03-12 -6 5 90
C RECEIPT 04-12 2 30 7 150
ISSUE 05-12 -2 5 120
******************************************
So essentially your problem is to calculate and display Curbal and Value...yes?
First of all how is this report grouped?? Is Date more important than Lot Number??
I sort of think so since you are wanting to evaluate how much of a certain product you have at any given time.
Your data example gives successively increasing date and form a RECEIPT point of view I can see this being true (ie. Batch Lot A should be made before Batch Lot C) but when ISSUES of Batch B begin are all ISSUES of Lot A complete??
To me this must be addressed since you are looking for on-hand / current value numbers....at least that is what the SALES MANAGER in me thinks.
So the Grouping of this report is probably
Group 1 - Product ID (not included in your example)
Group 2 - date (Transaction Date)
Group 3 - Lot Number
with sorting by Issue (descending)
At least that is what I will assume they will be for this example.
a tricky part here in your calculations is that your value depends on what has been issued....there doesn't seem to be an indicator as to what Lot has been issued so it must be on the basis of FIFO as your title indicates.
Now to keep track of this I would use arrays . The way to do this to reduce maintainance is to set the size 50% higher than you expect...so if your max number of LOTs for a given report is 10 in your estimation ... then size the arrays for 15
To Calculate your CurBal and Value
1. Create an initialization formula
//@Initialization (placed suppressed in Group 1 header)
WhilePrintingRecords;
if Not inRepeatingGroupHeader then
(
stringVar Array Lot := ["","","","","","","","","","","","","","",""];
numberVar array Quantity := [0,0,0,0,0,0,0,0,0,0,0,0,0,0,0];
numberVar array Price := [0,0,0,0,0,0,0,0,0,0,0,0,0,0,0];
numberVar Curbal := 0;
numberVar Value := 0;
StringVar Warning := "";
);
2. Place the following formulas in the Detail section in their appropriate places
//@CalcNumbers (Suppressed in detail section)
WhilePrintingRecords;
numberVar Curbal;
numberVar Value;
stringVar array Lot;
numberVar array Quantity;
numberVar array Price;
numberVar pointer;
BooleanVar flag := False;
NumberVar temp := 0;
if {Table.issue} = "RECEIPT" then
(
for pointer := 1 to ubound(Lot) do
(
if Lot[pointer] = {Table.Lot} or Lot[pointer] = "" then
flag := True;
if flag then exit do;
);
If pointer = ubound(Lot) and not flag then
(
Warning := "Maintenance on arrays required...data missing";
temp := 0; //does nothing, just makes If-Then legal
)
else
(
Lot[pointer] := {Table.Lot};
Price[pointer] := {Table.price};
Quantity[pointer] := Quantity[pointer]+ {Table.Quantity};
CurBal := CurBal + {Table.Quantity};
value := value + ({Table.price} * {Table.Quantity});
);
temp := 0; //does nothing, just makes If-Then legal
)
else // this is for ISSUES
(
temp := {Table.Quantity};
CurBal := CurBal + temp;
for pointer := 1 to ubound(Quantity) do
(
if Quantity[pointer] <> 0 then
(
if Quantity[pointer] > temp then
(
Quantity[pointer] := Quantity[pointer]+ temp;
value := value + (Price[pointer] * temp);
flag := True;
)
else
(
temp := temp - Quantity[pointer] ;
value := value + (Price[pointer] * Quantity[pointer]);
flag := False;
);
);
if flag then exit do;
);
temp := 0; //does nothing, just makes If-Then legal
);
now you can assign display values to the calculated results
//@DisplayCurBal (in detail section)
EvaluateAfter(@CalcNumbers)
numberVar Curbal;
Curbal;
//@DisplayValue (in detail section)
EvaluateAfter(@CalcNumbers)
numberVar Value;
Value;
In the Group 1 footer place this maintenance Alert
//@Alert (this can have a yellow background to standout)
WhilePrintingRecords;
stringVar Warning;
Warning; //will not appear unless tere is a problem with the array sizes.
I think this will do the trick....may require a tweak or two though.
Jim Broadbent
The quality of the answer is directly proportional to the quality of the problem statement!
